2 $PostgreSQL: pgsql/doc/src/sgml/xfunc.sgml,v 1.80 2004/03/09 16:57:47 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 <command>CREATE FUNCTION</command> command to
68 understand the examples better.
69 Some examples from this chapter
70 can be found in <filename>funcs.sql</filename>
71 and <filename>funcs.c</filename> in the <filename>src/tutorial</>
72 directory in the <productname>PostgreSQL</productname> source distribution.
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. Note that because the syntax
108 of the <command>CREATE FUNCTION</command> command requires the body of the
109 function to be enclosed in single quotes, single quote marks
111 in the body of the function must be escaped, by writing two single
112 quotes (<literal>''</>) or a backslash (<literal>\'</>) where each
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 '
146 Notice that we defined a column alias within the function body for the result of the function
147 (with the name <literal>result</>), but this column alias is not visible
148 outside the function. Hence, the result is labeled <literal>one</>
149 instead of <literal>result</>.
153 It is almost as easy to define <acronym>SQL</acronym> functions
154 that take base types as arguments. In the example below, notice
155 how we refer to the arguments within the function as <literal>$1</>
159 CREATE FUNCTION add_em(integer, integer) RETURNS integer AS '
163 SELECT add_em(1, 2) AS answer;
172 Here is a more useful function, which might be used to debit a
176 CREATE FUNCTION tf1 (integer, numeric) RETURNS integer AS '
178 SET balance = balance - $2
179 WHERE accountno = $1;
184 A user could execute this function to debit account 17 by $100.00 as
188 SELECT tf1(17, 100.0);
193 In practice one would probably like a more useful result from the
194 function than a constant 1, so a more likely definition
198 CREATE FUNCTION tf1 (integer, numeric) RETURNS numeric AS '
200 SET balance = balance - $2
201 WHERE accountno = $1;
202 SELECT balance FROM bank WHERE accountno = $1;
206 which adjusts the balance and returns the new balance.
210 Any collection of commands in the <acronym>SQL</acronym>
211 language can be packaged together and defined as a function.
212 Besides <command>SELECT</command> queries,
213 the commands can include data modification (i.e.,
214 <command>INSERT</command>, <command>UPDATE</command>, and
215 <command>DELETE</command>). However, the final command
216 must be a <command>SELECT</command> that returns whatever is
217 specified as the function's return type. Alternatively, if you
218 want to define a SQL function that performs actions but has no
219 useful value to return, you can define it as returning <type>void</>.
220 In that case, the function body must not end with a <command>SELECT</command>.
224 CREATE FUNCTION clean_emp() RETURNS void AS '
226 WHERE salary <= 0;
240 <title><acronym>SQL</acronym> Functions on Composite Types</title>
243 When specifying functions with arguments of composite
244 types, we must not only specify which
245 argument we want (as we did above with <literal>$1</> and <literal>$2</literal>) but
246 also the attributes of that argument. For example, suppose that
247 <type>emp</type> is a table containing employee data, and therefore
248 also the name of the composite type of each row of the table. Here
249 is a function <function>double_salary</function> that computes what someone's
250 salary would be if it were doubled:
260 CREATE FUNCTION double_salary(emp) RETURNS integer AS '
261 SELECT $1.salary * 2 AS salary;
264 SELECT name, double_salary(emp) AS dream
266 WHERE emp.cubicle ~= point '(2,1)';
275 Notice the use of the syntax <literal>$1.salary</literal>
276 to select one field of the argument row value. Also notice
277 how the calling <command>SELECT</> command uses a table name to denote
278 the entire current row of that table as a composite value. The table
279 row can alternatively be referenced like this:
281 SELECT name, double_salary(emp.*) AS dream
283 WHERE emp.cubicle ~= point '(2,1)';
285 which emphasizes its row nature.
289 It is also possible to build a function that returns a composite type.
290 This is an example of a function
291 that returns a single <type>emp</type> row:
294 CREATE FUNCTION new_emp() RETURNS emp AS '
295 SELECT text ''None'' AS name,
298 point ''(2,2)'' AS cubicle;
302 In this example we have specified each of the attributes
303 with a constant value, but any computation
304 could have been substituted for these constants.
308 Note two important things about defining the function:
313 The select list order in the query must be exactly the same as
314 that in which the columns appear in the table associated
315 with the composite type. (Naming the columns, as we did above,
316 is irrelevant to the system.)
321 You must typecast the expressions to match the
322 definition of the composite type, or you will get errors like this:
325 ERROR: function declared to return emp returns varchar instead of text at column 1
334 A function that returns a row (composite type) can be used as a table
335 function, as described below. It can also be called in the context
336 of an SQL expression, but only when you
337 extract a single attribute out of the row or pass the entire row into
338 another function that accepts the same composite type.
342 This is an example of extracting an attribute out of a row type:
345 SELECT (new_emp()).name;
352 We need the extra parentheses to keep the parser from getting confused:
355 SELECT new_emp().name;
356 ERROR: syntax error at or near "." at character 17
361 Another option is to use
362 functional notation for extracting an attribute. The simple way
363 to explain this is that we can use the
364 notations <literal>attribute(table)</> and <literal>table.attribute</>
368 SELECT name(new_emp());
376 -- This is the same as:
377 -- SELECT emp.name AS youngster FROM emp WHERE emp.age < 30
379 SELECT name(emp) AS youngster
381 WHERE age(emp) < 30;
390 The other way to use a function returning a row result is to declare a
391 second function accepting a row type argument and pass the
392 result of the first function to it:
395 CREATE FUNCTION getname(emp) RETURNS text AS '
399 SELECT getname(new_emp());
409 <title><acronym>SQL</acronym> Functions as Table Sources</title>
412 All SQL functions may be used in the <literal>FROM</> clause of a query,
413 but it is particularly useful for functions returning composite types.
414 If the function is defined to return a base type, the table function
415 produces a one-column table. If the function is defined to return
416 a composite type, the table function produces a column for each attribute
417 of the composite type.
424 CREATE TABLE foo (fooid int, foosubid int, fooname text);
425 INSERT INTO foo VALUES (1, 1, 'Joe');
426 INSERT INTO foo VALUES (1, 2, 'Ed');
427 INSERT INTO foo VALUES (2, 1, 'Mary');
429 CREATE FUNCTION getfoo(int) RETURNS foo AS '
430 SELECT * FROM foo WHERE fooid = $1;
433 SELECT *, upper(fooname) FROM getfoo(1) AS t1;
435 fooid | foosubid | fooname | upper
436 -------+----------+---------+-------
441 As the example shows, we can work with the columns of the function's
442 result just the same as if they were columns of a regular table.
446 Note that we only got one row out of the function. This is because
447 we did not use <literal>SETOF</>. This is described in the next section.
452 <title><acronym>SQL</acronym> Functions Returning Sets</title>
455 When an SQL function is declared as returning <literal>SETOF
456 <replaceable>sometype</></literal>, the function's final
457 <command>SELECT</> query is executed to completion, and each row it
458 outputs is returned as an element of the result set.
462 This feature is normally used when calling the function in the <literal>FROM</>
463 clause. In this case each row returned by the function becomes
464 a row of the table seen by the query. For example, assume that
465 table <literal>foo</> has the same contents as above, and we say:
468 CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS '
469 SELECT * FROM foo WHERE fooid = $1;
472 SELECT * FROM getfoo(1) AS t1;
477 fooid | foosubid | fooname
478 -------+----------+---------
486 Currently, functions returning sets may also be called in the select list
487 of a query. For each row that the query
488 generates by itself, the function returning set is invoked, and an output
489 row is generated for each element of the function's result set. Note,
490 however, that this capability is deprecated and may be removed in future
491 releases. The following is an example function returning a set from the
495 CREATE FUNCTION listchildren(text) RETURNS SETOF text AS
496 'SELECT name FROM nodes WHERE parent = $1'
510 SELECT listchildren('Top');
518 SELECT name, listchildren(name) FROM nodes;
520 --------+--------------
529 In the last <command>SELECT</command>,
530 notice that no output row appears for <literal>Child2</>, <literal>Child3</>, etc.
531 This happens because <function>listchildren</function> returns an empty set
532 for those arguments, so no result rows are generated.
537 <title>Polymorphic <acronym>SQL</acronym> Functions</title>
540 <acronym>SQL</acronym> functions may be declared to accept and
541 return the polymorphic types <type>anyelement</type> and
542 <type>anyarray</type>. See <xref
543 linkend="extend-types-polymorphic"> for a more detailed
544 explanation of polymorphic functions. Here is a polymorphic
545 function <function>make_array</function> that builds up an array
546 from two arbitrary data type elements:
548 CREATE FUNCTION make_array(anyelement, anyelement) RETURNS anyarray AS '
549 SELECT ARRAY[$1, $2];
552 SELECT make_array(1, 2) AS intarray, make_array('a'::text, 'b') AS textarray;
554 ----------+-----------
561 Notice the use of the typecast <literal>'a'::text</literal>
562 to specify that the argument is of type <type>text</type>. This is
563 required if the argument is just a string literal, since otherwise
564 it would be treated as type
565 <type>unknown</type>, and array of <type>unknown</type> is not a valid
567 Without the typecast, you will get errors like this:
570 ERROR: could not determine "anyarray"/"anyelement" type because input has type "unknown"
576 It is permitted to have polymorphic arguments with a deterministic
577 return type, but the converse is not. For example:
579 CREATE FUNCTION is_greater(anyelement, anyelement) RETURNS boolean AS '
583 SELECT is_greater(1, 2);
589 CREATE FUNCTION invalid_func() RETURNS anyelement AS '
592 ERROR: cannot determine result data type
593 DETAIL: A function returning "anyarray" or "anyelement" must have at least one argument of either type.
599 <sect1 id="xfunc-pl">
600 <title>Procedural Language Functions</title>
603 Procedural languages aren't built into the
604 <productname>PostgreSQL</productname> server; they are offered
605 by loadable modules. Please refer to the documentation of the
606 procedural language in question for details about the syntax and how the
607 function body is interpreted for each language.
611 There are currently four procedural languages available in the
612 standard <productname>PostgreSQL</productname> distribution:
613 <application>PL/pgSQL</application>, <application>PL/Tcl</application>,
614 <application>PL/Perl</application>, and
615 <application>PL/Python</application>.
616 Refer to <xref linkend="xplang"> for more information.
617 Other languages can be defined by users.
618 The basics of developing a new procedural language are covered in <xref
619 linkend="plhandler">.
623 <sect1 id="xfunc-internal">
624 <title>Internal Functions</title>
626 <indexterm zone="xfunc-internal"><primary>function</><secondary>internal</></>
629 Internal functions are functions written in C that have been statically
630 linked into the <productname>PostgreSQL</productname> server.
631 The <quote>body</quote> of the function definition
632 specifies the C-language name of the function, which need not be the
633 same as the name being declared for SQL use.
634 (For reasons of backwards compatibility, an empty body
635 is accepted as meaning that the C-language function name is the
636 same as the SQL name.)
640 Normally, all internal functions present in the
641 server are declared during the initialization of the database cluster (<command>initdb</command>),
642 but a user could use <command>CREATE FUNCTION</command>
643 to create additional alias names for an internal function.
644 Internal functions are declared in <command>CREATE FUNCTION</command>
645 with language name <literal>internal</literal>. For instance, to
646 create an alias for the <function>sqrt</function> function:
648 CREATE FUNCTION square_root(double precision) RETURNS double precision
653 (Most internal functions expect to be declared <quote>strict</quote>.)
658 Not all <quote>predefined</quote> functions are
659 <quote>internal</quote> in the above sense. Some predefined
660 functions are written in SQL.
666 <title>C-Language Functions</title>
668 <indexterm zone="xfunc-sql">
669 <primary>function</primary>
670 <secondary>user-defined</secondary>
671 <tertiary>in C</tertiary>
675 User-defined functions can be written in C (or a language that can
676 be made compatible with C, such as C++). Such functions are
677 compiled into dynamically loadable objects (also called shared
678 libraries) and are loaded by the server on demand. The dynamic
679 loading feature is what distinguishes <quote>C language</> functions
680 from <quote>internal</> functions --- the actual coding conventions
681 are essentially the same for both. (Hence, the standard internal
682 function library is a rich source of coding examples for user-defined
687 Two different calling conventions are currently used for C functions.
688 The newer <quote>version 1</quote> calling convention is indicated by writing
689 a <literal>PG_FUNCTION_INFO_V1()</literal> macro call for the function,
690 as illustrated below. Lack of such a macro indicates an old-style
691 (<quote>version 0</quote>) function. The language name specified in <command>CREATE FUNCTION</command>
692 is <literal>C</literal> in either case. Old-style functions are now deprecated
693 because of portability problems and lack of functionality, but they
694 are still supported for compatibility reasons.
697 <sect2 id="xfunc-c-dynload">
698 <title>Dynamic Loading</title>
700 <indexterm zone="xfunc-c-dynload">
701 <primary>dynamic loading</primary>
705 The first time a user-defined function in a particular
706 loadable object file is called in a session,
707 the dynamic loader loads that object file into memory so that the
708 function can be called. The <command>CREATE FUNCTION</command>
709 for a user-defined C function must therefore specify two pieces of
710 information for the function: the name of the loadable
711 object file, and the C name (link symbol) of the specific function to call
712 within that object file. If the C name is not explicitly specified then
713 it is assumed to be the same as the SQL function name.
717 The following algorithm is used to locate the shared object file
718 based on the name given in the <command>CREATE FUNCTION</command>
724 If the name is an absolute path, the given file is loaded.
730 If the name starts with the string <literal>$libdir</literal>,
731 that part is replaced by the <productname>PostgreSQL</> package
733 name, which is determined at build time.<indexterm><primary>$libdir</></>
739 If the name does not contain a directory part, the file is
740 searched for in the path specified by the configuration variable
741 <xref linkend="guc-dynamic-library-path">.<indexterm><primary>dynamic_library_path</></>
747 Otherwise (the file was not found in the path, or it contains a
748 non-absolute directory part), the dynamic loader will try to
749 take the name as given, which will most likely fail. (It is
750 unreliable to depend on the current working directory.)
755 If this sequence does not work, the platform-specific shared
756 library file name extension (often <filename>.so</filename>) is
757 appended to the given name and this sequence is tried again. If
758 that fails as well, the load will fail.
762 The user ID the <productname>PostgreSQL</productname> server runs
763 as must be able to traverse the path to the file you intend to
764 load. Making the file or a higher-level directory not readable
765 and/or not executable by the <systemitem>postgres</systemitem>
766 user is a common mistake.
770 In any case, the file name that is given in the
771 <command>CREATE FUNCTION</command> command is recorded literally
772 in the system catalogs, so if the file needs to be loaded again
773 the same procedure is applied.
778 <productname>PostgreSQL</productname> will not compile a C function
779 automatically. The object file must be compiled before it is referenced
781 FUNCTION</> command. See <xref linkend="dfunc"> for additional
787 After it is used for the first time, a dynamically loaded object
788 file is retained in memory. Future calls in the same session to
789 the function(s) in that file will only incur the small overhead of
790 a symbol table lookup. If you need to force a reload of an object
791 file, for example after recompiling it, use the <command>LOAD</>
792 command or begin a fresh session.
796 It is recommended to locate shared libraries either relative to
797 <literal>$libdir</literal> or through the dynamic library path.
798 This simplifies version upgrades if the new installation is at a
799 different location. The actual directory that
800 <literal>$libdir</literal> stands for can be found out with the
801 command <literal>pg_config --pkglibdir</literal>.
805 Before <productname>PostgreSQL</productname> release 7.2, only
806 exact absolute paths to object files could be specified in
807 <command>CREATE FUNCTION</>. This approach is now deprecated
808 since it makes the function definition unnecessarily unportable.
809 It's best to specify just the shared library name with no path nor
810 extension, and let the search mechanism provide that information
815 <sect2 id="xfunc-c-basetype">
816 <title>Base Types in C-Language Functions</title>
818 <indexterm zone="xfunc-c-basetype">
819 <primary>data type</primary>
820 <secondary>internal organisation</secondary>
824 To know how to write C-language functions, you need to know how
825 <productname>PostgreSQL</productname> internally represents base
826 data types and how they can be passed to and from functions.
827 Internally, <productname>PostgreSQL</productname> regards a base
828 type as a <quote>blob of memory</quote>. The user-defined
829 functions that you define over a type in turn define the way that
830 <productname>PostgreSQL</productname> can operate on it. That
831 is, <productname>PostgreSQL</productname> will only store and
832 retrieve the data from disk and use your user-defined functions
833 to input, process, and output the data.
837 Base types can have one of three internal formats:
842 pass by value, fixed-length
847 pass by reference, fixed-length
852 pass by reference, variable-length
859 By-value types can only be 1, 2, or 4 bytes in length
860 (also 8 bytes, if <literal>sizeof(Datum)</literal> is 8 on your machine).
861 You should be careful
862 to define your types such that they will be the same
863 size (in bytes) on all architectures. For example, the
864 <literal>long</literal> type is dangerous because it
865 is 4 bytes on some machines and 8 bytes on others, whereas
866 <type>int</type> type is 4 bytes on most
867 Unix machines. A reasonable implementation of
868 the <type>int4</type> type on Unix
872 /* 4-byte integer, passed by value */
878 On the other hand, fixed-length types of any size may
879 be passed by-reference. For example, here is a sample
880 implementation of a <productname>PostgreSQL</productname> type:
883 /* 16-byte structure, passed by reference */
890 Only pointers to such types can be used when passing
891 them in and out of <productname>PostgreSQL</productname> functions.
892 To return a value of such a type, allocate the right amount of
893 memory with <literal>palloc</literal>, fill in the allocated memory,
894 and return a pointer to it. (You can also return an input value
895 that has the same type as the return value directly by returning
896 the pointer to the input value. <emphasis>Never</> modify the
897 contents of a pass-by-reference input value, however.)
901 Finally, all variable-length types must also be passed
902 by reference. All variable-length types must begin
903 with a length field of exactly 4 bytes, and all data to
904 be stored within that type must be located in the memory
905 immediately following that length field. The
906 length field contains the total length of the structure,
907 that is, it includes the size of the length field
912 As an example, we can define the type <type>text</type> as
922 Obviously, the data field declared here is not long enough to hold
923 all possible strings. Since it's impossible to declare a variable-size
924 structure in <acronym>C</acronym>, we rely on the knowledge that the
925 <acronym>C</acronym> compiler won't range-check array subscripts. We
926 just allocate the necessary amount of space and then access the array as
927 if it were declared the right length. (This is a common trick, which
928 you can read about in many textbooks about C.)
933 variable-length types, we must be careful to allocate
934 the correct amount of memory and set the length field correctly.
935 For example, if we wanted to store 40 bytes in a <structname>text</>
936 structure, we might use a code fragment like this:
939 #include "postgres.h"
941 char buffer[40]; /* our source data */
943 text *destination = (text *) palloc(VARHDRSZ + 40);
944 destination->length = VARHDRSZ + 40;
945 memcpy(destination->data, buffer, 40);
949 <literal>VARHDRSZ</> is the same as <literal>sizeof(int4)</>, but
950 it's considered good style to use the macro <literal>VARHDRSZ</>
951 to refer to the size of the overhead for a variable-length type.
955 <xref linkend="xfunc-c-type-table"> specifies which C type
956 corresponds to which SQL type when writing a C-language function
957 that uses a built-in type of <productname>PostgreSQL</>.
958 The <quote>Defined In</quote> column gives the header file that
959 needs to be included to get the type definition. (The actual
960 definition may be in a different file that is included by the
961 listed file. It is recommended that users stick to the defined
962 interface.) Note that you should always include
963 <filename>postgres.h</filename> first in any source file, because
964 it declares a number of things that you will need anyway.
967 <table tocentry="1" id="xfunc-c-type-table">
968 <title>Equivalent C Types for Built-In SQL Types</title>
985 <entry><type>abstime</type></entry>
986 <entry><type>AbsoluteTime</type></entry>
987 <entry><filename>utils/nabstime.h</filename></entry>
990 <entry><type>boolean</type></entry>
991 <entry><type>bool</type></entry>
992 <entry><filename>postgres.h</filename> (maybe compiler built-in)</entry>
995 <entry><type>box</type></entry>
996 <entry><type>BOX*</type></entry>
997 <entry><filename>utils/geo_decls.h</filename></entry>
1000 <entry><type>bytea</type></entry>
1001 <entry><type>bytea*</type></entry>
1002 <entry><filename>postgres.h</filename></entry>
1005 <entry><type>"char"</type></entry>
1006 <entry><type>char</type></entry>
1007 <entry>(compiler built-in)</entry>
1010 <entry><type>character</type></entry>
1011 <entry><type>BpChar*</type></entry>
1012 <entry><filename>postgres.h</filename></entry>
1015 <entry><type>cid</type></entry>
1016 <entry><type>CommandId</type></entry>
1017 <entry><filename>postgres.h</filename></entry>
1020 <entry><type>date</type></entry>
1021 <entry><type>DateADT</type></entry>
1022 <entry><filename>utils/date.h</filename></entry>
1025 <entry><type>smallint</type> (<type>int2</type>)</entry>
1026 <entry><type>int2</type> or <type>int16</type></entry>
1027 <entry><filename>postgres.h</filename></entry>
1030 <entry><type>int2vector</type></entry>
1031 <entry><type>int2vector*</type></entry>
1032 <entry><filename>postgres.h</filename></entry>
1035 <entry><type>integer</type> (<type>int4</type>)</entry>
1036 <entry><type>int4</type> or <type>int32</type></entry>
1037 <entry><filename>postgres.h</filename></entry>
1040 <entry><type>real</type> (<type>float4</type>)</entry>
1041 <entry><type>float4*</type></entry>
1042 <entry><filename>postgres.h</filename></entry>
1045 <entry><type>double precision</type> (<type>float8</type>)</entry>
1046 <entry><type>float8*</type></entry>
1047 <entry><filename>postgres.h</filename></entry>
1050 <entry><type>interval</type></entry>
1051 <entry><type>Interval*</type></entry>
1052 <entry><filename>utils/timestamp.h</filename></entry>
1055 <entry><type>lseg</type></entry>
1056 <entry><type>LSEG*</type></entry>
1057 <entry><filename>utils/geo_decls.h</filename></entry>
1060 <entry><type>name</type></entry>
1061 <entry><type>Name</type></entry>
1062 <entry><filename>postgres.h</filename></entry>
1065 <entry><type>oid</type></entry>
1066 <entry><type>Oid</type></entry>
1067 <entry><filename>postgres.h</filename></entry>
1070 <entry><type>oidvector</type></entry>
1071 <entry><type>oidvector*</type></entry>
1072 <entry><filename>postgres.h</filename></entry>
1075 <entry><type>path</type></entry>
1076 <entry><type>PATH*</type></entry>
1077 <entry><filename>utils/geo_decls.h</filename></entry>
1080 <entry><type>point</type></entry>
1081 <entry><type>POINT*</type></entry>
1082 <entry><filename>utils/geo_decls.h</filename></entry>
1085 <entry><type>regproc</type></entry>
1086 <entry><type>regproc</type></entry>
1087 <entry><filename>postgres.h</filename></entry>
1090 <entry><type>reltime</type></entry>
1091 <entry><type>RelativeTime</type></entry>
1092 <entry><filename>utils/nabstime.h</filename></entry>
1095 <entry><type>text</type></entry>
1096 <entry><type>text*</type></entry>
1097 <entry><filename>postgres.h</filename></entry>
1100 <entry><type>tid</type></entry>
1101 <entry><type>ItemPointer</type></entry>
1102 <entry><filename>storage/itemptr.h</filename></entry>
1105 <entry><type>time</type></entry>
1106 <entry><type>TimeADT</type></entry>
1107 <entry><filename>utils/date.h</filename></entry>
1110 <entry><type>time with time zone</type></entry>
1111 <entry><type>TimeTzADT</type></entry>
1112 <entry><filename>utils/date.h</filename></entry>
1115 <entry><type>timestamp</type></entry>
1116 <entry><type>Timestamp*</type></entry>
1117 <entry><filename>utils/timestamp.h</filename></entry>
1120 <entry><type>tinterval</type></entry>
1121 <entry><type>TimeInterval</type></entry>
1122 <entry><filename>utils/nabstime.h</filename></entry>
1125 <entry><type>varchar</type></entry>
1126 <entry><type>VarChar*</type></entry>
1127 <entry><filename>postgres.h</filename></entry>
1130 <entry><type>xid</type></entry>
1131 <entry><type>TransactionId</type></entry>
1132 <entry><filename>postgres.h</filename></entry>
1139 Now that we've gone over all of the possible structures
1140 for base types, we can show some examples of real functions.
1145 <title>Calling Conventions Version 0 for C-Language Functions</title>
1148 We present the <quote>old style</quote> calling convention first --- although
1149 this approach is now deprecated, it's easier to get a handle on
1150 initially. In the version-0 method, the arguments and result
1151 of the C function are just declared in normal C style, but being
1152 careful to use the C representation of each SQL data type as shown
1157 Here are some examples:
1160 #include "postgres.h"
1161 #include <string.h>
1171 /* by reference, fixed length */
1174 add_one_float8(float8 *arg)
1176 float8 *result = (float8 *) palloc(sizeof(float8));
1178 *result = *arg + 1.0;
1184 makepoint(Point *pointx, Point *pointy)
1186 Point *new_point = (Point *) palloc(sizeof(Point));
1188 new_point->x = pointx->x;
1189 new_point->y = pointy->y;
1194 /* by reference, variable length */
1200 * VARSIZE is the total size of the struct in bytes.
1202 text *new_t = (text *) palloc(VARSIZE(t));
1203 VARATT_SIZEP(new_t) = VARSIZE(t);
1205 * VARDATA is a pointer to the data region of the struct.
1207 memcpy((void *) VARDATA(new_t), /* destination */
1208 (void *) VARDATA(t), /* source */
1209 VARSIZE(t)-VARHDRSZ); /* how many bytes */
1214 concat_text(text *arg1, text *arg2)
1216 int32 new_text_size = VARSIZE(arg1) + VARSIZE(arg2) - VARHDRSZ;
1217 text *new_text = (text *) palloc(new_text_size);
1219 VARATT_SIZEP(new_text) = new_text_size;
1220 memcpy(VARDATA(new_text), VARDATA(arg1), VARSIZE(arg1)-VARHDRSZ);
1221 memcpy(VARDATA(new_text) + (VARSIZE(arg1)-VARHDRSZ),
1222 VARDATA(arg2), VARSIZE(arg2)-VARHDRSZ);
1229 Supposing that the above code has been prepared in file
1230 <filename>funcs.c</filename> and compiled into a shared object,
1231 we could define the functions to <productname>PostgreSQL</productname>
1232 with commands like this:
1235 CREATE FUNCTION add_one(integer) RETURNS integer
1236 AS '<replaceable>DIRECTORY</replaceable>/funcs', 'add_one'
1239 -- note overloading of SQL function name "add_one"
1240 CREATE FUNCTION add_one(double precision) RETURNS double precision
1241 AS '<replaceable>DIRECTORY</replaceable>/funcs', 'add_one_float8'
1244 CREATE FUNCTION makepoint(point, point) RETURNS point
1245 AS '<replaceable>DIRECTORY</replaceable>/funcs', 'makepoint'
1248 CREATE FUNCTION copytext(text) RETURNS text
1249 AS '<replaceable>DIRECTORY</replaceable>/funcs', 'copytext'
1252 CREATE FUNCTION concat_text(text, text) RETURNS text
1253 AS '<replaceable>DIRECTORY</replaceable>/funcs', 'concat_text',
1259 Here, <replaceable>DIRECTORY</replaceable> stands for the
1260 directory of the shared library file (for instance the
1261 <productname>PostgreSQL</productname> tutorial directory, which
1262 contains the code for the examples used in this section).
1263 (Better style would be to use just <literal>'funcs'</> in the
1264 <literal>AS</> clause, after having added
1265 <replaceable>DIRECTORY</replaceable> to the search path. In any
1266 case, we may omit the system-specific extension for a shared
1267 library, commonly <literal>.so</literal> or
1268 <literal>.sl</literal>.)
1272 Notice that we have specified the functions as <quote>strict</quote>,
1274 the system should automatically assume a null result if any input
1275 value is null. By doing this, we avoid having to check for null inputs
1276 in the function code. Without this, we'd have to check for null values
1277 explicitly, by checking for a null pointer for each
1278 pass-by-reference argument. (For pass-by-value arguments, we don't
1279 even have a way to check!)
1283 Although this calling convention is simple to use,
1284 it is not very portable; on some architectures there are problems
1285 with passing data types that are smaller than <type>int</type> this way. Also, there is
1286 no simple way to return a null result, nor to cope with null arguments
1287 in any way other than making the function strict. The version-1
1288 convention, presented next, overcomes these objections.
1293 <title>Calling Conventions Version 1 for C-Language Functions</title>
1296 The version-1 calling convention relies on macros to suppress most
1297 of the complexity of passing arguments and results. The C declaration
1298 of a version-1 function is always
1300 Datum funcname(PG_FUNCTION_ARGS)
1302 In addition, the macro call
1304 PG_FUNCTION_INFO_V1(funcname);
1306 must appear in the same source file. (Conventionally. it's
1307 written just before the function itself.) This macro call is not
1308 needed for <literal>internal</>-language functions, since
1309 <productname>PostgreSQL</> assumes that all internal functions
1310 use the version-1 convention. It is, however, required for
1311 dynamically-loaded functions.
1315 In a version-1 function, each actual argument is fetched using a
1316 <function>PG_GETARG_<replaceable>xxx</replaceable>()</function>
1317 macro that corresponds to the argument's data type, and the
1318 result is returned using a
1319 <function>PG_RETURN_<replaceable>xxx</replaceable>()</function>
1320 macro for the return type.
1321 <function>PG_GETARG_<replaceable>xxx</replaceable>()</function>
1322 takes as its argument the number of the function argument to
1323 fetch, where the count starts at 0.
1324 <function>PG_RETURN_<replaceable>xxx</replaceable>()</function>
1325 takes as its argument the actual value to return.
1329 Here we show the same functions as above, coded in version-1 style:
1332 #include "postgres.h"
1333 #include <string.h>
1338 PG_FUNCTION_INFO_V1(add_one);
1341 add_one(PG_FUNCTION_ARGS)
1343 int32 arg = PG_GETARG_INT32(0);
1345 PG_RETURN_INT32(arg + 1);
1348 /* b reference, fixed length */
1350 PG_FUNCTION_INFO_V1(add_one_float8);
1353 add_one_float8(PG_FUNCTION_ARGS)
1355 /* The macros for FLOAT8 hide its pass-by-reference nature. */
1356 float8 arg = PG_GETARG_FLOAT8(0);
1358 PG_RETURN_FLOAT8(arg + 1.0);
1361 PG_FUNCTION_INFO_V1(makepoint);
1364 makepoint(PG_FUNCTION_ARGS)
1366 /* Here, the pass-by-reference nature of Point is not hidden. */
1367 Point *pointx = PG_GETARG_POINT_P(0);
1368 Point *pointy = PG_GETARG_POINT_P(1);
1369 Point *new_point = (Point *) palloc(sizeof(Point));
1371 new_point->x = pointx->x;
1372 new_point->y = pointy->y;
1374 PG_RETURN_POINT_P(new_point);
1377 /* by reference, variable length */
1379 PG_FUNCTION_INFO_V1(copytext);
1382 copytext(PG_FUNCTION_ARGS)
1384 text *t = PG_GETARG_TEXT_P(0);
1386 * VARSIZE is the total size of the struct in bytes.
1388 text *new_t = (text *) palloc(VARSIZE(t));
1389 VARATT_SIZEP(new_t) = VARSIZE(t);
1391 * VARDATA is a pointer to the data region of the struct.
1393 memcpy((void *) VARDATA(new_t), /* destination */
1394 (void *) VARDATA(t), /* source */
1395 VARSIZE(t)-VARHDRSZ); /* how many bytes */
1396 PG_RETURN_TEXT_P(new_t);
1399 PG_FUNCTION_INFO_V1(concat_text);
1402 concat_text(PG_FUNCTION_ARGS)
1404 text *arg1 = PG_GETARG_TEXT_P(0);
1405 text *arg2 = PG_GETARG_TEXT_P(1);
1406 int32 new_text_size = VARSIZE(arg1) + VARSIZE(arg2) - VARHDRSZ;
1407 text *new_text = (text *) palloc(new_text_size);
1409 VARATT_SIZEP(new_text) = new_text_size;
1410 memcpy(VARDATA(new_text), VARDATA(arg1), VARSIZE(arg1)-VARHDRSZ);
1411 memcpy(VARDATA(new_text) + (VARSIZE(arg1)-VARHDRSZ),
1412 VARDATA(arg2), VARSIZE(arg2)-VARHDRSZ);
1413 PG_RETURN_TEXT_P(new_text);
1419 The <command>CREATE FUNCTION</command> commands are the same as
1420 for the version-0 equivalents.
1424 At first glance, the version-1 coding conventions may appear to
1425 be just pointless obscurantism. They do, however, offer a number
1426 of improvements, because the macros can hide unnecessary detail.
1427 An example is that in coding <function>add_one_float8</>, we no longer need to
1428 be aware that <type>float8</type> is a pass-by-reference type. Another
1429 example is that the <literal>GETARG</> macros for variable-length types allow
1430 for more efficient fetching of <quote>toasted</quote> (compressed or
1431 out-of-line) values.
1435 One big improvement in version-1 functions is better handling of null
1436 inputs and results. The macro <function>PG_ARGISNULL(<replaceable>n</>)</function>
1437 allows a function to test whether each input is null. (Of course, doing
1438 this is only necessary in functions not declared <quote>strict</>.)
1440 <function>PG_GETARG_<replaceable>xxx</replaceable>()</function> macros,
1441 the input arguments are counted beginning at zero. Note that one
1442 should refrain from executing
1443 <function>PG_GETARG_<replaceable>xxx</replaceable>()</function> until
1444 one has verified that the argument isn't null.
1445 To return a null result, execute <function>PG_RETURN_NULL()</function>;
1446 this works in both strict and nonstrict functions.
1450 Other options provided in the new-style interface are two
1452 <function>PG_GETARG_<replaceable>xxx</replaceable>()</function>
1453 macros. The first of these,
1454 <function>PG_GETARG_<replaceable>xxx</replaceable>_COPY()</function>,
1455 guarantees to return a copy of the specified argument that is
1456 safe for writing into. (The normal macros will sometimes return a
1457 pointer to a value that is physically stored in a table, which
1458 must not be written to. Using the
1459 <function>PG_GETARG_<replaceable>xxx</replaceable>_COPY()</function>
1460 macros guarantees a writable result.)
1461 The second variant consists of the
1462 <function>PG_GETARG_<replaceable>xxx</replaceable>_SLICE()</function>
1463 macros which take three arguments. The first is the number of the
1464 function argument (as above). The second and third are the offset and
1465 length of the segment to be returned. Offsets are counted from
1466 zero, and a negative length requests that the remainder of the
1467 value be returned. These macros provide more efficient access to
1468 parts of large values in the case where they have storage type
1469 <quote>external</quote>. (The storage type of a column can be specified using
1470 <literal>ALTER TABLE <replaceable>tablename</replaceable> ALTER
1471 COLUMN <replaceable>colname</replaceable> SET STORAGE
1472 <replaceable>storagetype</replaceable></literal>. <replaceable>storagetype</replaceable> is one of
1473 <literal>plain</>, <literal>external</>, <literal>extended</literal>,
1474 or <literal>main</>.)
1478 Finally, the version-1 function call conventions make it possible
1479 to return set results (<xref linkend="xfunc-c-return-set">) and
1480 implement trigger functions (<xref linkend="triggers">) and
1481 procedural-language call handlers (<xref
1482 linkend="plhandler">). Version-1 code is also more
1483 portable than version-0, because it does not break restrictions
1484 on function call protocol in the C standard. For more details
1485 see <filename>src/backend/utils/fmgr/README</filename> in the
1486 source distribution.
1491 <title>Writing Code</title>
1494 Before we turn to the more advanced topics, we should discuss
1495 some coding rules for <productname>PostgreSQL</productname>
1496 C-language functions. While it may be possible to load functions
1497 written in languages other than C into
1498 <productname>PostgreSQL</productname>, this is usually difficult
1499 (when it is possible at all) because other languages, such as
1500 C++, FORTRAN, or Pascal often do not follow the same calling
1501 convention as C. That is, other languages do not pass argument
1502 and return values between functions in the same way. For this
1503 reason, we will assume that your C-language functions are
1504 actually written in C.
1508 The basic rules for writing and building C functions are as follows:
1513 Use <literal>pg_config
1514 --includedir-server</literal><indexterm><primary>pg_config</><secondary>with user-defined C functions</></>
1515 to find out where the <productname>PostgreSQL</> server header
1516 files are installed on your system (or the system that your
1517 users will be running on). This option is new with
1518 <productname>PostgreSQL</> 7.2. For
1519 <productname>PostgreSQL</> 7.1 you should use the option
1520 <option>--includedir</option>. (<command>pg_config</command>
1521 will exit with a non-zero status if it encounters an unknown
1522 option.) For releases prior to 7.1 you will have to guess,
1523 but since that was before the current calling conventions were
1524 introduced, it is unlikely that you want to support those
1531 When allocating memory, use the
1532 <productname>PostgreSQL</productname> functions
1533 <function>palloc</function><indexterm><primary>palloc</></> and <function>pfree</function><indexterm><primary>pfree</></>
1534 instead of the corresponding C library functions
1535 <function>malloc</function> and <function>free</function>.
1536 The memory allocated by <function>palloc</function> will be
1537 freed automatically at the end of each transaction, preventing
1544 Always zero the bytes of your structures using
1545 <function>memset</function>. Without this, it's difficult to
1546 support hash indexes or hash joins, as you must pick out only
1547 the significant bits of your data structure to compute a hash.
1548 Even if you initialize all fields of your structure, there may be
1549 alignment padding (holes in the structure) that may contain
1556 Most of the internal <productname>PostgreSQL</productname>
1557 types are declared in <filename>postgres.h</filename>, while
1558 the function manager interfaces
1559 (<symbol>PG_FUNCTION_ARGS</symbol>, etc.) are in
1560 <filename>fmgr.h</filename>, so you will need to include at
1561 least these two files. For portability reasons it's best to
1562 include <filename>postgres.h</filename> <emphasis>first</>,
1563 before any other system or user header files. Including
1564 <filename>postgres.h</filename> will also include
1565 <filename>elog.h</filename> and <filename>palloc.h</filename>
1572 Symbol names defined within object files must not conflict
1573 with each other or with symbols defined in the
1574 <productname>PostgreSQL</productname> server executable. You
1575 will have to rename your functions or variables if you get
1576 error messages to this effect.
1582 Compiling and linking your code so that it can be dynamically
1583 loaded into <productname>PostgreSQL</productname> always
1584 requires special flags. See <xref linkend="dfunc"> for a
1585 detailed explanation of how to do it for your particular
1596 <title>Composite-Type Arguments in C-Language Functions</title>
1599 Composite types do not have a fixed layout like C
1600 structures. Instances of a composite type may contain
1601 null fields. In addition, composite types that are
1602 part of an inheritance hierarchy may have different
1603 fields than other members of the same inheritance hierarchy.
1604 Therefore, <productname>PostgreSQL</productname> provides
1605 a function interface for accessing fields of composite types
1610 Suppose we want to write a function to answer the query
1613 SELECT name, c_overpaid(emp, 1500) AS overpaid
1615 WHERE name = 'Bill' OR name = 'Sam';
1618 Using call conventions version 0, we can define
1619 <function>c_overpaid</> as:
1622 #include "postgres.h"
1623 #include "executor/executor.h" /* for GetAttributeByName() */
1626 c_overpaid(TupleTableSlot *t, /* the current row of emp */
1632 salary = DatumGetInt32(GetAttributeByName(t, "salary", &isnull));
1635 return salary > limit;
1639 In version-1 coding, the above would look like this:
1642 #include "postgres.h"
1643 #include "executor/executor.h" /* for GetAttributeByName() */
1645 PG_FUNCTION_INFO_V1(c_overpaid);
1648 c_overpaid(PG_FUNCTION_ARGS)
1650 TupleTableSlot *t = (TupleTableSlot *) PG_GETARG_POINTER(0);
1651 int32 limit = PG_GETARG_INT32(1);
1655 salary = DatumGetInt32(GetAttributeByName(t, "salary", &isnull));
1657 PG_RETURN_BOOL(false);
1658 /* Alternatively, we might prefer to do PG_RETURN_NULL() for null salary. */
1660 PG_RETURN_BOOL(salary > limit);
1666 <function>GetAttributeByName</function> is the
1667 <productname>PostgreSQL</productname> system function that
1668 returns attributes out of the specified row. It has
1669 three arguments: the argument of type <type>TupleTableSlot*</type> passed into
1670 the function, the name of the desired attribute, and a
1671 return parameter that tells whether the attribute
1672 is null. <function>GetAttributeByName</function> returns a <type>Datum</type>
1673 value that you can convert to the proper data type by using the
1674 appropriate <function>DatumGet<replaceable>XXX</replaceable>()</function> macro.
1678 The following command declares the function
1679 <function>c_overpaid</function> in SQL:
1682 CREATE FUNCTION c_overpaid(emp, integer) RETURNS boolean
1683 AS '<replaceable>DIRECTORY</replaceable>/funcs', 'c_overpaid'
1690 <title>Returning Rows (Composite Types) from C-Language Functions</title>
1693 To return a row or composite-type value from a C-language
1694 function, you can use a special API that provides macros and
1695 functions to hide most of the complexity of building composite
1696 data types. To use this API, the source file must include:
1698 #include "funcapi.h"
1703 The support for returning composite data types (or rows) starts
1704 with the <structname>AttInMetadata</> structure. This structure
1705 holds arrays of individual attribute information needed to create
1706 a row from raw C strings. The information contained in the
1707 structure is derived from a <structname>TupleDesc</> structure,
1708 but it is stored to avoid redundant computations on each call to
1709 a set-returning function (see next section). In the case of a
1710 function returning a set, the <structname>AttInMetadata</>
1711 structure should be computed once during the first call and saved
1712 for reuse in later calls. <structname>AttInMetadata</> also
1713 saves a pointer to the original <structname>TupleDesc</>.
1715 typedef struct AttInMetadata
1717 /* full TupleDesc */
1720 /* array of attribute type input function finfo */
1721 FmgrInfo *attinfuncs;
1723 /* array of attribute type typelem */
1726 /* array of attribute typmod */
1733 To assist you in populating this structure, several functions and a macro
1736 TupleDesc RelationNameGetTupleDesc(const char *relname)
1738 to get a <structname>TupleDesc</> for a named relation, or
1740 TupleDesc TypeGetTupleDesc(Oid typeoid, List *colaliases)
1742 to get a <structname>TupleDesc</> based on a type OID. This can
1743 be used to get a <structname>TupleDesc</> for a base or
1744 composite type. Then
1746 AttInMetadata *TupleDescGetAttInMetadata(TupleDesc tupdesc)
1748 will return a pointer to an <structname>AttInMetadata</>,
1749 initialized based on the given
1750 <structname>TupleDesc</>. <structname>AttInMetadata</> can be
1751 used in conjunction with C strings to produce a properly formed
1752 row value (internally called tuple).
1756 To return a tuple you must create a tuple slot based on the
1757 <structname>TupleDesc</>. You can use
1759 TupleTableSlot *TupleDescGetSlot(TupleDesc tupdesc)
1761 to initialize this tuple slot, or obtain one through other (user provided)
1762 means. The tuple slot is needed to create a <type>Datum</> for return by the
1763 function. The same slot can (and should) be reused on each call.
1767 After constructing an <structname>AttInMetadata</> structure,
1769 HeapTuple BuildTupleFromCStrings(AttInMetadata *attinmeta, char **values)
1771 can be used to build a <structname>HeapTuple</> given user data
1772 in C string form. <literal>values</literal> is an array of C strings, one for
1773 each attribute of the return row. Each C string should be in
1774 the form expected by the input function of the attribute data
1775 type. In order to return a null value for one of the attributes,
1776 the corresponding pointer in the <parameter>values</> array
1777 should be set to <symbol>NULL</>. This function will need to
1778 be called again for each row you return.
1782 Building a tuple via <function>TupleDescGetAttInMetadata</> and
1783 <function>BuildTupleFromCStrings</> is only convenient if your
1784 function naturally computes the values to be returned as text
1785 strings. If your code naturally computes the values as a set of
1786 <type>Datum</> values, you should instead use the underlying
1787 function <function>heap_formtuple</> to convert the
1788 <type>Datum</type> values directly into a tuple. You will still need
1789 the <structname>TupleDesc</> and a <structname>TupleTableSlot</>,
1790 but not <structname>AttInMetadata</>.
1794 Once you have built a tuple to return from your function, it
1795 must be converted into a <type>Datum</>. Use
1797 TupleGetDatum(TupleTableSlot *slot, HeapTuple tuple)
1799 to get a <type>Datum</> given a tuple and a slot. This
1800 <type>Datum</> can be returned directly if you intend to return
1801 just a single row, or it can be used as the current return value
1802 in a set-returning function.
1806 An example appears in the next section.
1811 <sect2 id="xfunc-c-return-set">
1812 <title>Returning Sets from C-Language Functions</title>
1815 There is also a special API that provides support for returning
1816 sets (multiple rows) from a C-language function. A set-returning
1817 function must follow the version-1 calling conventions. Also,
1818 source files must include <filename>funcapi.h</filename>, as
1823 A set-returning function (<acronym>SRF</>) is called
1824 once for each item it returns. The <acronym>SRF</> must
1825 therefore save enough state to remember what it was doing and
1826 return the next item on each call.
1827 The structure <structname>FuncCallContext</> is provided to help
1828 control this process. Within a function, <literal>fcinfo->flinfo->fn_extra</>
1829 is used to hold a pointer to <structname>FuncCallContext</>
1835 * Number of times we've been called before
1837 * call_cntr is initialized to 0 for you by SRF_FIRSTCALL_INIT(), and
1838 * incremented for you every time SRF_RETURN_NEXT() is called.
1843 * OPTIONAL maximum number of calls
1845 * max_calls is here for convenience only and setting it is optional.
1846 * If not set, you must provide alternative means to know when the
1852 * OPTIONAL pointer to result slot
1854 * slot is for use when returning tuples (i.e., composite data types)
1855 * and is not needed when returning base data types.
1857 TupleTableSlot *slot;
1860 * OPTIONAL pointer to miscellaneous user-provided context information
1862 * user_fctx is for use as a pointer to your own data to retain
1863 * arbitrary context information between calls of your function.
1868 * OPTIONAL pointer to struct containing attribute type input metadata
1870 * attinmeta is for use when returning tuples (i.e., composite data types)
1871 * and is not needed when returning base data types. It
1872 * is only needed if you intend to use BuildTupleFromCStrings() to create
1875 AttInMetadata *attinmeta;
1878 * memory context used for structures that must live for multiple calls
1880 * multi_call_memory_ctx is set by SRF_FIRSTCALL_INIT() for you, and used
1881 * by SRF_RETURN_DONE() for cleanup. It is the most appropriate memory
1882 * context for any memory that is to be reused across multiple calls
1885 MemoryContext multi_call_memory_ctx;
1891 An <acronym>SRF</> uses several functions and macros that
1892 automatically manipulate the <structname>FuncCallContext</>
1893 structure (and expect to find it via <literal>fn_extra</>). Use
1897 to determine if your function is being called for the first or a
1898 subsequent time. On the first call (only) use
1900 SRF_FIRSTCALL_INIT()
1902 to initialize the <structname>FuncCallContext</>. On every function call,
1903 including the first, use
1907 to properly set up for using the <structname>FuncCallContext</>
1908 and clearing any previously returned data left over from the
1913 If your function has data to return, use
1915 SRF_RETURN_NEXT(funcctx, result)
1917 to return it to the caller. (<literal>result</> must be of type
1918 <type>Datum</>, either a single value or a tuple prepared as
1919 described above.) Finally, when your function is finished
1922 SRF_RETURN_DONE(funcctx)
1924 to clean up and end the <acronym>SRF</>.
1928 The memory context that is current when the <acronym>SRF</> is called is
1929 a transient context that will be cleared between calls. This means
1930 that you do not need to call <function>pfree</> on everything
1931 you allocated using <function>palloc</>; it will go away anyway. However, if you want to allocate
1932 any data structures to live across calls, you need to put them somewhere
1933 else. The memory context referenced by
1934 <structfield>multi_call_memory_ctx</> is a suitable location for any
1935 data that needs to survive until the <acronym>SRF</> is finished running. In most
1936 cases, this means that you should switch into
1937 <structfield>multi_call_memory_ctx</> while doing the first-call setup.
1941 A complete pseudo-code example looks like the following:
1944 my_set_returning_function(PG_FUNCTION_ARGS)
1946 FuncCallContext *funcctx;
1948 MemoryContext oldcontext;
1949 <replaceable>further declarations as needed</replaceable>
1951 if (SRF_IS_FIRSTCALL())
1953 funcctx = SRF_FIRSTCALL_INIT();
1954 oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
1955 /* One-time setup code appears here: */
1956 <replaceable>user code</replaceable>
1957 <replaceable>if returning composite</replaceable>
1958 <replaceable>build TupleDesc, and perhaps AttInMetadata</replaceable>
1959 <replaceable>obtain slot</replaceable>
1960 funcctx->slot = slot;
1961 <replaceable>endif returning composite</replaceable>
1962 <replaceable>user code</replaceable>
1963 MemoryContextSwitchTo(oldcontext);
1966 /* Each-time setup code appears here: */
1967 <replaceable>user code</replaceable>
1968 funcctx = SRF_PERCALL_SETUP();
1969 <replaceable>user code</replaceable>
1971 /* this is just one way we might test whether we are done: */
1972 if (funcctx->call_cntr < funcctx->max_calls)
1974 /* Here we want to return another item: */
1975 <replaceable>user code</replaceable>
1976 <replaceable>obtain result Datum</replaceable>
1977 SRF_RETURN_NEXT(funcctx, result);
1981 /* Here we are done returning items and just need to clean up: */
1982 <replaceable>user code</replaceable>
1983 SRF_RETURN_DONE(funcctx);
1990 A complete example of a simple <acronym>SRF</> returning a composite type looks like:
1992 PG_FUNCTION_INFO_V1(testpassbyval);
1995 testpassbyval(PG_FUNCTION_ARGS)
1997 FuncCallContext *funcctx;
2001 TupleTableSlot *slot;
2002 AttInMetadata *attinmeta;
2004 /* stuff done only on the first call of the function */
2005 if (SRF_IS_FIRSTCALL())
2007 MemoryContext oldcontext;
2009 /* create a function context for cross-call persistence */
2010 funcctx = SRF_FIRSTCALL_INIT();
2012 /* switch to memory context appropriate for multiple function calls */
2013 oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
2015 /* total number of tuples to be returned */
2016 funcctx->max_calls = PG_GETARG_UINT32(0);
2018 /* Build a tuple description for a __testpassbyval tuple */
2019 tupdesc = RelationNameGetTupleDesc("__testpassbyval");
2021 /* allocate a slot for a tuple with this tupdesc */
2022 slot = TupleDescGetSlot(tupdesc);
2024 /* assign slot to function context */
2025 funcctx->slot = slot;
2028 * generate attribute metadata needed later to produce tuples from raw
2031 attinmeta = TupleDescGetAttInMetadata(tupdesc);
2032 funcctx->attinmeta = attinmeta;
2034 MemoryContextSwitchTo(oldcontext);
2037 /* stuff done on every call of the function */
2038 funcctx = SRF_PERCALL_SETUP();
2040 call_cntr = funcctx->call_cntr;
2041 max_calls = funcctx->max_calls;
2042 slot = funcctx->slot;
2043 attinmeta = funcctx->attinmeta;
2045 if (call_cntr < max_calls) /* do when there is more left to send */
2052 * Prepare a values array for storage in our slot.
2053 * This should be an array of C strings which will
2054 * be processed later by the type input functions.
2056 values = (char **) palloc(3 * sizeof(char *));
2057 values[0] = (char *) palloc(16 * sizeof(char));
2058 values[1] = (char *) palloc(16 * sizeof(char));
2059 values[2] = (char *) palloc(16 * sizeof(char));
2061 snprintf(values[0], 16, "%d", 1 * PG_GETARG_INT32(1));
2062 snprintf(values[1], 16, "%d", 2 * PG_GETARG_INT32(1));
2063 snprintf(values[2], 16, "%d", 3 * PG_GETARG_INT32(1));
2066 tuple = BuildTupleFromCStrings(attinmeta, values);
2068 /* make the tuple into a datum */
2069 result = TupleGetDatum(slot, tuple);
2071 /* clean up (this is not really necessary) */
2077 SRF_RETURN_NEXT(funcctx, result);
2079 else /* do when there is no more left */
2081 SRF_RETURN_DONE(funcctx);
2086 The SQL code to declare this function is:
2088 CREATE TYPE __testpassbyval AS (f1 integer, f2 integer, f3 integer);
2090 CREATE OR REPLACE FUNCTION testpassbyval(integer, integer) RETURNS SETOF __testpassbyval
2091 AS '<replaceable>filename</>', 'testpassbyval'
2092 LANGUAGE C IMMUTABLE STRICT;
2097 The directory <filename>contrib/tablefunc</> in the source
2098 distribution contains more examples of set-returning functions.
2103 <title>Polymorphic Arguments and Return Types</title>
2106 C-language functions may be declared to accept and
2107 return the polymorphic types
2108 <type>anyelement</type> and <type>anyarray</type>.
2109 See <xref linkend="extend-types-polymorphic"> for a more detailed explanation
2110 of polymorphic functions. When function arguments or return types
2111 are defined as polymorphic types, the function author cannot know
2112 in advance what data type it will be called with, or
2113 need to return. There are two routines provided in <filename>fmgr.h</>
2114 to allow a version-1 C function to discover the actual data types
2115 of its arguments and the type it is expected to return. The routines are
2116 called <literal>get_fn_expr_rettype(FmgrInfo *flinfo)</> and
2117 <literal>get_fn_expr_argtype(FmgrInfo *flinfo, int argnum)</>.
2118 They return the result or argument type OID, or <symbol>InvalidOid</symbol> if the
2119 information is not available.
2120 The structure <literal>flinfo</> is normally accessed as
2121 <literal>fcinfo->flinfo</>. The parameter <literal>argnum</>
2126 For example, suppose we want to write a function to accept a single
2127 element of any type, and return a one-dimensional array of that type:
2130 PG_FUNCTION_INFO_V1(make_array);
2132 make_array(PG_FUNCTION_ARGS)
2135 Oid element_type = get_fn_expr_argtype(fcinfo->flinfo, 0);
2144 if (!OidIsValid(element_type))
2145 elog(ERROR, "could not determine data type of input");
2147 /* get the provided element */
2148 element = PG_GETARG_DATUM(0);
2150 /* we have one dimension */
2152 /* and one element */
2154 /* and lower bound is 1 */
2157 /* get required info about the element type */
2158 get_typlenbyvalalign(element_type, &typlen, &typbyval, &typalign);
2160 /* now build the array */
2161 result = construct_md_array(&element, ndims, dims, lbs,
2162 element_type, typlen, typbyval, typalign);
2164 PG_RETURN_ARRAYTYPE_P(result);
2170 The following command declares the function
2171 <function>make_array</function> in SQL:
2174 CREATE FUNCTION make_array(anyelement) RETURNS anyarray
2175 AS '<replaceable>DIRECTORY</replaceable>/funcs', 'make_array'
2179 Note the use of <literal>STRICT</literal>; this is essential
2180 since the code is not bothering to test for a null input.
2185 <sect1 id="xfunc-overload">
2186 <title>Function Overloading</title>
2188 <indexterm zone="xfunc-overload">
2189 <primary>overloading</primary>
2190 <secondary>functions</secondary>
2194 More than one function may be defined with the same SQL name, so long
2195 as the arguments they take are different. In other words,
2196 function names can be <firstterm>overloaded</firstterm>. When a
2197 query is executed, the server will determine which function to
2198 call from the data types and the number of the provided arguments.
2199 Overloading can also be used to simulate functions with a variable
2200 number of arguments, up to a finite maximum number.
2204 A function may also have the same name as an attribute. (Recall
2205 that <literal>attribute(table)</literal> is equivalent to
2206 <literal>table.attribute</literal>.) In the case that there is an
2207 ambiguity between a function on a complex type and an attribute of
2208 the complex type, the attribute will always be used.
2212 When creating a family of overloaded functions, one should be
2213 careful not to create ambiguities. For instance, given the
2216 CREATE FUNCTION test(int, real) RETURNS ...
2217 CREATE FUNCTION test(smallint, double precision) RETURNS ...
2219 it is not immediately clear which function would be called with
2220 some trivial input like <literal>test(1, 1.5)</literal>. The
2221 currently implemented resolution rules are described in
2222 <xref linkend="typeconv">, but it is unwise to design a system that subtly
2223 relies on this behavior.
2227 When overloading C-language functions, there is an additional
2228 constraint: The C name of each function in the family of
2229 overloaded functions must be different from the C names of all
2230 other functions, either internal or dynamically loaded. If this
2231 rule is violated, the behavior is not portable. You might get a
2232 run-time linker error, or one of the functions will get called
2233 (usually the internal one). The alternative form of the
2234 <literal>AS</> clause for the SQL <command>CREATE
2235 FUNCTION</command> command decouples the SQL function name from
2236 the function name in the C source code. E.g.,
2238 CREATE FUNCTION test(int) RETURNS int
2239 AS '<replaceable>filename</>', 'test_1arg'
2241 CREATE FUNCTION test(int, int) RETURNS int
2242 AS '<replaceable>filename</>', 'test_2arg'
2245 The names of the C functions here reflect one of many possible conventions.
2249 <!-- Keep this comment at the end of the file
2254 sgml-minimize-attributes:nil
2255 sgml-always-quote-attributes:t
2258 sgml-parent-document:nil
2259 sgml-default-dtd-file:"./reference.ced"
2260 sgml-exposed-tags:nil
2261 sgml-local-catalogs:("/usr/lib/sgml/catalog")
2262 sgml-local-ecat-files:nil