2 $PostgreSQL: pgsql/doc/src/sgml/xfunc.sgml,v 1.84 2004/05/16 23:22:07 neilc Exp $
6 <title>User-Defined Functions</title>
8 <indexterm zone="xfunc">
9 <primary>function</primary>
10 <secondary>user-defined</secondary>
14 <productname>PostgreSQL</productname> provides four kinds of
20 query language functions (functions written in
21 <acronym>SQL</acronym>) (<xref linkend="xfunc-sql">)
26 procedural language functions (functions written in, for
27 example, <application>PL/Tcl</> or <application>PL/pgSQL</>)
28 (<xref linkend="xfunc-pl">)
33 internal functions (<xref linkend="xfunc-internal">)
38 C-language functions (<xref linkend="xfunc-c">)
46 of function can take base types, composite types, or
47 combinations of these as arguments (parameters). In addition,
48 every kind of function can return a base type or
53 Many kinds of functions can take or return certain pseudo-types
54 (such as polymorphic types), but the available facilities vary.
55 Consult the description of each kind of function for more details.
59 It's easiest to define <acronym>SQL</acronym>
60 functions, so we'll start by discussing those.
61 Most of the concepts presented for <acronym>SQL</acronym> functions
62 will carry over to the other types of functions.
66 Throughout this chapter, it can be useful to look at the reference
67 page of the <xref linkend="sql-createfunction"> command to
68 understand the examples better. Some examples from this chapter
69 can be found in <filename>funcs.sql</filename> and
70 <filename>funcs.c</filename> in the <filename>src/tutorial</>
71 directory in the <productname>PostgreSQL</productname> source
76 <sect1 id="xfunc-sql">
77 <title>Query Language (<acronym>SQL</acronym>) Functions</title>
79 <indexterm zone="xfunc-sql">
80 <primary>function</primary>
81 <secondary>user-defined</secondary>
82 <tertiary>in SQL</tertiary>
86 SQL functions execute an arbitrary list of SQL statements, returning
87 the result of the last query in the list.
88 In the simple (non-set)
89 case, the first row of the last query's result will be returned.
90 (Bear in mind that <quote>the first row</quote> of a multirow
91 result is not well-defined unless you use <literal>ORDER BY</>.)
92 If the last query happens
93 to return no rows at all, the null value will be returned.
97 <indexterm><primary>SETOF</><seealso>function</></> Alternatively,
98 an SQL function may be declared to return a set, by specifying the
99 function's return type as <literal>SETOF
100 <replaceable>sometype</></literal>.<indexterm><primary>SETOF</></>
101 In this case all rows of the last query's result are returned.
102 Further details appear below.
106 The body of an SQL function should be a list of one or more SQL
107 statements separated by semicolons. Although dollar quoting
108 obviates this, note that because the syntax of the <command>CREATE
109 FUNCTION</command> command, if you choose not to use dollar
110 quoting, i.e. the body of the function is enclosed in single quotes,
111 you must escape single quote marks (<literal>'</>) used in the body of
112 the function, either by writing two single quotes (<literal>''</>) or
113 with a backslash (<literal>\'</>) where you desire each quote to be.
117 Arguments to the SQL function may be referenced in the function
118 body using the syntax <literal>$<replaceable>n</></>: <literal>$1</> refers to
119 the first argument, <literal>$2</> to the second, and so on. If an argument
120 is of a composite type, then the dot notation,
121 e.g., <literal>$1.name</literal>, may be used to access attributes
126 <title><acronym>SQL</acronym> Functions on Base Types</title>
129 The simplest possible <acronym>SQL</acronym> function has no arguments and
130 simply returns a base type, such as <type>integer</type>:
133 CREATE FUNCTION one() RETURNS integer AS $$
137 -- Alternative syntax:
138 CREATE FUNCTION one() RETURNS integer AS '
151 Notice that we defined a column alias within the function body for the result of the function
152 (with the name <literal>result</>), but this column alias is not visible
153 outside the function. Hence, the result is labeled <literal>one</>
154 instead of <literal>result</>.
158 It is almost as easy to define <acronym>SQL</acronym> functions
159 that take base types as arguments. In the example below, notice
160 how we refer to the arguments within the function as <literal>$1</>
164 CREATE FUNCTION add_em(integer, integer) RETURNS integer AS $$
168 SELECT add_em(1, 2) AS answer;
177 Here is a more useful function, which might be used to debit a
181 CREATE FUNCTION tf1 (integer, numeric) RETURNS integer AS $$
183 SET balance = balance - $2
184 WHERE accountno = $1;
189 A user could execute this function to debit account 17 by $100.00 as
193 SELECT tf1(17, 100.0);
198 In practice one would probably like a more useful result from the
199 function than a constant 1, so a more likely definition
203 CREATE FUNCTION tf1 (integer, numeric) RETURNS numeric AS $$
205 SET balance = balance - $2
206 WHERE accountno = $1;
207 SELECT balance FROM bank WHERE accountno = $1;
211 which adjusts the balance and returns the new balance.
215 Any collection of commands in the <acronym>SQL</acronym>
216 language can be packaged together and defined as a function.
217 Besides <command>SELECT</command> queries,
218 the commands can include data modification (i.e.,
219 <command>INSERT</command>, <command>UPDATE</command>, and
220 <command>DELETE</command>). However, the final command
221 must be a <command>SELECT</command> that returns whatever is
222 specified as the function's return type. Alternatively, if you
223 want to define a SQL function that performs actions but has no
224 useful value to return, you can define it as returning <type>void</>.
225 In that case, the function body must not end with a <command>SELECT</command>.
229 CREATE FUNCTION clean_emp() RETURNS void AS $$
231 WHERE salary <= 0;
245 <title><acronym>SQL</acronym> Functions on Composite Types</title>
248 When writing functions with arguments of composite
249 types, we must not only specify which
250 argument we want (as we did above with <literal>$1</> and <literal>$2</literal>) but
251 also the desired attribute (field) of that argument. For example,
253 <type>emp</type> is a table containing employee data, and therefore
254 also the name of the composite type of each row of the table. Here
255 is a function <function>double_salary</function> that computes what someone's
256 salary would be if it were doubled:
266 CREATE FUNCTION double_salary(emp) RETURNS numeric AS $$
267 SELECT $1.salary * 2 AS salary;
270 SELECT name, double_salary(emp.*) AS dream
272 WHERE emp.cubicle ~= point '(2,1)';
281 Notice the use of the syntax <literal>$1.salary</literal>
282 to select one field of the argument row value. Also notice
283 how the calling <command>SELECT</> command uses <literal>*</>
285 the entire current row of a table as a composite value. The table
286 row can alternatively be referenced using just the table name,
289 SELECT name, double_salary(emp) AS dream
291 WHERE emp.cubicle ~= point '(2,1)';
293 but this usage is deprecated since it's easy to get confused.
297 Sometimes it is handy to construct a composite argument value
298 on-the-fly. This can be done with the <literal>ROW</> construct.
299 For example, we could adjust the data being passed to the function:
301 SELECT name, double_salary(row(name, salary*1.1, age, cubicle)) AS dream
307 It is also possible to build a function that returns a composite type.
308 This is an example of a function
309 that returns a single <type>emp</type> row:
312 CREATE FUNCTION new_emp() RETURNS emp AS $$
313 SELECT text 'None' AS name,
316 point '(2,2)' AS cubicle;
320 In this example we have specified each of the attributes
321 with a constant value, but any computation
322 could have been substituted for these constants.
326 Note two important things about defining the function:
331 The select list order in the query must be exactly the same as
332 that in which the columns appear in the table associated
333 with the composite type. (Naming the columns, as we did above,
334 is irrelevant to the system.)
339 You must typecast the expressions to match the
340 definition of the composite type, or you will get errors like this:
343 ERROR: function declared to return emp returns varchar instead of text at column 1
352 A function that returns a row (composite type) can be used as a table
353 function, as described below. It can also be called in the context
354 of an SQL expression, but only when you
355 extract a single attribute out of the row or pass the entire row into
356 another function that accepts the same composite type.
360 This is an example of extracting an attribute out of a row type:
363 SELECT (new_emp()).name;
370 We need the extra parentheses to keep the parser from getting confused:
373 SELECT new_emp().name;
374 ERROR: syntax error at or near "." at character 17
379 Another option is to use
380 functional notation for extracting an attribute. The simple way
381 to explain this is that we can use the
382 notations <literal>attribute(table)</> and <literal>table.attribute</>
386 SELECT name(new_emp());
394 -- This is the same as:
395 -- SELECT emp.name AS youngster FROM emp WHERE emp.age < 30
397 SELECT name(emp) AS youngster
399 WHERE age(emp) < 30;
408 The other way to use a function returning a row result is to declare a
409 second function accepting a row type argument and pass the
410 result of the first function to it:
413 CREATE FUNCTION getname(emp) RETURNS text AS $$
417 SELECT getname(new_emp());
427 <title><acronym>SQL</acronym> Functions as Table Sources</title>
430 All SQL functions may be used in the <literal>FROM</> clause of a query,
431 but it is particularly useful for functions returning composite types.
432 If the function is defined to return a base type, the table function
433 produces a one-column table. If the function is defined to return
434 a composite type, the table function produces a column for each attribute
435 of the composite type.
442 CREATE TABLE foo (fooid int, foosubid int, fooname text);
443 INSERT INTO foo VALUES (1, 1, 'Joe');
444 INSERT INTO foo VALUES (1, 2, 'Ed');
445 INSERT INTO foo VALUES (2, 1, 'Mary');
447 CREATE FUNCTION getfoo(int) RETURNS foo AS $$
448 SELECT * FROM foo WHERE fooid = $1;
451 SELECT *, upper(fooname) FROM getfoo(1) AS t1;
453 fooid | foosubid | fooname | upper
454 -------+----------+---------+-------
459 As the example shows, we can work with the columns of the function's
460 result just the same as if they were columns of a regular table.
464 Note that we only got one row out of the function. This is because
465 we did not use <literal>SETOF</>. This is described in the next section.
470 <title><acronym>SQL</acronym> Functions Returning Sets</title>
473 When an SQL function is declared as returning <literal>SETOF
474 <replaceable>sometype</></literal>, the function's final
475 <command>SELECT</> query is executed to completion, and each row it
476 outputs is returned as an element of the result set.
480 This feature is normally used when calling the function in the <literal>FROM</>
481 clause. In this case each row returned by the function becomes
482 a row of the table seen by the query. For example, assume that
483 table <literal>foo</> has the same contents as above, and we say:
486 CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
487 SELECT * FROM foo WHERE fooid = $1;
490 SELECT * FROM getfoo(1) AS t1;
495 fooid | foosubid | fooname
496 -------+----------+---------
504 Currently, functions returning sets may also be called in the select list
505 of a query. For each row that the query
506 generates by itself, the function returning set is invoked, and an output
507 row is generated for each element of the function's result set. Note,
508 however, that this capability is deprecated and may be removed in future
509 releases. The following is an example function returning a set from the
513 CREATE FUNCTION listchildren(text) RETURNS SETOF text AS $$
514 SELECT name FROM nodes WHERE parent = $1
528 SELECT listchildren('Top');
536 SELECT name, listchildren(name) FROM nodes;
538 --------+--------------
547 In the last <command>SELECT</command>,
548 notice that no output row appears for <literal>Child2</>, <literal>Child3</>, etc.
549 This happens because <function>listchildren</function> returns an empty set
550 for those arguments, so no result rows are generated.
555 <title>Polymorphic <acronym>SQL</acronym> Functions</title>
558 <acronym>SQL</acronym> functions may be declared to accept and
559 return the polymorphic types <type>anyelement</type> and
560 <type>anyarray</type>. See <xref
561 linkend="extend-types-polymorphic"> for a more detailed
562 explanation of polymorphic functions. Here is a polymorphic
563 function <function>make_array</function> that builds up an array
564 from two arbitrary data type elements:
566 CREATE FUNCTION make_array(anyelement, anyelement) RETURNS anyarray AS $$
567 SELECT ARRAY[$1, $2];
570 SELECT make_array(1, 2) AS intarray, make_array('a'::text, 'b') AS textarray;
572 ----------+-----------
579 Notice the use of the typecast <literal>'a'::text</literal>
580 to specify that the argument is of type <type>text</type>. This is
581 required if the argument is just a string literal, since otherwise
582 it would be treated as type
583 <type>unknown</type>, and array of <type>unknown</type> is not a valid
585 Without the typecast, you will get errors like this:
588 ERROR: could not determine "anyarray"/"anyelement" type because input has type "unknown"
594 It is permitted to have polymorphic arguments with a deterministic
595 return type, but the converse is not. For example:
597 CREATE FUNCTION is_greater(anyelement, anyelement) RETURNS boolean AS $$
601 SELECT is_greater(1, 2);
607 CREATE FUNCTION invalid_func() RETURNS anyelement AS $$
610 ERROR: cannot determine result data type
611 DETAIL: A function returning "anyarray" or "anyelement" must have at least one argument of either type.
617 <sect1 id="xfunc-pl">
618 <title>Procedural Language Functions</title>
621 Procedural languages aren't built into the
622 <productname>PostgreSQL</productname> server; they are offered
623 by loadable modules. Please refer to the documentation of the
624 procedural language in question for details about the syntax and how the
625 function body is interpreted for each language.
629 There are currently four procedural languages available in the
630 standard <productname>PostgreSQL</productname> distribution:
631 <application>PL/pgSQL</application>, <application>PL/Tcl</application>,
632 <application>PL/Perl</application>, and
633 <application>PL/Python</application>.
634 Refer to <xref linkend="xplang"> for more information.
635 Other languages can be defined by users.
636 The basics of developing a new procedural language are covered in <xref
637 linkend="plhandler">.
641 <sect1 id="xfunc-internal">
642 <title>Internal Functions</title>
644 <indexterm zone="xfunc-internal"><primary>function</><secondary>internal</></>
647 Internal functions are functions written in C that have been statically
648 linked into the <productname>PostgreSQL</productname> server.
649 The <quote>body</quote> of the function definition
650 specifies the C-language name of the function, which need not be the
651 same as the name being declared for SQL use.
652 (For reasons of backwards compatibility, an empty body
653 is accepted as meaning that the C-language function name is the
654 same as the SQL name.)
658 Normally, all internal functions present in the
659 server are declared during the initialization of the database cluster (<command>initdb</command>),
660 but a user could use <command>CREATE FUNCTION</command>
661 to create additional alias names for an internal function.
662 Internal functions are declared in <command>CREATE FUNCTION</command>
663 with language name <literal>internal</literal>. For instance, to
664 create an alias for the <function>sqrt</function> function:
666 CREATE FUNCTION square_root(double precision) RETURNS double precision
671 (Most internal functions expect to be declared <quote>strict</quote>.)
676 Not all <quote>predefined</quote> functions are
677 <quote>internal</quote> in the above sense. Some predefined
678 functions are written in SQL.
684 <title>C-Language Functions</title>
686 <indexterm zone="xfunc-sql">
687 <primary>function</primary>
688 <secondary>user-defined</secondary>
689 <tertiary>in C</tertiary>
693 User-defined functions can be written in C (or a language that can
694 be made compatible with C, such as C++). Such functions are
695 compiled into dynamically loadable objects (also called shared
696 libraries) and are loaded by the server on demand. The dynamic
697 loading feature is what distinguishes <quote>C language</> functions
698 from <quote>internal</> functions --- the actual coding conventions
699 are essentially the same for both. (Hence, the standard internal
700 function library is a rich source of coding examples for user-defined
705 Two different calling conventions are currently used for C functions.
706 The newer <quote>version 1</quote> calling convention is indicated by writing
707 a <literal>PG_FUNCTION_INFO_V1()</literal> macro call for the function,
708 as illustrated below. Lack of such a macro indicates an old-style
709 (<quote>version 0</quote>) function. The language name specified in <command>CREATE FUNCTION</command>
710 is <literal>C</literal> in either case. Old-style functions are now deprecated
711 because of portability problems and lack of functionality, but they
712 are still supported for compatibility reasons.
715 <sect2 id="xfunc-c-dynload">
716 <title>Dynamic Loading</title>
718 <indexterm zone="xfunc-c-dynload">
719 <primary>dynamic loading</primary>
723 The first time a user-defined function in a particular
724 loadable object file is called in a session,
725 the dynamic loader loads that object file into memory so that the
726 function can be called. The <command>CREATE FUNCTION</command>
727 for a user-defined C function must therefore specify two pieces of
728 information for the function: the name of the loadable
729 object file, and the C name (link symbol) of the specific function to call
730 within that object file. If the C name is not explicitly specified then
731 it is assumed to be the same as the SQL function name.
735 The following algorithm is used to locate the shared object file
736 based on the name given in the <command>CREATE FUNCTION</command>
742 If the name is an absolute path, the given file is loaded.
748 If the name starts with the string <literal>$libdir</literal>,
749 that part is replaced by the <productname>PostgreSQL</> package
751 name, which is determined at build time.<indexterm><primary>$libdir</></>
757 If the name does not contain a directory part, the file is
758 searched for in the path specified by the configuration variable
759 <xref linkend="guc-dynamic-library-path">.<indexterm><primary>dynamic_library_path</></>
765 Otherwise (the file was not found in the path, or it contains a
766 non-absolute directory part), the dynamic loader will try to
767 take the name as given, which will most likely fail. (It is
768 unreliable to depend on the current working directory.)
773 If this sequence does not work, the platform-specific shared
774 library file name extension (often <filename>.so</filename>) is
775 appended to the given name and this sequence is tried again. If
776 that fails as well, the load will fail.
780 The user ID the <productname>PostgreSQL</productname> server runs
781 as must be able to traverse the path to the file you intend to
782 load. Making the file or a higher-level directory not readable
783 and/or not executable by the <systemitem>postgres</systemitem>
784 user is a common mistake.
788 In any case, the file name that is given in the
789 <command>CREATE FUNCTION</command> command is recorded literally
790 in the system catalogs, so if the file needs to be loaded again
791 the same procedure is applied.
796 <productname>PostgreSQL</productname> will not compile a C function
797 automatically. The object file must be compiled before it is referenced
799 FUNCTION</> command. See <xref linkend="dfunc"> for additional
805 After it is used for the first time, a dynamically loaded object
806 file is retained in memory. Future calls in the same session to
807 the function(s) in that file will only incur the small overhead of
808 a symbol table lookup. If you need to force a reload of an object
809 file, for example after recompiling it, use the <command>LOAD</>
810 command or begin a fresh session.
814 It is recommended to locate shared libraries either relative to
815 <literal>$libdir</literal> or through the dynamic library path.
816 This simplifies version upgrades if the new installation is at a
817 different location. The actual directory that
818 <literal>$libdir</literal> stands for can be found out with the
819 command <literal>pg_config --pkglibdir</literal>.
823 Before <productname>PostgreSQL</productname> release 7.2, only
824 exact absolute paths to object files could be specified in
825 <command>CREATE FUNCTION</>. This approach is now deprecated
826 since it makes the function definition unnecessarily unportable.
827 It's best to specify just the shared library name with no path nor
828 extension, and let the search mechanism provide that information
833 <sect2 id="xfunc-c-basetype">
834 <title>Base Types in C-Language Functions</title>
836 <indexterm zone="xfunc-c-basetype">
837 <primary>data type</primary>
838 <secondary>internal organisation</secondary>
842 To know how to write C-language functions, you need to know how
843 <productname>PostgreSQL</productname> internally represents base
844 data types and how they can be passed to and from functions.
845 Internally, <productname>PostgreSQL</productname> regards a base
846 type as a <quote>blob of memory</quote>. The user-defined
847 functions that you define over a type in turn define the way that
848 <productname>PostgreSQL</productname> can operate on it. That
849 is, <productname>PostgreSQL</productname> will only store and
850 retrieve the data from disk and use your user-defined functions
851 to input, process, and output the data.
855 Base types can have one of three internal formats:
860 pass by value, fixed-length
865 pass by reference, fixed-length
870 pass by reference, variable-length
877 By-value types can only be 1, 2, or 4 bytes in length
878 (also 8 bytes, if <literal>sizeof(Datum)</literal> is 8 on your machine).
879 You should be careful
880 to define your types such that they will be the same
881 size (in bytes) on all architectures. For example, the
882 <literal>long</literal> type is dangerous because it
883 is 4 bytes on some machines and 8 bytes on others, whereas
884 <type>int</type> type is 4 bytes on most
885 Unix machines. A reasonable implementation of
886 the <type>int4</type> type on Unix
890 /* 4-byte integer, passed by value */
896 On the other hand, fixed-length types of any size may
897 be passed by-reference. For example, here is a sample
898 implementation of a <productname>PostgreSQL</productname> type:
901 /* 16-byte structure, passed by reference */
908 Only pointers to such types can be used when passing
909 them in and out of <productname>PostgreSQL</productname> functions.
910 To return a value of such a type, allocate the right amount of
911 memory with <literal>palloc</literal>, fill in the allocated memory,
912 and return a pointer to it. (You can also return an input value
913 that has the same type as the return value directly by returning
914 the pointer to the input value. <emphasis>Never</> modify the
915 contents of a pass-by-reference input value, however.)
919 Finally, all variable-length types must also be passed
920 by reference. All variable-length types must begin
921 with a length field of exactly 4 bytes, and all data to
922 be stored within that type must be located in the memory
923 immediately following that length field. The
924 length field contains the total length of the structure,
925 that is, it includes the size of the length field
930 As an example, we can define the type <type>text</type> as
940 Obviously, the data field declared here is not long enough to hold
941 all possible strings. Since it's impossible to declare a variable-size
942 structure in <acronym>C</acronym>, we rely on the knowledge that the
943 <acronym>C</acronym> compiler won't range-check array subscripts. We
944 just allocate the necessary amount of space and then access the array as
945 if it were declared the right length. (This is a common trick, which
946 you can read about in many textbooks about C.)
951 variable-length types, we must be careful to allocate
952 the correct amount of memory and set the length field correctly.
953 For example, if we wanted to store 40 bytes in a <structname>text</>
954 structure, we might use a code fragment like this:
957 #include "postgres.h"
959 char buffer[40]; /* our source data */
961 text *destination = (text *) palloc(VARHDRSZ + 40);
962 destination->length = VARHDRSZ + 40;
963 memcpy(destination->data, buffer, 40);
967 <literal>VARHDRSZ</> is the same as <literal>sizeof(int4)</>, but
968 it's considered good style to use the macro <literal>VARHDRSZ</>
969 to refer to the size of the overhead for a variable-length type.
973 <xref linkend="xfunc-c-type-table"> specifies which C type
974 corresponds to which SQL type when writing a C-language function
975 that uses a built-in type of <productname>PostgreSQL</>.
976 The <quote>Defined In</quote> column gives the header file that
977 needs to be included to get the type definition. (The actual
978 definition may be in a different file that is included by the
979 listed file. It is recommended that users stick to the defined
980 interface.) Note that you should always include
981 <filename>postgres.h</filename> first in any source file, because
982 it declares a number of things that you will need anyway.
985 <table tocentry="1" id="xfunc-c-type-table">
986 <title>Equivalent C Types for Built-In SQL Types</title>
1003 <entry><type>abstime</type></entry>
1004 <entry><type>AbsoluteTime</type></entry>
1005 <entry><filename>utils/nabstime.h</filename></entry>
1008 <entry><type>boolean</type></entry>
1009 <entry><type>bool</type></entry>
1010 <entry><filename>postgres.h</filename> (maybe compiler built-in)</entry>
1013 <entry><type>box</type></entry>
1014 <entry><type>BOX*</type></entry>
1015 <entry><filename>utils/geo_decls.h</filename></entry>
1018 <entry><type>bytea</type></entry>
1019 <entry><type>bytea*</type></entry>
1020 <entry><filename>postgres.h</filename></entry>
1023 <entry><type>"char"</type></entry>
1024 <entry><type>char</type></entry>
1025 <entry>(compiler built-in)</entry>
1028 <entry><type>character</type></entry>
1029 <entry><type>BpChar*</type></entry>
1030 <entry><filename>postgres.h</filename></entry>
1033 <entry><type>cid</type></entry>
1034 <entry><type>CommandId</type></entry>
1035 <entry><filename>postgres.h</filename></entry>
1038 <entry><type>date</type></entry>
1039 <entry><type>DateADT</type></entry>
1040 <entry><filename>utils/date.h</filename></entry>
1043 <entry><type>smallint</type> (<type>int2</type>)</entry>
1044 <entry><type>int2</type> or <type>int16</type></entry>
1045 <entry><filename>postgres.h</filename></entry>
1048 <entry><type>int2vector</type></entry>
1049 <entry><type>int2vector*</type></entry>
1050 <entry><filename>postgres.h</filename></entry>
1053 <entry><type>integer</type> (<type>int4</type>)</entry>
1054 <entry><type>int4</type> or <type>int32</type></entry>
1055 <entry><filename>postgres.h</filename></entry>
1058 <entry><type>real</type> (<type>float4</type>)</entry>
1059 <entry><type>float4*</type></entry>
1060 <entry><filename>postgres.h</filename></entry>
1063 <entry><type>double precision</type> (<type>float8</type>)</entry>
1064 <entry><type>float8*</type></entry>
1065 <entry><filename>postgres.h</filename></entry>
1068 <entry><type>interval</type></entry>
1069 <entry><type>Interval*</type></entry>
1070 <entry><filename>utils/timestamp.h</filename></entry>
1073 <entry><type>lseg</type></entry>
1074 <entry><type>LSEG*</type></entry>
1075 <entry><filename>utils/geo_decls.h</filename></entry>
1078 <entry><type>name</type></entry>
1079 <entry><type>Name</type></entry>
1080 <entry><filename>postgres.h</filename></entry>
1083 <entry><type>oid</type></entry>
1084 <entry><type>Oid</type></entry>
1085 <entry><filename>postgres.h</filename></entry>
1088 <entry><type>oidvector</type></entry>
1089 <entry><type>oidvector*</type></entry>
1090 <entry><filename>postgres.h</filename></entry>
1093 <entry><type>path</type></entry>
1094 <entry><type>PATH*</type></entry>
1095 <entry><filename>utils/geo_decls.h</filename></entry>
1098 <entry><type>point</type></entry>
1099 <entry><type>POINT*</type></entry>
1100 <entry><filename>utils/geo_decls.h</filename></entry>
1103 <entry><type>regproc</type></entry>
1104 <entry><type>regproc</type></entry>
1105 <entry><filename>postgres.h</filename></entry>
1108 <entry><type>reltime</type></entry>
1109 <entry><type>RelativeTime</type></entry>
1110 <entry><filename>utils/nabstime.h</filename></entry>
1113 <entry><type>text</type></entry>
1114 <entry><type>text*</type></entry>
1115 <entry><filename>postgres.h</filename></entry>
1118 <entry><type>tid</type></entry>
1119 <entry><type>ItemPointer</type></entry>
1120 <entry><filename>storage/itemptr.h</filename></entry>
1123 <entry><type>time</type></entry>
1124 <entry><type>TimeADT</type></entry>
1125 <entry><filename>utils/date.h</filename></entry>
1128 <entry><type>time with time zone</type></entry>
1129 <entry><type>TimeTzADT</type></entry>
1130 <entry><filename>utils/date.h</filename></entry>
1133 <entry><type>timestamp</type></entry>
1134 <entry><type>Timestamp*</type></entry>
1135 <entry><filename>utils/timestamp.h</filename></entry>
1138 <entry><type>tinterval</type></entry>
1139 <entry><type>TimeInterval</type></entry>
1140 <entry><filename>utils/nabstime.h</filename></entry>
1143 <entry><type>varchar</type></entry>
1144 <entry><type>VarChar*</type></entry>
1145 <entry><filename>postgres.h</filename></entry>
1148 <entry><type>xid</type></entry>
1149 <entry><type>TransactionId</type></entry>
1150 <entry><filename>postgres.h</filename></entry>
1157 Now that we've gone over all of the possible structures
1158 for base types, we can show some examples of real functions.
1163 <title>Calling Conventions Version 0 for C-Language Functions</title>
1166 We present the <quote>old style</quote> calling convention first --- although
1167 this approach is now deprecated, it's easier to get a handle on
1168 initially. In the version-0 method, the arguments and result
1169 of the C function are just declared in normal C style, but being
1170 careful to use the C representation of each SQL data type as shown
1175 Here are some examples:
1178 #include "postgres.h"
1179 #include <string.h>
1189 /* by reference, fixed length */
1192 add_one_float8(float8 *arg)
1194 float8 *result = (float8 *) palloc(sizeof(float8));
1196 *result = *arg + 1.0;
1202 makepoint(Point *pointx, Point *pointy)
1204 Point *new_point = (Point *) palloc(sizeof(Point));
1206 new_point->x = pointx->x;
1207 new_point->y = pointy->y;
1212 /* by reference, variable length */
1218 * VARSIZE is the total size of the struct in bytes.
1220 text *new_t = (text *) palloc(VARSIZE(t));
1221 VARATT_SIZEP(new_t) = VARSIZE(t);
1223 * VARDATA is a pointer to the data region of the struct.
1225 memcpy((void *) VARDATA(new_t), /* destination */
1226 (void *) VARDATA(t), /* source */
1227 VARSIZE(t)-VARHDRSZ); /* how many bytes */
1232 concat_text(text *arg1, text *arg2)
1234 int32 new_text_size = VARSIZE(arg1) + VARSIZE(arg2) - VARHDRSZ;
1235 text *new_text = (text *) palloc(new_text_size);
1237 VARATT_SIZEP(new_text) = new_text_size;
1238 memcpy(VARDATA(new_text), VARDATA(arg1), VARSIZE(arg1)-VARHDRSZ);
1239 memcpy(VARDATA(new_text) + (VARSIZE(arg1)-VARHDRSZ),
1240 VARDATA(arg2), VARSIZE(arg2)-VARHDRSZ);
1247 Supposing that the above code has been prepared in file
1248 <filename>funcs.c</filename> and compiled into a shared object,
1249 we could define the functions to <productname>PostgreSQL</productname>
1250 with commands like this:
1253 CREATE FUNCTION add_one(integer) RETURNS integer
1254 AS '<replaceable>DIRECTORY</replaceable>/funcs', 'add_one'
1257 -- note overloading of SQL function name "add_one"
1258 CREATE FUNCTION add_one(double precision) RETURNS double precision
1259 AS '<replaceable>DIRECTORY</replaceable>/funcs', 'add_one_float8'
1262 CREATE FUNCTION makepoint(point, point) RETURNS point
1263 AS '<replaceable>DIRECTORY</replaceable>/funcs', 'makepoint'
1266 CREATE FUNCTION copytext(text) RETURNS text
1267 AS '<replaceable>DIRECTORY</replaceable>/funcs', 'copytext'
1270 CREATE FUNCTION concat_text(text, text) RETURNS text
1271 AS '<replaceable>DIRECTORY</replaceable>/funcs', 'concat_text',
1277 Here, <replaceable>DIRECTORY</replaceable> stands for the
1278 directory of the shared library file (for instance the
1279 <productname>PostgreSQL</productname> tutorial directory, which
1280 contains the code for the examples used in this section).
1281 (Better style would be to use just <literal>'funcs'</> in the
1282 <literal>AS</> clause, after having added
1283 <replaceable>DIRECTORY</replaceable> to the search path. In any
1284 case, we may omit the system-specific extension for a shared
1285 library, commonly <literal>.so</literal> or
1286 <literal>.sl</literal>.)
1290 Notice that we have specified the functions as <quote>strict</quote>,
1292 the system should automatically assume a null result if any input
1293 value is null. By doing this, we avoid having to check for null inputs
1294 in the function code. Without this, we'd have to check for null values
1295 explicitly, by checking for a null pointer for each
1296 pass-by-reference argument. (For pass-by-value arguments, we don't
1297 even have a way to check!)
1301 Although this calling convention is simple to use,
1302 it is not very portable; on some architectures there are problems
1303 with passing data types that are smaller than <type>int</type> this way. Also, there is
1304 no simple way to return a null result, nor to cope with null arguments
1305 in any way other than making the function strict. The version-1
1306 convention, presented next, overcomes these objections.
1311 <title>Calling Conventions Version 1 for C-Language Functions</title>
1314 The version-1 calling convention relies on macros to suppress most
1315 of the complexity of passing arguments and results. The C declaration
1316 of a version-1 function is always
1318 Datum funcname(PG_FUNCTION_ARGS)
1320 In addition, the macro call
1322 PG_FUNCTION_INFO_V1(funcname);
1324 must appear in the same source file. (Conventionally. it's
1325 written just before the function itself.) This macro call is not
1326 needed for <literal>internal</>-language functions, since
1327 <productname>PostgreSQL</> assumes that all internal functions
1328 use the version-1 convention. It is, however, required for
1329 dynamically-loaded functions.
1333 In a version-1 function, each actual argument is fetched using a
1334 <function>PG_GETARG_<replaceable>xxx</replaceable>()</function>
1335 macro that corresponds to the argument's data type, and the
1336 result is returned using a
1337 <function>PG_RETURN_<replaceable>xxx</replaceable>()</function>
1338 macro for the return type.
1339 <function>PG_GETARG_<replaceable>xxx</replaceable>()</function>
1340 takes as its argument the number of the function argument to
1341 fetch, where the count starts at 0.
1342 <function>PG_RETURN_<replaceable>xxx</replaceable>()</function>
1343 takes as its argument the actual value to return.
1347 Here we show the same functions as above, coded in version-1 style:
1350 #include "postgres.h"
1351 #include <string.h>
1356 PG_FUNCTION_INFO_V1(add_one);
1359 add_one(PG_FUNCTION_ARGS)
1361 int32 arg = PG_GETARG_INT32(0);
1363 PG_RETURN_INT32(arg + 1);
1366 /* b reference, fixed length */
1368 PG_FUNCTION_INFO_V1(add_one_float8);
1371 add_one_float8(PG_FUNCTION_ARGS)
1373 /* The macros for FLOAT8 hide its pass-by-reference nature. */
1374 float8 arg = PG_GETARG_FLOAT8(0);
1376 PG_RETURN_FLOAT8(arg + 1.0);
1379 PG_FUNCTION_INFO_V1(makepoint);
1382 makepoint(PG_FUNCTION_ARGS)
1384 /* Here, the pass-by-reference nature of Point is not hidden. */
1385 Point *pointx = PG_GETARG_POINT_P(0);
1386 Point *pointy = PG_GETARG_POINT_P(1);
1387 Point *new_point = (Point *) palloc(sizeof(Point));
1389 new_point->x = pointx->x;
1390 new_point->y = pointy->y;
1392 PG_RETURN_POINT_P(new_point);
1395 /* by reference, variable length */
1397 PG_FUNCTION_INFO_V1(copytext);
1400 copytext(PG_FUNCTION_ARGS)
1402 text *t = PG_GETARG_TEXT_P(0);
1404 * VARSIZE is the total size of the struct in bytes.
1406 text *new_t = (text *) palloc(VARSIZE(t));
1407 VARATT_SIZEP(new_t) = VARSIZE(t);
1409 * VARDATA is a pointer to the data region of the struct.
1411 memcpy((void *) VARDATA(new_t), /* destination */
1412 (void *) VARDATA(t), /* source */
1413 VARSIZE(t)-VARHDRSZ); /* how many bytes */
1414 PG_RETURN_TEXT_P(new_t);
1417 PG_FUNCTION_INFO_V1(concat_text);
1420 concat_text(PG_FUNCTION_ARGS)
1422 text *arg1 = PG_GETARG_TEXT_P(0);
1423 text *arg2 = PG_GETARG_TEXT_P(1);
1424 int32 new_text_size = VARSIZE(arg1) + VARSIZE(arg2) - VARHDRSZ;
1425 text *new_text = (text *) palloc(new_text_size);
1427 VARATT_SIZEP(new_text) = new_text_size;
1428 memcpy(VARDATA(new_text), VARDATA(arg1), VARSIZE(arg1)-VARHDRSZ);
1429 memcpy(VARDATA(new_text) + (VARSIZE(arg1)-VARHDRSZ),
1430 VARDATA(arg2), VARSIZE(arg2)-VARHDRSZ);
1431 PG_RETURN_TEXT_P(new_text);
1437 The <command>CREATE FUNCTION</command> commands are the same as
1438 for the version-0 equivalents.
1442 At first glance, the version-1 coding conventions may appear to
1443 be just pointless obscurantism. They do, however, offer a number
1444 of improvements, because the macros can hide unnecessary detail.
1445 An example is that in coding <function>add_one_float8</>, we no longer need to
1446 be aware that <type>float8</type> is a pass-by-reference type. Another
1447 example is that the <literal>GETARG</> macros for variable-length types allow
1448 for more efficient fetching of <quote>toasted</quote> (compressed or
1449 out-of-line) values.
1453 One big improvement in version-1 functions is better handling of null
1454 inputs and results. The macro <function>PG_ARGISNULL(<replaceable>n</>)</function>
1455 allows a function to test whether each input is null. (Of course, doing
1456 this is only necessary in functions not declared <quote>strict</>.)
1458 <function>PG_GETARG_<replaceable>xxx</replaceable>()</function> macros,
1459 the input arguments are counted beginning at zero. Note that one
1460 should refrain from executing
1461 <function>PG_GETARG_<replaceable>xxx</replaceable>()</function> until
1462 one has verified that the argument isn't null.
1463 To return a null result, execute <function>PG_RETURN_NULL()</function>;
1464 this works in both strict and nonstrict functions.
1468 Other options provided in the new-style interface are two
1470 <function>PG_GETARG_<replaceable>xxx</replaceable>()</function>
1471 macros. The first of these,
1472 <function>PG_GETARG_<replaceable>xxx</replaceable>_COPY()</function>,
1473 guarantees to return a copy of the specified argument that is
1474 safe for writing into. (The normal macros will sometimes return a
1475 pointer to a value that is physically stored in a table, which
1476 must not be written to. Using the
1477 <function>PG_GETARG_<replaceable>xxx</replaceable>_COPY()</function>
1478 macros guarantees a writable result.)
1479 The second variant consists of the
1480 <function>PG_GETARG_<replaceable>xxx</replaceable>_SLICE()</function>
1481 macros which take three arguments. The first is the number of the
1482 function argument (as above). The second and third are the offset and
1483 length of the segment to be returned. Offsets are counted from
1484 zero, and a negative length requests that the remainder of the
1485 value be returned. These macros provide more efficient access to
1486 parts of large values in the case where they have storage type
1487 <quote>external</quote>. (The storage type of a column can be specified using
1488 <literal>ALTER TABLE <replaceable>tablename</replaceable> ALTER
1489 COLUMN <replaceable>colname</replaceable> SET STORAGE
1490 <replaceable>storagetype</replaceable></literal>. <replaceable>storagetype</replaceable> is one of
1491 <literal>plain</>, <literal>external</>, <literal>extended</literal>,
1492 or <literal>main</>.)
1496 Finally, the version-1 function call conventions make it possible
1497 to return set results (<xref linkend="xfunc-c-return-set">) and
1498 implement trigger functions (<xref linkend="triggers">) and
1499 procedural-language call handlers (<xref
1500 linkend="plhandler">). Version-1 code is also more
1501 portable than version-0, because it does not break restrictions
1502 on function call protocol in the C standard. For more details
1503 see <filename>src/backend/utils/fmgr/README</filename> in the
1504 source distribution.
1509 <title>Writing Code</title>
1512 Before we turn to the more advanced topics, we should discuss
1513 some coding rules for <productname>PostgreSQL</productname>
1514 C-language functions. While it may be possible to load functions
1515 written in languages other than C into
1516 <productname>PostgreSQL</productname>, this is usually difficult
1517 (when it is possible at all) because other languages, such as
1518 C++, FORTRAN, or Pascal often do not follow the same calling
1519 convention as C. That is, other languages do not pass argument
1520 and return values between functions in the same way. For this
1521 reason, we will assume that your C-language functions are
1522 actually written in C.
1526 The basic rules for writing and building C functions are as follows:
1531 Use <literal>pg_config
1532 --includedir-server</literal><indexterm><primary>pg_config</><secondary>with user-defined C functions</></>
1533 to find out where the <productname>PostgreSQL</> server header
1534 files are installed on your system (or the system that your
1535 users will be running on). This option is new with
1536 <productname>PostgreSQL</> 7.2. For
1537 <productname>PostgreSQL</> 7.1 you should use the option
1538 <option>--includedir</option>. (<command>pg_config</command>
1539 will exit with a non-zero status if it encounters an unknown
1540 option.) For releases prior to 7.1 you will have to guess,
1541 but since that was before the current calling conventions were
1542 introduced, it is unlikely that you want to support those
1549 When allocating memory, use the
1550 <productname>PostgreSQL</productname> functions
1551 <function>palloc</function><indexterm><primary>palloc</></> and <function>pfree</function><indexterm><primary>pfree</></>
1552 instead of the corresponding C library functions
1553 <function>malloc</function> and <function>free</function>.
1554 The memory allocated by <function>palloc</function> will be
1555 freed automatically at the end of each transaction, preventing
1562 Always zero the bytes of your structures using
1563 <function>memset</function>. Without this, it's difficult to
1564 support hash indexes or hash joins, as you must pick out only
1565 the significant bits of your data structure to compute a hash.
1566 Even if you initialize all fields of your structure, there may be
1567 alignment padding (holes in the structure) that may contain
1574 Most of the internal <productname>PostgreSQL</productname>
1575 types are declared in <filename>postgres.h</filename>, while
1576 the function manager interfaces
1577 (<symbol>PG_FUNCTION_ARGS</symbol>, etc.) are in
1578 <filename>fmgr.h</filename>, so you will need to include at
1579 least these two files. For portability reasons it's best to
1580 include <filename>postgres.h</filename> <emphasis>first</>,
1581 before any other system or user header files. Including
1582 <filename>postgres.h</filename> will also include
1583 <filename>elog.h</filename> and <filename>palloc.h</filename>
1590 Symbol names defined within object files must not conflict
1591 with each other or with symbols defined in the
1592 <productname>PostgreSQL</productname> server executable. You
1593 will have to rename your functions or variables if you get
1594 error messages to this effect.
1600 Compiling and linking your code so that it can be dynamically
1601 loaded into <productname>PostgreSQL</productname> always
1602 requires special flags. See <xref linkend="dfunc"> for a
1603 detailed explanation of how to do it for your particular
1614 <title>Composite-Type Arguments in C-Language Functions</title>
1617 Composite types do not have a fixed layout like C
1618 structures. Instances of a composite type may contain
1619 null fields. In addition, composite types that are
1620 part of an inheritance hierarchy may have different
1621 fields than other members of the same inheritance hierarchy.
1622 Therefore, <productname>PostgreSQL</productname> provides
1623 a function interface for accessing fields of composite types
1628 Suppose we want to write a function to answer the query
1631 SELECT name, c_overpaid(emp, 1500) AS overpaid
1633 WHERE name = 'Bill' OR name = 'Sam';
1636 Using call conventions version 0, we can define
1637 <function>c_overpaid</> as:
1640 #include "postgres.h"
1641 #include "executor/executor.h" /* for GetAttributeByName() */
1644 c_overpaid(HeapTupleHeader t, /* the current row of emp */
1650 salary = DatumGetInt32(GetAttributeByName(t, "salary", &isnull));
1653 return salary > limit;
1657 In version-1 coding, the above would look like this:
1660 #include "postgres.h"
1661 #include "executor/executor.h" /* for GetAttributeByName() */
1663 PG_FUNCTION_INFO_V1(c_overpaid);
1666 c_overpaid(PG_FUNCTION_ARGS)
1668 HeapTupleHeader t = PG_GETARG_HEAPTUPLEHEADER(0);
1669 int32 limit = PG_GETARG_INT32(1);
1673 salary = DatumGetInt32(GetAttributeByName(t, "salary", &isnull));
1675 PG_RETURN_BOOL(false);
1676 /* Alternatively, we might prefer to do PG_RETURN_NULL() for null salary. */
1678 PG_RETURN_BOOL(salary > limit);
1684 <function>GetAttributeByName</function> is the
1685 <productname>PostgreSQL</productname> system function that
1686 returns attributes out of the specified row. It has
1687 three arguments: the argument of type <type>HeapTupleHeader</type> passed
1689 the function, the name of the desired attribute, and a
1690 return parameter that tells whether the attribute
1691 is null. <function>GetAttributeByName</function> returns a <type>Datum</type>
1692 value that you can convert to the proper data type by using the
1693 appropriate <function>DatumGet<replaceable>XXX</replaceable>()</function> macro.
1697 There is also <function>GetAttributeByNum</function>, which selects
1698 the target attribute by column number instead of name.
1702 The following command declares the function
1703 <function>c_overpaid</function> in SQL:
1706 CREATE FUNCTION c_overpaid(emp, integer) RETURNS boolean
1707 AS '<replaceable>DIRECTORY</replaceable>/funcs', 'c_overpaid'
1711 Notice we have used <literal>STRICT</> so that we did not have to
1712 check whether the input arguments were NULL.
1717 <title>Returning Rows (Composite Types) from C-Language Functions</title>
1720 To return a row or composite-type value from a C-language
1721 function, you can use a special API that provides macros and
1722 functions to hide most of the complexity of building composite
1723 data types. To use this API, the source file must include:
1725 #include "funcapi.h"
1730 There are two ways you can build a composite data value (henceforth
1731 a <quote>tuple</>): you can build it from an array of Datum values,
1732 or from an array of C strings that can be passed to the input
1733 conversion functions of the tuple's column datatypes. In either
1734 case, you first need to obtain or construct a <structname>TupleDesc</>
1735 descriptor for the tuple structure. When working with Datums, you
1736 pass the <structname>TupleDesc</> to <function>BlessTupleDesc</>,
1737 and then call <function>heap_formtuple</> for each row. When working
1738 with C strings, you pass the <structname>TupleDesc</> to
1739 <function>TupleDescGetAttInMetadata</>, and then call
1740 <function>BuildTupleFromCStrings</> for each row. In the case of a
1741 function returning a set of tuples, the setup steps can all be done
1742 once during the first call of the function.
1746 Several helper functions are available for setting up the initial
1747 <structname>TupleDesc</>. If you want to use a named composite type,
1748 you can fetch the information from the system catalogs. Use
1750 TupleDesc RelationNameGetTupleDesc(const char *relname)
1752 to get a <structname>TupleDesc</> for a named relation, or
1754 TupleDesc TypeGetTupleDesc(Oid typeoid, List *colaliases)
1756 to get a <structname>TupleDesc</> based on a type OID. This can
1757 be used to get a <structname>TupleDesc</> for a base or
1758 composite type. When writing a function that returns
1759 <structname>record</>, the expected <structname>TupleDesc</>
1760 must be passed in by the caller.
1764 Once you have a <structname>TupleDesc</>, call
1766 TupleDesc BlessTupleDesc(TupleDesc tupdesc)
1768 if you plan to work with Datums, or
1770 AttInMetadata *TupleDescGetAttInMetadata(TupleDesc tupdesc)
1772 if you plan to work with C strings. If you are writing a function
1773 returning set, you can save the results of these functions in the
1774 <structname>FuncCallContext</> structure --- use the
1775 <structfield>tuple_desc</> or <structfield>attinmeta</> field
1780 When working with Datums, use
1782 HeapTuple heap_formtuple(TupleDesc tupdesc, Datum *values, char *nulls)
1784 to build a <structname>HeapTuple</> given user data in Datum form.
1788 When working with C strings, use
1790 HeapTuple BuildTupleFromCStrings(AttInMetadata *attinmeta, char **values)
1792 to build a <structname>HeapTuple</> given user data
1793 in C string form. <literal>values</literal> is an array of C strings,
1794 one for each attribute of the return row. Each C string should be in
1795 the form expected by the input function of the attribute data
1796 type. In order to return a null value for one of the attributes,
1797 the corresponding pointer in the <parameter>values</> array
1798 should be set to <symbol>NULL</>. This function will need to
1799 be called again for each row you return.
1803 Once you have built a tuple to return from your function, it
1804 must be converted into a <type>Datum</>. Use
1806 HeapTupleGetDatum(HeapTuple tuple)
1808 to convert a <structname>HeapTuple</> into a valid Datum. This
1809 <type>Datum</> can be returned directly if you intend to return
1810 just a single row, or it can be used as the current return value
1811 in a set-returning function.
1815 An example appears in the next section.
1820 <sect2 id="xfunc-c-return-set">
1821 <title>Returning Sets from C-Language Functions</title>
1824 There is also a special API that provides support for returning
1825 sets (multiple rows) from a C-language function. A set-returning
1826 function must follow the version-1 calling conventions. Also,
1827 source files must include <filename>funcapi.h</filename>, as
1832 A set-returning function (<acronym>SRF</>) is called
1833 once for each item it returns. The <acronym>SRF</> must
1834 therefore save enough state to remember what it was doing and
1835 return the next item on each call.
1836 The structure <structname>FuncCallContext</> is provided to help
1837 control this process. Within a function, <literal>fcinfo->flinfo->fn_extra</>
1838 is used to hold a pointer to <structname>FuncCallContext</>
1844 * Number of times we've been called before
1846 * call_cntr is initialized to 0 for you by SRF_FIRSTCALL_INIT(), and
1847 * incremented for you every time SRF_RETURN_NEXT() is called.
1852 * OPTIONAL maximum number of calls
1854 * max_calls is here for convenience only and setting it is optional.
1855 * If not set, you must provide alternative means to know when the
1861 * OPTIONAL pointer to result slot
1863 * This is obsolete and only present for backwards compatibility, viz,
1864 * user-defined SRFs that use the deprecated TupleDescGetSlot().
1866 TupleTableSlot *slot;
1869 * OPTIONAL pointer to miscellaneous user-provided context information
1871 * user_fctx is for use as a pointer to your own data to retain
1872 * arbitrary context information between calls of your function.
1877 * OPTIONAL pointer to struct containing attribute type input metadata
1879 * attinmeta is for use when returning tuples (i.e., composite data types)
1880 * and is not used when returning base data types. It is only needed
1881 * if you intend to use BuildTupleFromCStrings() to create the return
1884 AttInMetadata *attinmeta;
1887 * memory context used for structures that must live for multiple calls
1889 * multi_call_memory_ctx is set by SRF_FIRSTCALL_INIT() for you, and used
1890 * by SRF_RETURN_DONE() for cleanup. It is the most appropriate memory
1891 * context for any memory that is to be reused across multiple calls
1894 MemoryContext multi_call_memory_ctx;
1897 * OPTIONAL pointer to struct containing tuple description
1899 * tuple_desc is for use when returning tuples (i.e. composite data types)
1900 * and is only needed if you are going to build the tuples with
1901 * heap_formtuple() rather than with BuildTupleFromCStrings(). Note that
1902 * the TupleDesc pointer stored here should usually have been run through
1903 * BlessTupleDesc() first.
1905 TupleDesc tuple_desc;
1912 An <acronym>SRF</> uses several functions and macros that
1913 automatically manipulate the <structname>FuncCallContext</>
1914 structure (and expect to find it via <literal>fn_extra</>). Use
1918 to determine if your function is being called for the first or a
1919 subsequent time. On the first call (only) use
1921 SRF_FIRSTCALL_INIT()
1923 to initialize the <structname>FuncCallContext</>. On every function call,
1924 including the first, use
1928 to properly set up for using the <structname>FuncCallContext</>
1929 and clearing any previously returned data left over from the
1934 If your function has data to return, use
1936 SRF_RETURN_NEXT(funcctx, result)
1938 to return it to the caller. (<literal>result</> must be of type
1939 <type>Datum</>, either a single value or a tuple prepared as
1940 described above.) Finally, when your function is finished
1943 SRF_RETURN_DONE(funcctx)
1945 to clean up and end the <acronym>SRF</>.
1949 The memory context that is current when the <acronym>SRF</> is called is
1950 a transient context that will be cleared between calls. This means
1951 that you do not need to call <function>pfree</> on everything
1952 you allocated using <function>palloc</>; it will go away anyway. However, if you want to allocate
1953 any data structures to live across calls, you need to put them somewhere
1954 else. The memory context referenced by
1955 <structfield>multi_call_memory_ctx</> is a suitable location for any
1956 data that needs to survive until the <acronym>SRF</> is finished running. In most
1957 cases, this means that you should switch into
1958 <structfield>multi_call_memory_ctx</> while doing the first-call setup.
1962 A complete pseudo-code example looks like the following:
1965 my_set_returning_function(PG_FUNCTION_ARGS)
1967 FuncCallContext *funcctx;
1969 MemoryContext oldcontext;
1970 <replaceable>further declarations as needed</replaceable>
1972 if (SRF_IS_FIRSTCALL())
1974 funcctx = SRF_FIRSTCALL_INIT();
1975 oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
1976 /* One-time setup code appears here: */
1977 <replaceable>user code</replaceable>
1978 <replaceable>if returning composite</replaceable>
1979 <replaceable>build TupleDesc, and perhaps AttInMetadata</replaceable>
1980 <replaceable>endif returning composite</replaceable>
1981 <replaceable>user code</replaceable>
1982 MemoryContextSwitchTo(oldcontext);
1985 /* Each-time setup code appears here: */
1986 <replaceable>user code</replaceable>
1987 funcctx = SRF_PERCALL_SETUP();
1988 <replaceable>user code</replaceable>
1990 /* this is just one way we might test whether we are done: */
1991 if (funcctx->call_cntr < funcctx->max_calls)
1993 /* Here we want to return another item: */
1994 <replaceable>user code</replaceable>
1995 <replaceable>obtain result Datum</replaceable>
1996 SRF_RETURN_NEXT(funcctx, result);
2000 /* Here we are done returning items and just need to clean up: */
2001 <replaceable>user code</replaceable>
2002 SRF_RETURN_DONE(funcctx);
2009 A complete example of a simple <acronym>SRF</> returning a composite type looks like:
2011 PG_FUNCTION_INFO_V1(testpassbyval);
2014 testpassbyval(PG_FUNCTION_ARGS)
2016 FuncCallContext *funcctx;
2020 AttInMetadata *attinmeta;
2022 /* stuff done only on the first call of the function */
2023 if (SRF_IS_FIRSTCALL())
2025 MemoryContext oldcontext;
2027 /* create a function context for cross-call persistence */
2028 funcctx = SRF_FIRSTCALL_INIT();
2030 /* switch to memory context appropriate for multiple function calls */
2031 oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
2033 /* total number of tuples to be returned */
2034 funcctx->max_calls = PG_GETARG_UINT32(0);
2036 /* Build a tuple description for a __testpassbyval tuple */
2037 tupdesc = RelationNameGetTupleDesc("__testpassbyval");
2040 * generate attribute metadata needed later to produce tuples from raw
2043 attinmeta = TupleDescGetAttInMetadata(tupdesc);
2044 funcctx->attinmeta = attinmeta;
2046 MemoryContextSwitchTo(oldcontext);
2049 /* stuff done on every call of the function */
2050 funcctx = SRF_PERCALL_SETUP();
2052 call_cntr = funcctx->call_cntr;
2053 max_calls = funcctx->max_calls;
2054 attinmeta = funcctx->attinmeta;
2056 if (call_cntr < max_calls) /* do when there is more left to send */
2063 * Prepare a values array for building the returned tuple.
2064 * This should be an array of C strings which will
2065 * be processed later by the type input functions.
2067 values = (char **) palloc(3 * sizeof(char *));
2068 values[0] = (char *) palloc(16 * sizeof(char));
2069 values[1] = (char *) palloc(16 * sizeof(char));
2070 values[2] = (char *) palloc(16 * sizeof(char));
2072 snprintf(values[0], 16, "%d", 1 * PG_GETARG_INT32(1));
2073 snprintf(values[1], 16, "%d", 2 * PG_GETARG_INT32(1));
2074 snprintf(values[2], 16, "%d", 3 * PG_GETARG_INT32(1));
2077 tuple = BuildTupleFromCStrings(attinmeta, values);
2079 /* make the tuple into a datum */
2080 result = HeapTupleGetDatum(tuple);
2082 /* clean up (this is not really necessary) */
2088 SRF_RETURN_NEXT(funcctx, result);
2090 else /* do when there is no more left */
2092 SRF_RETURN_DONE(funcctx);
2097 The SQL code to declare this function is:
2099 CREATE TYPE __testpassbyval AS (f1 integer, f2 integer, f3 integer);
2101 CREATE OR REPLACE FUNCTION testpassbyval(integer, integer) RETURNS SETOF __testpassbyval
2102 AS '<replaceable>filename</>', 'testpassbyval'
2103 LANGUAGE C IMMUTABLE STRICT;
2108 The directory <filename>contrib/tablefunc</> in the source
2109 distribution contains more examples of set-returning functions.
2114 <title>Polymorphic Arguments and Return Types</title>
2117 C-language functions may be declared to accept and
2118 return the polymorphic types
2119 <type>anyelement</type> and <type>anyarray</type>.
2120 See <xref linkend="extend-types-polymorphic"> for a more detailed explanation
2121 of polymorphic functions. When function arguments or return types
2122 are defined as polymorphic types, the function author cannot know
2123 in advance what data type it will be called with, or
2124 need to return. There are two routines provided in <filename>fmgr.h</>
2125 to allow a version-1 C function to discover the actual data types
2126 of its arguments and the type it is expected to return. The routines are
2127 called <literal>get_fn_expr_rettype(FmgrInfo *flinfo)</> and
2128 <literal>get_fn_expr_argtype(FmgrInfo *flinfo, int argnum)</>.
2129 They return the result or argument type OID, or <symbol>InvalidOid</symbol> if the
2130 information is not available.
2131 The structure <literal>flinfo</> is normally accessed as
2132 <literal>fcinfo->flinfo</>. The parameter <literal>argnum</>
2137 For example, suppose we want to write a function to accept a single
2138 element of any type, and return a one-dimensional array of that type:
2141 PG_FUNCTION_INFO_V1(make_array);
2143 make_array(PG_FUNCTION_ARGS)
2146 Oid element_type = get_fn_expr_argtype(fcinfo->flinfo, 0);
2155 if (!OidIsValid(element_type))
2156 elog(ERROR, "could not determine data type of input");
2158 /* get the provided element */
2159 element = PG_GETARG_DATUM(0);
2161 /* we have one dimension */
2163 /* and one element */
2165 /* and lower bound is 1 */
2168 /* get required info about the element type */
2169 get_typlenbyvalalign(element_type, &typlen, &typbyval, &typalign);
2171 /* now build the array */
2172 result = construct_md_array(&element, ndims, dims, lbs,
2173 element_type, typlen, typbyval, typalign);
2175 PG_RETURN_ARRAYTYPE_P(result);
2181 The following command declares the function
2182 <function>make_array</function> in SQL:
2185 CREATE FUNCTION make_array(anyelement) RETURNS anyarray
2186 AS '<replaceable>DIRECTORY</replaceable>/funcs', 'make_array'
2190 Note the use of <literal>STRICT</literal>; this is essential
2191 since the code is not bothering to test for a null input.
2196 <sect1 id="xfunc-overload">
2197 <title>Function Overloading</title>
2199 <indexterm zone="xfunc-overload">
2200 <primary>overloading</primary>
2201 <secondary>functions</secondary>
2205 More than one function may be defined with the same SQL name, so long
2206 as the arguments they take are different. In other words,
2207 function names can be <firstterm>overloaded</firstterm>. When a
2208 query is executed, the server will determine which function to
2209 call from the data types and the number of the provided arguments.
2210 Overloading can also be used to simulate functions with a variable
2211 number of arguments, up to a finite maximum number.
2215 A function may also have the same name as an attribute. (Recall
2216 that <literal>attribute(table)</literal> is equivalent to
2217 <literal>table.attribute</literal>.) In the case that there is an
2218 ambiguity between a function on a complex type and an attribute of
2219 the complex type, the attribute will always be used.
2223 When creating a family of overloaded functions, one should be
2224 careful not to create ambiguities. For instance, given the
2227 CREATE FUNCTION test(int, real) RETURNS ...
2228 CREATE FUNCTION test(smallint, double precision) RETURNS ...
2230 it is not immediately clear which function would be called with
2231 some trivial input like <literal>test(1, 1.5)</literal>. The
2232 currently implemented resolution rules are described in
2233 <xref linkend="typeconv">, but it is unwise to design a system that subtly
2234 relies on this behavior.
2238 When overloading C-language functions, there is an additional
2239 constraint: The C name of each function in the family of
2240 overloaded functions must be different from the C names of all
2241 other functions, either internal or dynamically loaded. If this
2242 rule is violated, the behavior is not portable. You might get a
2243 run-time linker error, or one of the functions will get called
2244 (usually the internal one). The alternative form of the
2245 <literal>AS</> clause for the SQL <command>CREATE
2246 FUNCTION</command> command decouples the SQL function name from
2247 the function name in the C source code. E.g.,
2249 CREATE FUNCTION test(int) RETURNS int
2250 AS '<replaceable>filename</>', 'test_1arg'
2252 CREATE FUNCTION test(int, int) RETURNS int
2253 AS '<replaceable>filename</>', 'test_2arg'
2256 The names of the C functions here reflect one of many possible conventions.
2260 <!-- Keep this comment at the end of the file
2265 sgml-minimize-attributes:nil
2266 sgml-always-quote-attributes:t
2269 sgml-parent-document:nil
2270 sgml-default-dtd-file:"./reference.ced"
2271 sgml-exposed-tags:nil
2272 sgml-local-catalogs:("/usr/lib/sgml/catalog")
2273 sgml-local-ecat-files:nil