1 <!-- doc/src/sgml/xfunc.sgml -->
4 <title>User-defined Functions</title>
6 <indexterm zone="xfunc">
7 <primary>function</primary>
8 <secondary>user-defined</secondary>
12 <productname>PostgreSQL</productname> provides four kinds of
18 query language functions (functions written in
19 <acronym>SQL</acronym>) (<xref linkend="xfunc-sql">)
24 procedural language functions (functions written in, for
25 example, <application>PL/pgSQL</> or <application>PL/Tcl</>)
26 (<xref linkend="xfunc-pl">)
31 internal functions (<xref linkend="xfunc-internal">)
36 C-language functions (<xref linkend="xfunc-c">)
44 of function can take base types, composite types, or
45 combinations of these as arguments (parameters). In addition,
46 every kind of function can return a base type or
47 a composite type. Functions can also be defined to return
48 sets of base or composite values.
52 Many kinds of functions can take or return certain pseudo-types
53 (such as polymorphic types), but the available facilities vary.
54 Consult the description of each kind of function for more details.
58 It's easiest to define <acronym>SQL</acronym>
59 functions, so we'll start by discussing those.
60 Most of the concepts presented for <acronym>SQL</acronym> functions
61 will carry over to the other types of functions.
65 Throughout this chapter, it can be useful to look at the reference
66 page of the <xref linkend="sql-createfunction"> command to
67 understand the examples better. Some examples from this chapter
68 can be found in <filename>funcs.sql</filename> and
69 <filename>funcs.c</filename> in the <filename>src/tutorial</>
70 directory in the <productname>PostgreSQL</productname> source
75 <sect1 id="xfunc-sql">
76 <title>Query Language (<acronym>SQL</acronym>) Functions</title>
78 <indexterm zone="xfunc-sql">
79 <primary>function</primary>
80 <secondary>user-defined</secondary>
81 <tertiary>in SQL</tertiary>
85 SQL functions execute an arbitrary list of SQL statements, returning
86 the result of the last query in the list.
87 In the simple (non-set)
88 case, the first row of the last query's result will be returned.
89 (Bear in mind that <quote>the first row</quote> of a multirow
90 result is not well-defined unless you use <literal>ORDER BY</>.)
91 If the last query happens
92 to return no rows at all, the null value will be returned.
96 Alternatively, an SQL function can be declared to return a set,
97 by specifying the function's return type as <literal>SETOF
98 <replaceable>sometype</></literal>, or equivalently by declaring it as
99 <literal>RETURNS TABLE(<replaceable>columns</>)</literal>. In this case
100 all rows of the last query's result are returned. Further details appear
105 The body of an SQL function must be a list of SQL
106 statements separated by semicolons. A semicolon after the last
107 statement is optional. Unless the function is declared to return
108 <type>void</>, the last statement must be a <command>SELECT</>,
109 or an <command>INSERT</>, <command>UPDATE</>, or <command>DELETE</>
110 that has a <literal>RETURNING</> clause.
114 Any collection of commands in the <acronym>SQL</acronym>
115 language can be packaged together and defined as a function.
116 Besides <command>SELECT</command> queries, the commands can include data
117 modification queries (<command>INSERT</command>,
118 <command>UPDATE</command>, and <command>DELETE</command>), as well as
119 other SQL commands. (You cannot use transaction control commands, e.g.
120 <command>COMMIT</>, <command>SAVEPOINT</>, and some utility
121 commands, e.g. <literal>VACUUM</>, in <acronym>SQL</acronym> functions.)
122 However, the final command
123 must be a <command>SELECT</command> or have a <literal>RETURNING</>
124 clause that returns whatever is
125 specified as the function's return type. Alternatively, if you
126 want to define a SQL function that performs actions but has no
127 useful value to return, you can define it as returning <type>void</>.
128 For example, this function removes rows with negative salaries from
129 the <literal>emp</> table:
132 CREATE FUNCTION clean_emp() RETURNS void AS '
147 The syntax of the <command>CREATE FUNCTION</command> command requires
148 the function body to be written as a string constant. It is usually
149 most convenient to use dollar quoting (see <xref
150 linkend="sql-syntax-dollar-quoting">) for the string constant.
151 If you choose to use regular single-quoted string constant syntax,
152 you must double single quote marks (<literal>'</>) and backslashes
153 (<literal>\</>) (assuming escape string syntax) in the body of
154 the function (see <xref linkend="sql-syntax-strings">).
157 <sect2 id="xfunc-sql-function-arguments">
158 <title>Arguments for <acronym>SQL</acronym> Functions</title>
161 <primary>function</primary>
162 <secondary>named argument</secondary>
166 Arguments of a SQL function can be referenced in the function
167 body using either names or numbers. Examples of both methods appear
172 To use a name, declare the function argument as having a name, and
173 then just write that name in the function body. If the argument name
174 is the same as any column name in the current SQL command within the
175 function, the column name will take precedence. To override this,
176 qualify the argument name with the name of the function itself, that is
177 <literal><replaceable>function_name</>.<replaceable>argument_name</></literal>.
178 (If this would conflict with a qualified column name, again the column
179 name wins. You can avoid the ambiguity by choosing a different alias for
180 the table within the SQL command.)
184 In the older numeric approach, arguments are referenced using the syntax
185 <literal>$<replaceable>n</></>: <literal>$1</> refers to the first input
186 argument, <literal>$2</> to the second, and so on. This will work
187 whether or not the particular argument was declared with a name.
191 If an argument is of a composite type, then the dot notation,
192 e.g., <literal>argname.fieldname</literal> or
193 <literal>$1.fieldname</literal>, can be used to access attributes of the
194 argument. Again, you might need to qualify the argument's name with the
195 function name to make the form with an argument name unambiguous.
199 SQL function arguments can only be used as data values,
200 not as identifiers. Thus for example this is reasonable:
202 INSERT INTO mytable VALUES ($1);
204 but this will not work:
206 INSERT INTO $1 VALUES (42);
212 The ability to use names to reference SQL function arguments was added
213 in <productname>PostgreSQL</productname> 9.2. Functions to be used in
214 older servers must use the <literal>$<replaceable>n</></> notation.
219 <sect2 id="xfunc-sql-base-functions">
220 <title><acronym>SQL</acronym> Functions on Base Types</title>
223 The simplest possible <acronym>SQL</acronym> function has no arguments and
224 simply returns a base type, such as <type>integer</type>:
227 CREATE FUNCTION one() RETURNS integer AS $$
231 -- Alternative syntax for string literal:
232 CREATE FUNCTION one() RETURNS integer AS '
245 Notice that we defined a column alias within the function body for the result of the function
246 (with the name <literal>result</>), but this column alias is not visible
247 outside the function. Hence, the result is labeled <literal>one</>
248 instead of <literal>result</>.
252 It is almost as easy to define <acronym>SQL</acronym> functions
253 that take base types as arguments:
256 CREATE FUNCTION add_em(x integer, y integer) RETURNS integer AS $$
260 SELECT add_em(1, 2) AS answer;
269 Alternatively, we could dispense with names for the arguments and
273 CREATE FUNCTION add_em(integer, integer) RETURNS integer AS $$
277 SELECT add_em(1, 2) AS answer;
286 Here is a more useful function, which might be used to debit a
290 CREATE FUNCTION tf1 (accountno integer, debit numeric) RETURNS integer AS $$
292 SET balance = balance - debit
293 WHERE accountno = tf1.accountno;
298 A user could execute this function to debit account 17 by $100.00 as
302 SELECT tf1(17, 100.0);
307 In this example, we chose the name <literal>accountno</> for the first
308 argument, but this is the same as the name of a column in the
309 <literal>bank</> table. Within the <command>UPDATE</> command,
310 <literal>accountno</> refers to the column <literal>bank.accountno</>,
311 so <literal>tf1.accountno</> must be used to refer to the argument.
312 We could of course avoid this by using a different name for the argument.
316 In practice one would probably like a more useful result from the
317 function than a constant 1, so a more likely definition
321 CREATE FUNCTION tf1 (accountno integer, debit numeric) RETURNS integer AS $$
323 SET balance = balance - debit
324 WHERE accountno = tf1.accountno;
325 SELECT balance FROM bank WHERE accountno = tf1.accountno;
329 which adjusts the balance and returns the new balance.
330 The same thing could be done in one command using <literal>RETURNING</>:
333 CREATE FUNCTION tf1 (accountno integer, debit numeric) RETURNS integer AS $$
335 SET balance = balance - debit
336 WHERE accountno = tf1.accountno
343 <sect2 id="xfunc-sql-composite-functions">
344 <title><acronym>SQL</acronym> Functions on Composite Types</title>
347 When writing functions with arguments of composite types, we must not
348 only specify which argument we want but also the desired attribute
349 (field) of that argument. For example, suppose that
350 <type>emp</type> is a table containing employee data, and therefore
351 also the name of the composite type of each row of the table. Here
352 is a function <function>double_salary</function> that computes what someone's
353 salary would be if it were doubled:
363 INSERT INTO emp VALUES ('Bill', 4200, 45, '(2,1)');
365 CREATE FUNCTION double_salary(emp) RETURNS numeric AS $$
366 SELECT $1.salary * 2 AS salary;
369 SELECT name, double_salary(emp.*) AS dream
371 WHERE emp.cubicle ~= point '(2,1)';
380 Notice the use of the syntax <literal>$1.salary</literal>
381 to select one field of the argument row value. Also notice
382 how the calling <command>SELECT</> command uses <literal>*</>
384 the entire current row of a table as a composite value. The table
385 row can alternatively be referenced using just the table name,
388 SELECT name, double_salary(emp) AS dream
390 WHERE emp.cubicle ~= point '(2,1)';
392 but this usage is deprecated since it's easy to get confused.
396 Sometimes it is handy to construct a composite argument value
397 on-the-fly. This can be done with the <literal>ROW</> construct.
398 For example, we could adjust the data being passed to the function:
400 SELECT name, double_salary(ROW(name, salary*1.1, age, cubicle)) AS dream
406 It is also possible to build a function that returns a composite type.
407 This is an example of a function
408 that returns a single <type>emp</type> row:
411 CREATE FUNCTION new_emp() RETURNS emp AS $$
412 SELECT text 'None' AS name,
415 point '(2,2)' AS cubicle;
419 In this example we have specified each of the attributes
420 with a constant value, but any computation
421 could have been substituted for these constants.
425 Note two important things about defining the function:
430 The select list order in the query must be exactly the same as
431 that in which the columns appear in the table associated
432 with the composite type. (Naming the columns, as we did above,
433 is irrelevant to the system.)
438 You must typecast the expressions to match the
439 definition of the composite type, or you will get errors like this:
442 ERROR: function declared to return emp returns varchar instead of text at column 1
451 A different way to define the same function is:
454 CREATE FUNCTION new_emp() RETURNS emp AS $$
455 SELECT ROW('None', 1000.0, 25, '(2,2)')::emp;
459 Here we wrote a <command>SELECT</> that returns just a single
460 column of the correct composite type. This isn't really better
461 in this situation, but it is a handy alternative in some cases
462 — for example, if we need to compute the result by calling
463 another function that returns the desired composite value.
467 We could call this function directly in either of two ways:
473 --------------------------
474 (None,1000.0,25,"(2,2)")
476 SELECT * FROM new_emp();
478 name | salary | age | cubicle
479 ------+--------+-----+---------
480 None | 1000.0 | 25 | (2,2)
483 The second way is described more fully in <xref
484 linkend="xfunc-sql-table-functions">.
488 When you use a function that returns a composite type,
489 you might want only one field (attribute) from its result.
490 You can do that with syntax like this:
493 SELECT (new_emp()).name;
500 The extra parentheses are needed to keep the parser from getting
501 confused. If you try to do it without them, you get something like this:
504 SELECT new_emp().name;
505 ERROR: syntax error at or near "."
506 LINE 1: SELECT new_emp().name;
512 Another option is to use
513 functional notation for extracting an attribute. The simple way
514 to explain this is that we can use the
515 notations <literal>attribute(table)</> and <literal>table.attribute</>
519 SELECT name(new_emp());
527 -- This is the same as:
528 -- SELECT emp.name AS youngster FROM emp WHERE emp.age < 30;
530 SELECT name(emp) AS youngster FROM emp WHERE age(emp) < 30;
541 The equivalence between functional notation and attribute notation
542 makes it possible to use functions on composite types to emulate
543 <quote>computed fields</>.
545 <primary>computed field</primary>
548 <primary>field</primary>
549 <secondary>computed</secondary>
551 For example, using the previous definition
552 for <literal>double_salary(emp)</>, we can write
555 SELECT emp.name, emp.double_salary FROM emp;
558 An application using this wouldn't need to be directly aware that
559 <literal>double_salary</> isn't a real column of the table.
560 (You can also emulate computed fields with views.)
564 Because of this behavior, it's unwise to give a function that takes
565 a single composite-type argument the same name as any of the fields of
571 Another way to use a function returning a composite type is to pass the
572 result to another function that accepts the correct row type as input:
575 CREATE FUNCTION getname(emp) RETURNS text AS $$
579 SELECT getname(new_emp());
588 Still another way to use a function that returns a composite type is to
589 call it as a table function, as described in <xref
590 linkend="xfunc-sql-table-functions">.
594 <sect2 id="xfunc-output-parameters">
595 <title><acronym>SQL</> Functions with Output Parameters</title>
598 <primary>function</primary>
599 <secondary>output parameter</secondary>
603 An alternative way of describing a function's results is to define it
604 with <firstterm>output parameters</>, as in this example:
607 CREATE FUNCTION add_em (IN x int, IN y int, OUT sum int)
618 This is not essentially different from the version of <literal>add_em</>
619 shown in <xref linkend="xfunc-sql-base-functions">. The real value of
620 output parameters is that they provide a convenient way of defining
621 functions that return several columns. For example,
624 CREATE FUNCTION sum_n_product (x int, y int, OUT sum int, OUT product int)
625 AS 'SELECT x + y, x * y'
628 SELECT * FROM sum_n_product(11,42);
635 What has essentially happened here is that we have created an anonymous
636 composite type for the result of the function. The above example has
637 the same end result as
640 CREATE TYPE sum_prod AS (sum int, product int);
642 CREATE FUNCTION sum_n_product (int, int) RETURNS sum_prod
643 AS 'SELECT $1 + $2, $1 * $2'
647 but not having to bother with the separate composite type definition
648 is often handy. Notice that the names attached to the output parameters
649 are not just decoration, but determine the column names of the anonymous
650 composite type. (If you omit a name for an output parameter, the
651 system will choose a name on its own.)
655 Notice that output parameters are not included in the calling argument
656 list when invoking such a function from SQL. This is because
657 <productname>PostgreSQL</productname> considers only the input
658 parameters to define the function's calling signature. That means
659 also that only the input parameters matter when referencing the function
660 for purposes such as dropping it. We could drop the above function
664 DROP FUNCTION sum_n_product (x int, y int, OUT sum int, OUT product int);
665 DROP FUNCTION sum_n_product (int, int);
670 Parameters can be marked as <literal>IN</> (the default),
671 <literal>OUT</>, <literal>INOUT</>, or <literal>VARIADIC</>.
673 parameter serves as both an input parameter (part of the calling
674 argument list) and an output parameter (part of the result record type).
675 <literal>VARIADIC</> parameters are input parameters, but are treated
676 specially as described next.
680 <sect2 id="xfunc-sql-variadic-functions">
681 <title><acronym>SQL</> Functions with Variable Numbers of Arguments</title>
684 <primary>function</primary>
685 <secondary>variadic</secondary>
689 <primary>variadic function</primary>
693 <acronym>SQL</acronym> functions can be declared to accept
694 variable numbers of arguments, so long as all the <quote>optional</>
695 arguments are of the same data type. The optional arguments will be
696 passed to the function as an array. The function is declared by
697 marking the last parameter as <literal>VARIADIC</>; this parameter
698 must be declared as being of an array type. For example:
701 CREATE FUNCTION mleast(VARIADIC arr numeric[]) RETURNS numeric AS $$
702 SELECT min($1[i]) FROM generate_subscripts($1, 1) g(i);
705 SELECT mleast(10, -1, 5, 4.4);
712 Effectively, all the actual arguments at or beyond the
713 <literal>VARIADIC</> position are gathered up into a one-dimensional
714 array, as if you had written
717 SELECT mleast(ARRAY[10, -1, 5, 4.4]); -- doesn't work
720 You can't actually write that, though — or at least, it will
721 not match this function definition. A parameter marked
722 <literal>VARIADIC</> matches one or more occurrences of its element
723 type, not of its own type.
727 Sometimes it is useful to be able to pass an already-constructed array
728 to a variadic function; this is particularly handy when one variadic
729 function wants to pass on its array parameter to another one. You can
730 do that by specifying <literal>VARIADIC</> in the call:
733 SELECT mleast(VARIADIC ARRAY[10, -1, 5, 4.4]);
736 This prevents expansion of the function's variadic parameter into its
737 element type, thereby allowing the array argument value to match
738 normally. <literal>VARIADIC</> can only be attached to the last
739 actual argument of a function call.
743 The array element parameters generated from a variadic parameter are
744 treated as not having any names of their own. This means it is not
745 possible to call a variadic function using named arguments (<xref
746 linkend="sql-syntax-calling-funcs">), except when you specify
747 <literal>VARIADIC</>. For example, this will work:
750 SELECT mleast(VARIADIC arr := ARRAY[10, -1, 5, 4.4]);
756 SELECT mleast(arr := 10);
757 SELECT mleast(arr := ARRAY[10, -1, 5, 4.4]);
762 <sect2 id="xfunc-sql-parameter-defaults">
763 <title><acronym>SQL</> Functions with Default Values for Arguments</title>
766 <primary>function</primary>
767 <secondary>default values for arguments</secondary>
771 Functions can be declared with default values for some or all input
772 arguments. The default values are inserted whenever the function is
773 called with insufficiently many actual arguments. Since arguments
774 can only be omitted from the end of the actual argument list, all
775 parameters after a parameter with a default value have to have
776 default values as well. (Although the use of named argument notation
777 could allow this restriction to be relaxed, it's still enforced so that
778 positional argument notation works sensibly.)
784 CREATE FUNCTION foo(a int, b int DEFAULT 2, c int DEFAULT 3)
791 SELECT foo(10, 20, 30);
809 SELECT foo(); -- fails since there is no default for the first argument
810 ERROR: function foo() does not exist
812 The <literal>=</literal> sign can also be used in place of the
813 key word <literal>DEFAULT</literal>.
817 <sect2 id="xfunc-sql-table-functions">
818 <title><acronym>SQL</acronym> Functions as Table Sources</title>
821 All SQL functions can be used in the <literal>FROM</> clause of a query,
822 but it is particularly useful for functions returning composite types.
823 If the function is defined to return a base type, the table function
824 produces a one-column table. If the function is defined to return
825 a composite type, the table function produces a column for each attribute
826 of the composite type.
833 CREATE TABLE foo (fooid int, foosubid int, fooname text);
834 INSERT INTO foo VALUES (1, 1, 'Joe');
835 INSERT INTO foo VALUES (1, 2, 'Ed');
836 INSERT INTO foo VALUES (2, 1, 'Mary');
838 CREATE FUNCTION getfoo(int) RETURNS foo AS $$
839 SELECT * FROM foo WHERE fooid = $1;
842 SELECT *, upper(fooname) FROM getfoo(1) AS t1;
844 fooid | foosubid | fooname | upper
845 -------+----------+---------+-------
850 As the example shows, we can work with the columns of the function's
851 result just the same as if they were columns of a regular table.
855 Note that we only got one row out of the function. This is because
856 we did not use <literal>SETOF</>. That is described in the next section.
860 <sect2 id="xfunc-sql-functions-returning-set">
861 <title><acronym>SQL</acronym> Functions Returning Sets</title>
864 <primary>function</primary>
865 <secondary>with SETOF</secondary>
869 When an SQL function is declared as returning <literal>SETOF
870 <replaceable>sometype</></literal>, the function's final
871 query is executed to completion, and each row it
872 outputs is returned as an element of the result set.
876 This feature is normally used when calling the function in the <literal>FROM</>
877 clause. In this case each row returned by the function becomes
878 a row of the table seen by the query. For example, assume that
879 table <literal>foo</> has the same contents as above, and we say:
882 CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
883 SELECT * FROM foo WHERE fooid = $1;
886 SELECT * FROM getfoo(1) AS t1;
891 fooid | foosubid | fooname
892 -------+----------+---------
900 It is also possible to return multiple rows with the columns defined by
901 output parameters, like this:
904 CREATE TABLE tab (y int, z int);
905 INSERT INTO tab VALUES (1, 2), (3, 4), (5, 6), (7, 8);
907 CREATE FUNCTION sum_n_product_with_tab (x int, OUT sum int, OUT product int)
910 SELECT $1 + tab.y, $1 * tab.y FROM tab;
913 SELECT * FROM sum_n_product_with_tab(10);
923 The key point here is that you must write <literal>RETURNS SETOF record</>
924 to indicate that the function returns multiple rows instead of just one.
925 If there is only one output parameter, write that parameter's type
926 instead of <type>record</>.
930 Currently, functions returning sets can also be called in the select list
931 of a query. For each row that the query
932 generates by itself, the function returning set is invoked, and an output
933 row is generated for each element of the function's result set. Note,
934 however, that this capability is deprecated and might be removed in future
935 releases. The following is an example function returning a set from the
939 CREATE FUNCTION listchildren(text) RETURNS SETOF text AS $$
940 SELECT name FROM nodes WHERE parent = $1
954 SELECT listchildren('Top');
962 SELECT name, listchildren(name) FROM nodes;
964 --------+--------------
973 In the last <command>SELECT</command>,
974 notice that no output row appears for <literal>Child2</>, <literal>Child3</>, etc.
975 This happens because <function>listchildren</function> returns an empty set
976 for those arguments, so no result rows are generated.
981 If a function's last command is <command>INSERT</>, <command>UPDATE</>,
982 or <command>DELETE</> with <literal>RETURNING</>, that command will
983 always be executed to completion, even if the function is not declared
984 with <literal>SETOF</> or the calling query does not fetch all the
985 result rows. Any extra rows produced by the <literal>RETURNING</>
986 clause are silently dropped, but the commanded table modifications
987 still happen (and are all completed before returning from the function).
992 <sect2 id="xfunc-sql-functions-returning-table">
993 <title><acronym>SQL</acronym> Functions Returning <literal>TABLE</></title>
996 <primary>function</primary>
997 <secondary>RETURNS TABLE</secondary>
1001 There is another way to declare a function as returning a set,
1002 which is to use the syntax
1003 <literal>RETURNS TABLE(<replaceable>columns</>)</literal>.
1004 This is equivalent to using one or more <literal>OUT</> parameters plus
1005 marking the function as returning <literal>SETOF record</> (or
1006 <literal>SETOF</> a single output parameter's type, as appropriate).
1007 This notation is specified in recent versions of the SQL standard, and
1008 thus may be more portable than using <literal>SETOF</>.
1012 For example, the preceding sum-and-product example could also be
1016 CREATE FUNCTION sum_n_product_with_tab (x int)
1017 RETURNS TABLE(sum int, product int) AS $$
1018 SELECT $1 + tab.y, $1 * tab.y FROM tab;
1022 It is not allowed to use explicit <literal>OUT</> or <literal>INOUT</>
1023 parameters with the <literal>RETURNS TABLE</> notation — you must
1024 put all the output columns in the <literal>TABLE</> list.
1029 <title>Polymorphic <acronym>SQL</acronym> Functions</title>
1032 <acronym>SQL</acronym> functions can be declared to accept and
1033 return the polymorphic types <type>anyelement</type>,
1034 <type>anyarray</type>, <type>anynonarray</type>,
1035 <type>anyenum</type>, and <type>anyrange</type>. See <xref
1036 linkend="extend-types-polymorphic"> for a more detailed
1037 explanation of polymorphic functions. Here is a polymorphic
1038 function <function>make_array</function> that builds up an array
1039 from two arbitrary data type elements:
1041 CREATE FUNCTION make_array(anyelement, anyelement) RETURNS anyarray AS $$
1042 SELECT ARRAY[$1, $2];
1045 SELECT make_array(1, 2) AS intarray, make_array('a'::text, 'b') AS textarray;
1046 intarray | textarray
1047 ----------+-----------
1054 Notice the use of the typecast <literal>'a'::text</literal>
1055 to specify that the argument is of type <type>text</type>. This is
1056 required if the argument is just a string literal, since otherwise
1057 it would be treated as type
1058 <type>unknown</type>, and array of <type>unknown</type> is not a valid
1060 Without the typecast, you will get errors like this:
1063 ERROR: could not determine polymorphic type because input has type "unknown"
1069 It is permitted to have polymorphic arguments with a fixed
1070 return type, but the converse is not. For example:
1072 CREATE FUNCTION is_greater(anyelement, anyelement) RETURNS boolean AS $$
1076 SELECT is_greater(1, 2);
1082 CREATE FUNCTION invalid_func() RETURNS anyelement AS $$
1085 ERROR: cannot determine result data type
1086 DETAIL: A function returning a polymorphic type must have at least one polymorphic argument.
1091 Polymorphism can be used with functions that have output arguments.
1094 CREATE FUNCTION dup (f1 anyelement, OUT f2 anyelement, OUT f3 anyarray)
1095 AS 'select $1, array[$1,$1]' LANGUAGE SQL;
1097 SELECT * FROM dup(22);
1106 Polymorphism can also be used with variadic functions.
1109 CREATE FUNCTION anyleast (VARIADIC anyarray) RETURNS anyelement AS $$
1110 SELECT min($1[i]) FROM generate_subscripts($1, 1) g(i);
1113 SELECT anyleast(10, -1, 5, 4);
1119 SELECT anyleast('abc'::text, 'def');
1125 CREATE FUNCTION concat_values(text, VARIADIC anyarray) RETURNS text AS $$
1126 SELECT array_to_string($2, $1);
1129 SELECT concat_values('|', 1, 4, 2);
1139 <title><acronym>SQL</acronym> Functions with Collations</title>
1142 <primary>collation</>
1143 <secondary>in SQL functions</>
1147 When a SQL function has one or more parameters of collatable data types,
1148 a collation is identified for each function call depending on the
1149 collations assigned to the actual arguments, as described in <xref
1150 linkend="collation">. If a collation is successfully identified
1151 (i.e., there are no conflicts of implicit collations among the arguments)
1152 then all the collatable parameters are treated as having that collation
1153 implicitly. This will affect the behavior of collation-sensitive
1154 operations within the function. For example, using the
1155 <function>anyleast</> function described above, the result of
1157 SELECT anyleast('abc'::text, 'ABC');
1159 will depend on the database's default collation. In <literal>C</> locale
1160 the result will be <literal>ABC</>, but in many other locales it will
1161 be <literal>abc</>. The collation to use can be forced by adding
1162 a <literal>COLLATE</> clause to any of the arguments, for example
1164 SELECT anyleast('abc'::text, 'ABC' COLLATE "C");
1166 Alternatively, if you wish a function to operate with a particular
1167 collation regardless of what it is called with, insert
1168 <literal>COLLATE</> clauses as needed in the function definition.
1169 This version of <function>anyleast</> would always use <literal>en_US</>
1170 locale to compare strings:
1172 CREATE FUNCTION anyleast (VARIADIC anyarray) RETURNS anyelement AS $$
1173 SELECT min($1[i] COLLATE "en_US") FROM generate_subscripts($1, 1) g(i);
1176 But note that this will throw an error if applied to a non-collatable
1181 If no common collation can be identified among the actual arguments,
1182 then a SQL function treats its parameters as having their data types'
1183 default collation (which is usually the database's default collation,
1184 but could be different for parameters of domain types).
1188 The behavior of collatable parameters can be thought of as a limited
1189 form of polymorphism, applicable only to textual data types.
1194 <sect1 id="xfunc-overload">
1195 <title>Function Overloading</title>
1197 <indexterm zone="xfunc-overload">
1198 <primary>overloading</primary>
1199 <secondary>functions</secondary>
1203 More than one function can be defined with the same SQL name, so long
1204 as the arguments they take are different. In other words,
1205 function names can be <firstterm>overloaded</firstterm>. When a
1206 query is executed, the server will determine which function to
1207 call from the data types and the number of the provided arguments.
1208 Overloading can also be used to simulate functions with a variable
1209 number of arguments, up to a finite maximum number.
1213 When creating a family of overloaded functions, one should be
1214 careful not to create ambiguities. For instance, given the
1217 CREATE FUNCTION test(int, real) RETURNS ...
1218 CREATE FUNCTION test(smallint, double precision) RETURNS ...
1220 it is not immediately clear which function would be called with
1221 some trivial input like <literal>test(1, 1.5)</literal>. The
1222 currently implemented resolution rules are described in
1223 <xref linkend="typeconv">, but it is unwise to design a system that subtly
1224 relies on this behavior.
1228 A function that takes a single argument of a composite type should
1229 generally not have the same name as any attribute (field) of that type.
1230 Recall that <literal>attribute(table)</literal> is considered equivalent
1231 to <literal>table.attribute</literal>. In the case that there is an
1232 ambiguity between a function on a composite type and an attribute of
1233 the composite type, the attribute will always be used. It is possible
1234 to override that choice by schema-qualifying the function name
1235 (that is, <literal>schema.func(table)</literal>) but it's better to
1236 avoid the problem by not choosing conflicting names.
1240 Another possible conflict is between variadic and non-variadic functions.
1241 For instance, it is possible to create both <literal>foo(numeric)</> and
1242 <literal>foo(VARIADIC numeric[])</>. In this case it is unclear which one
1243 should be matched to a call providing a single numeric argument, such as
1244 <literal>foo(10.1)</>. The rule is that the function appearing
1245 earlier in the search path is used, or if the two functions are in the
1246 same schema, the non-variadic one is preferred.
1250 When overloading C-language functions, there is an additional
1251 constraint: The C name of each function in the family of
1252 overloaded functions must be different from the C names of all
1253 other functions, either internal or dynamically loaded. If this
1254 rule is violated, the behavior is not portable. You might get a
1255 run-time linker error, or one of the functions will get called
1256 (usually the internal one). The alternative form of the
1257 <literal>AS</> clause for the SQL <command>CREATE
1258 FUNCTION</command> command decouples the SQL function name from
1259 the function name in the C source code. For instance:
1261 CREATE FUNCTION test(int) RETURNS int
1262 AS '<replaceable>filename</>', 'test_1arg'
1264 CREATE FUNCTION test(int, int) RETURNS int
1265 AS '<replaceable>filename</>', 'test_2arg'
1268 The names of the C functions here reflect one of many possible conventions.
1272 <sect1 id="xfunc-volatility">
1273 <title>Function Volatility Categories</title>
1275 <indexterm zone="xfunc-volatility">
1276 <primary>volatility</primary>
1277 <secondary>functions</secondary>
1279 <indexterm zone="xfunc-volatility">
1280 <primary>VOLATILE</primary>
1282 <indexterm zone="xfunc-volatility">
1283 <primary>STABLE</primary>
1285 <indexterm zone="xfunc-volatility">
1286 <primary>IMMUTABLE</primary>
1290 Every function has a <firstterm>volatility</> classification, with
1291 the possibilities being <literal>VOLATILE</>, <literal>STABLE</>, or
1292 <literal>IMMUTABLE</>. <literal>VOLATILE</> is the default if the
1293 <xref linkend="sql-createfunction">
1294 command does not specify a category. The volatility category is a
1295 promise to the optimizer about the behavior of the function:
1300 A <literal>VOLATILE</> function can do anything, including modifying
1301 the database. It can return different results on successive calls with
1302 the same arguments. The optimizer makes no assumptions about the
1303 behavior of such functions. A query using a volatile function will
1304 re-evaluate the function at every row where its value is needed.
1309 A <literal>STABLE</> function cannot modify the database and is
1310 guaranteed to return the same results given the same arguments
1311 for all rows within a single statement. This category allows the
1312 optimizer to optimize multiple calls of the function to a single
1313 call. In particular, it is safe to use an expression containing
1314 such a function in an index scan condition. (Since an index scan
1315 will evaluate the comparison value only once, not once at each
1316 row, it is not valid to use a <literal>VOLATILE</> function in an
1317 index scan condition.)
1322 An <literal>IMMUTABLE</> function cannot modify the database and is
1323 guaranteed to return the same results given the same arguments forever.
1324 This category allows the optimizer to pre-evaluate the function when
1325 a query calls it with constant arguments. For example, a query like
1326 <literal>SELECT ... WHERE x = 2 + 2</> can be simplified on sight to
1327 <literal>SELECT ... WHERE x = 4</>, because the function underlying
1328 the integer addition operator is marked <literal>IMMUTABLE</>.
1335 For best optimization results, you should label your functions with the
1336 strictest volatility category that is valid for them.
1340 Any function with side-effects <emphasis>must</> be labeled
1341 <literal>VOLATILE</>, so that calls to it cannot be optimized away.
1342 Even a function with no side-effects needs to be labeled
1343 <literal>VOLATILE</> if its value can change within a single query;
1344 some examples are <literal>random()</>, <literal>currval()</>,
1345 <literal>timeofday()</>.
1349 Another important example is that the <function>current_timestamp</>
1350 family of functions qualify as <literal>STABLE</>, since their values do
1351 not change within a transaction.
1355 There is relatively little difference between <literal>STABLE</> and
1356 <literal>IMMUTABLE</> categories when considering simple interactive
1357 queries that are planned and immediately executed: it doesn't matter
1358 a lot whether a function is executed once during planning or once during
1359 query execution startup. But there is a big difference if the plan is
1360 saved and reused later. Labeling a function <literal>IMMUTABLE</> when
1361 it really isn't might allow it to be prematurely folded to a constant during
1362 planning, resulting in a stale value being re-used during subsequent uses
1363 of the plan. This is a hazard when using prepared statements or when
1364 using function languages that cache plans (such as
1365 <application>PL/pgSQL</>).
1369 For functions written in SQL or in any of the standard procedural
1370 languages, there is a second important property determined by the
1371 volatility category, namely the visibility of any data changes that have
1372 been made by the SQL command that is calling the function. A
1373 <literal>VOLATILE</> function will see such changes, a <literal>STABLE</>
1374 or <literal>IMMUTABLE</> function will not. This behavior is implemented
1375 using the snapshotting behavior of MVCC (see <xref linkend="mvcc">):
1376 <literal>STABLE</> and <literal>IMMUTABLE</> functions use a snapshot
1377 established as of the start of the calling query, whereas
1378 <literal>VOLATILE</> functions obtain a fresh snapshot at the start of
1379 each query they execute.
1384 Functions written in C can manage snapshots however they want, but it's
1385 usually a good idea to make C functions work this way too.
1390 Because of this snapshotting behavior,
1391 a function containing only <command>SELECT</> commands can safely be
1392 marked <literal>STABLE</>, even if it selects from tables that might be
1393 undergoing modifications by concurrent queries.
1394 <productname>PostgreSQL</productname> will execute all commands of a
1395 <literal>STABLE</> function using the snapshot established for the
1396 calling query, and so it will see a fixed view of the database throughout
1401 The same snapshotting behavior is used for <command>SELECT</> commands
1402 within <literal>IMMUTABLE</> functions. It is generally unwise to select
1403 from database tables within an <literal>IMMUTABLE</> function at all,
1404 since the immutability will be broken if the table contents ever change.
1405 However, <productname>PostgreSQL</productname> does not enforce that you
1410 A common error is to label a function <literal>IMMUTABLE</> when its
1411 results depend on a configuration parameter. For example, a function
1412 that manipulates timestamps might well have results that depend on the
1413 <xref linkend="guc-timezone"> setting. For safety, such functions should
1414 be labeled <literal>STABLE</> instead.
1419 Before <productname>PostgreSQL</productname> release 8.0, the requirement
1420 that <literal>STABLE</> and <literal>IMMUTABLE</> functions cannot modify
1421 the database was not enforced by the system. Releases 8.0 and later enforce it
1422 by requiring SQL functions and procedural language functions of these
1423 categories to contain no SQL commands other than <command>SELECT</>.
1424 (This is not a completely bulletproof test, since such functions could
1425 still call <literal>VOLATILE</> functions that modify the database.
1426 If you do that, you will find that the <literal>STABLE</> or
1427 <literal>IMMUTABLE</> function does not notice the database changes
1428 applied by the called function, since they are hidden from its snapshot.)
1433 <sect1 id="xfunc-pl">
1434 <title>Procedural Language Functions</title>
1437 <productname>PostgreSQL</productname> allows user-defined functions
1438 to be written in other languages besides SQL and C. These other
1439 languages are generically called <firstterm>procedural
1440 languages</firstterm> (<acronym>PL</>s).
1441 Procedural languages aren't built into the
1442 <productname>PostgreSQL</productname> server; they are offered
1443 by loadable modules.
1444 See <xref linkend="xplang"> and following chapters for more
1449 <sect1 id="xfunc-internal">
1450 <title>Internal Functions</title>
1452 <indexterm zone="xfunc-internal"><primary>function</><secondary>internal</></>
1455 Internal functions are functions written in C that have been statically
1456 linked into the <productname>PostgreSQL</productname> server.
1457 The <quote>body</quote> of the function definition
1458 specifies the C-language name of the function, which need not be the
1459 same as the name being declared for SQL use.
1460 (For reasons of backward compatibility, an empty body
1461 is accepted as meaning that the C-language function name is the
1462 same as the SQL name.)
1466 Normally, all internal functions present in the
1467 server are declared during the initialization of the database cluster
1468 (see <xref linkend="creating-cluster">),
1469 but a user could use <command>CREATE FUNCTION</command>
1470 to create additional alias names for an internal function.
1471 Internal functions are declared in <command>CREATE FUNCTION</command>
1472 with language name <literal>internal</literal>. For instance, to
1473 create an alias for the <function>sqrt</function> function:
1475 CREATE FUNCTION square_root(double precision) RETURNS double precision
1480 (Most internal functions expect to be declared <quote>strict</quote>.)
1485 Not all <quote>predefined</quote> functions are
1486 <quote>internal</quote> in the above sense. Some predefined
1487 functions are written in SQL.
1492 <sect1 id="xfunc-c">
1493 <title>C-Language Functions</title>
1495 <indexterm zone="xfunc-c">
1496 <primary>function</primary>
1497 <secondary>user-defined</secondary>
1498 <tertiary>in C</tertiary>
1502 User-defined functions can be written in C (or a language that can
1503 be made compatible with C, such as C++). Such functions are
1504 compiled into dynamically loadable objects (also called shared
1505 libraries) and are loaded by the server on demand. The dynamic
1506 loading feature is what distinguishes <quote>C language</> functions
1507 from <quote>internal</> functions — the actual coding conventions
1508 are essentially the same for both. (Hence, the standard internal
1509 function library is a rich source of coding examples for user-defined
1514 Two different calling conventions are currently used for C functions.
1515 The newer <quote>version 1</quote> calling convention is indicated by writing
1516 a <literal>PG_FUNCTION_INFO_V1()</literal> macro call for the function,
1517 as illustrated below. Lack of such a macro indicates an old-style
1518 (<quote>version 0</quote>) function. The language name specified in <command>CREATE FUNCTION</command>
1519 is <literal>C</literal> in either case. Old-style functions are now deprecated
1520 because of portability problems and lack of functionality, but they
1521 are still supported for compatibility reasons.
1524 <sect2 id="xfunc-c-dynload">
1525 <title>Dynamic Loading</title>
1527 <indexterm zone="xfunc-c-dynload">
1528 <primary>dynamic loading</primary>
1532 The first time a user-defined function in a particular
1533 loadable object file is called in a session,
1534 the dynamic loader loads that object file into memory so that the
1535 function can be called. The <command>CREATE FUNCTION</command>
1536 for a user-defined C function must therefore specify two pieces of
1537 information for the function: the name of the loadable
1538 object file, and the C name (link symbol) of the specific function to call
1539 within that object file. If the C name is not explicitly specified then
1540 it is assumed to be the same as the SQL function name.
1544 The following algorithm is used to locate the shared object file
1545 based on the name given in the <command>CREATE FUNCTION</command>
1551 If the name is an absolute path, the given file is loaded.
1557 If the name starts with the string <literal>$libdir</literal>,
1558 that part is replaced by the <productname>PostgreSQL</> package
1560 name, which is determined at build time.<indexterm><primary>$libdir</></>
1566 If the name does not contain a directory part, the file is
1567 searched for in the path specified by the configuration variable
1568 <xref linkend="guc-dynamic-library-path">.<indexterm><primary>dynamic_library_path</></>
1574 Otherwise (the file was not found in the path, or it contains a
1575 non-absolute directory part), the dynamic loader will try to
1576 take the name as given, which will most likely fail. (It is
1577 unreliable to depend on the current working directory.)
1582 If this sequence does not work, the platform-specific shared
1583 library file name extension (often <filename>.so</filename>) is
1584 appended to the given name and this sequence is tried again. If
1585 that fails as well, the load will fail.
1589 It is recommended to locate shared libraries either relative to
1590 <literal>$libdir</literal> or through the dynamic library path.
1591 This simplifies version upgrades if the new installation is at a
1592 different location. The actual directory that
1593 <literal>$libdir</literal> stands for can be found out with the
1594 command <literal>pg_config --pkglibdir</literal>.
1598 The user ID the <productname>PostgreSQL</productname> server runs
1599 as must be able to traverse the path to the file you intend to
1600 load. Making the file or a higher-level directory not readable
1601 and/or not executable by the <systemitem>postgres</systemitem>
1602 user is a common mistake.
1606 In any case, the file name that is given in the
1607 <command>CREATE FUNCTION</command> command is recorded literally
1608 in the system catalogs, so if the file needs to be loaded again
1609 the same procedure is applied.
1614 <productname>PostgreSQL</productname> will not compile a C function
1615 automatically. The object file must be compiled before it is referenced
1616 in a <command>CREATE
1617 FUNCTION</> command. See <xref linkend="dfunc"> for additional
1622 <indexterm zone="xfunc-c-dynload">
1623 <primary>magic block</primary>
1627 To ensure that a dynamically loaded object file is not loaded into an
1628 incompatible server, <productname>PostgreSQL</productname> checks that the
1629 file contains a <quote>magic block</> with the appropriate contents.
1630 This allows the server to detect obvious incompatibilities, such as code
1631 compiled for a different major version of
1632 <productname>PostgreSQL</productname>. A magic block is required as of
1633 <productname>PostgreSQL</productname> 8.2. To include a magic block,
1634 write this in one (and only one) of the module source files, after having
1635 included the header <filename>fmgr.h</>:
1638 #ifdef PG_MODULE_MAGIC
1643 The <literal>#ifdef</> test can be omitted if the code doesn't
1644 need to compile against pre-8.2 <productname>PostgreSQL</productname>
1649 After it is used for the first time, a dynamically loaded object
1650 file is retained in memory. Future calls in the same session to
1651 the function(s) in that file will only incur the small overhead of
1652 a symbol table lookup. If you need to force a reload of an object
1653 file, for example after recompiling it, begin a fresh session.
1656 <indexterm zone="xfunc-c-dynload">
1657 <primary>_PG_init</primary>
1659 <indexterm zone="xfunc-c-dynload">
1660 <primary>_PG_fini</primary>
1662 <indexterm zone="xfunc-c-dynload">
1663 <primary>library initialization function</primary>
1665 <indexterm zone="xfunc-c-dynload">
1666 <primary>library finalization function</primary>
1670 Optionally, a dynamically loaded file can contain initialization and
1671 finalization functions. If the file includes a function named
1672 <function>_PG_init</>, that function will be called immediately after
1673 loading the file. The function receives no parameters and should
1674 return void. If the file includes a function named
1675 <function>_PG_fini</>, that function will be called immediately before
1676 unloading the file. Likewise, the function receives no parameters and
1677 should return void. Note that <function>_PG_fini</> will only be called
1678 during an unload of the file, not during process termination.
1679 (Presently, unloads are disabled and will never occur, but this may
1680 change in the future.)
1685 <sect2 id="xfunc-c-basetype">
1686 <title>Base Types in C-Language Functions</title>
1688 <indexterm zone="xfunc-c-basetype">
1689 <primary>data type</primary>
1690 <secondary>internal organization</secondary>
1694 To know how to write C-language functions, you need to know how
1695 <productname>PostgreSQL</productname> internally represents base
1696 data types and how they can be passed to and from functions.
1697 Internally, <productname>PostgreSQL</productname> regards a base
1698 type as a <quote>blob of memory</quote>. The user-defined
1699 functions that you define over a type in turn define the way that
1700 <productname>PostgreSQL</productname> can operate on it. That
1701 is, <productname>PostgreSQL</productname> will only store and
1702 retrieve the data from disk and use your user-defined functions
1703 to input, process, and output the data.
1707 Base types can have one of three internal formats:
1712 pass by value, fixed-length
1717 pass by reference, fixed-length
1722 pass by reference, variable-length
1729 By-value types can only be 1, 2, or 4 bytes in length
1730 (also 8 bytes, if <literal>sizeof(Datum)</literal> is 8 on your machine).
1731 You should be careful to define your types such that they will be the
1732 same size (in bytes) on all architectures. For example, the
1733 <literal>long</literal> type is dangerous because it is 4 bytes on some
1734 machines and 8 bytes on others, whereas <type>int</type> type is 4 bytes
1735 on most Unix machines. A reasonable implementation of the
1736 <type>int4</type> type on Unix machines might be:
1739 /* 4-byte integer, passed by value */
1745 On the other hand, fixed-length types of any size can
1746 be passed by-reference. For example, here is a sample
1747 implementation of a <productname>PostgreSQL</productname> type:
1750 /* 16-byte structure, passed by reference */
1757 Only pointers to such types can be used when passing
1758 them in and out of <productname>PostgreSQL</productname> functions.
1759 To return a value of such a type, allocate the right amount of
1760 memory with <literal>palloc</literal>, fill in the allocated memory,
1761 and return a pointer to it. (Also, if you just want to return the
1762 same value as one of your input arguments that's of the same data type,
1763 you can skip the extra <literal>palloc</literal> and just return the
1764 pointer to the input value.)
1768 Finally, all variable-length types must also be passed
1769 by reference. All variable-length types must begin
1770 with an opaque length field of exactly 4 bytes, which will be set
1771 by SET_VARSIZE; never set this field directly! All data to
1772 be stored within that type must be located in the memory
1773 immediately following that length field. The
1774 length field contains the total length of the structure,
1775 that is, it includes the size of the length field
1780 Another important point is to avoid leaving any uninitialized bits
1781 within data type values; for example, take care to zero out any
1782 alignment padding bytes that might be present in structs. Without
1783 this, logically-equivalent constants of your data type might be
1784 seen as unequal by the planner, leading to inefficient (though not
1790 <emphasis>Never</> modify the contents of a pass-by-reference input
1791 value. If you do so you are likely to corrupt on-disk data, since
1792 the pointer you are given might point directly into a disk buffer.
1793 The sole exception to this rule is explained in
1794 <xref linkend="xaggr">.
1799 As an example, we can define the type <type>text</type> as
1809 Obviously, the data field declared here is not long enough to hold
1810 all possible strings. Since it's impossible to declare a variable-size
1811 structure in <acronym>C</acronym>, we rely on the knowledge that the
1812 <acronym>C</acronym> compiler won't range-check array subscripts. We
1813 just allocate the necessary amount of space and then access the array as
1814 if it were declared the right length. (This is a common trick, which
1815 you can read about in many textbooks about C.)
1820 variable-length types, we must be careful to allocate
1821 the correct amount of memory and set the length field correctly.
1822 For example, if we wanted to store 40 bytes in a <structname>text</>
1823 structure, we might use a code fragment like this:
1825 <programlisting><![CDATA[
1826 #include "postgres.h"
1828 char buffer[40]; /* our source data */
1830 text *destination = (text *) palloc(VARHDRSZ + 40);
1831 SET_VARSIZE(destination, VARHDRSZ + 40);
1832 memcpy(destination->data, buffer, 40);
1837 <literal>VARHDRSZ</> is the same as <literal>sizeof(int32)</>, but
1838 it's considered good style to use the macro <literal>VARHDRSZ</>
1839 to refer to the size of the overhead for a variable-length type.
1840 Also, the length field <emphasis>must</> be set using the
1841 <literal>SET_VARSIZE</> macro, not by simple assignment.
1845 <xref linkend="xfunc-c-type-table"> specifies which C type
1846 corresponds to which SQL type when writing a C-language function
1847 that uses a built-in type of <productname>PostgreSQL</>.
1848 The <quote>Defined In</quote> column gives the header file that
1849 needs to be included to get the type definition. (The actual
1850 definition might be in a different file that is included by the
1851 listed file. It is recommended that users stick to the defined
1852 interface.) Note that you should always include
1853 <filename>postgres.h</filename> first in any source file, because
1854 it declares a number of things that you will need anyway.
1857 <table tocentry="1" id="xfunc-c-type-table">
1858 <title>Equivalent C Types for Built-in SQL Types</title>
1875 <entry><type>abstime</type></entry>
1876 <entry><type>AbsoluteTime</type></entry>
1877 <entry><filename>utils/nabstime.h</filename></entry>
1880 <entry><type>boolean</type></entry>
1881 <entry><type>bool</type></entry>
1882 <entry><filename>postgres.h</filename> (maybe compiler built-in)</entry>
1885 <entry><type>box</type></entry>
1886 <entry><type>BOX*</type></entry>
1887 <entry><filename>utils/geo_decls.h</filename></entry>
1890 <entry><type>bytea</type></entry>
1891 <entry><type>bytea*</type></entry>
1892 <entry><filename>postgres.h</filename></entry>
1895 <entry><type>"char"</type></entry>
1896 <entry><type>char</type></entry>
1897 <entry>(compiler built-in)</entry>
1900 <entry><type>character</type></entry>
1901 <entry><type>BpChar*</type></entry>
1902 <entry><filename>postgres.h</filename></entry>
1905 <entry><type>cid</type></entry>
1906 <entry><type>CommandId</type></entry>
1907 <entry><filename>postgres.h</filename></entry>
1910 <entry><type>date</type></entry>
1911 <entry><type>DateADT</type></entry>
1912 <entry><filename>utils/date.h</filename></entry>
1915 <entry><type>smallint</type> (<type>int2</type>)</entry>
1916 <entry><type>int2</type> or <type>int16</type></entry>
1917 <entry><filename>postgres.h</filename></entry>
1920 <entry><type>int2vector</type></entry>
1921 <entry><type>int2vector*</type></entry>
1922 <entry><filename>postgres.h</filename></entry>
1925 <entry><type>integer</type> (<type>int4</type>)</entry>
1926 <entry><type>int4</type> or <type>int32</type></entry>
1927 <entry><filename>postgres.h</filename></entry>
1930 <entry><type>real</type> (<type>float4</type>)</entry>
1931 <entry><type>float4*</type></entry>
1932 <entry><filename>postgres.h</filename></entry>
1935 <entry><type>double precision</type> (<type>float8</type>)</entry>
1936 <entry><type>float8*</type></entry>
1937 <entry><filename>postgres.h</filename></entry>
1940 <entry><type>interval</type></entry>
1941 <entry><type>Interval*</type></entry>
1942 <entry><filename>datatype/timestamp.h</filename></entry>
1945 <entry><type>lseg</type></entry>
1946 <entry><type>LSEG*</type></entry>
1947 <entry><filename>utils/geo_decls.h</filename></entry>
1950 <entry><type>name</type></entry>
1951 <entry><type>Name</type></entry>
1952 <entry><filename>postgres.h</filename></entry>
1955 <entry><type>oid</type></entry>
1956 <entry><type>Oid</type></entry>
1957 <entry><filename>postgres.h</filename></entry>
1960 <entry><type>oidvector</type></entry>
1961 <entry><type>oidvector*</type></entry>
1962 <entry><filename>postgres.h</filename></entry>
1965 <entry><type>path</type></entry>
1966 <entry><type>PATH*</type></entry>
1967 <entry><filename>utils/geo_decls.h</filename></entry>
1970 <entry><type>point</type></entry>
1971 <entry><type>POINT*</type></entry>
1972 <entry><filename>utils/geo_decls.h</filename></entry>
1975 <entry><type>regproc</type></entry>
1976 <entry><type>regproc</type></entry>
1977 <entry><filename>postgres.h</filename></entry>
1980 <entry><type>reltime</type></entry>
1981 <entry><type>RelativeTime</type></entry>
1982 <entry><filename>utils/nabstime.h</filename></entry>
1985 <entry><type>text</type></entry>
1986 <entry><type>text*</type></entry>
1987 <entry><filename>postgres.h</filename></entry>
1990 <entry><type>tid</type></entry>
1991 <entry><type>ItemPointer</type></entry>
1992 <entry><filename>storage/itemptr.h</filename></entry>
1995 <entry><type>time</type></entry>
1996 <entry><type>TimeADT</type></entry>
1997 <entry><filename>utils/date.h</filename></entry>
2000 <entry><type>time with time zone</type></entry>
2001 <entry><type>TimeTzADT</type></entry>
2002 <entry><filename>utils/date.h</filename></entry>
2005 <entry><type>timestamp</type></entry>
2006 <entry><type>Timestamp*</type></entry>
2007 <entry><filename>datatype/timestamp.h</filename></entry>
2010 <entry><type>tinterval</type></entry>
2011 <entry><type>TimeInterval</type></entry>
2012 <entry><filename>utils/nabstime.h</filename></entry>
2015 <entry><type>varchar</type></entry>
2016 <entry><type>VarChar*</type></entry>
2017 <entry><filename>postgres.h</filename></entry>
2020 <entry><type>xid</type></entry>
2021 <entry><type>TransactionId</type></entry>
2022 <entry><filename>postgres.h</filename></entry>
2029 Now that we've gone over all of the possible structures
2030 for base types, we can show some examples of real functions.
2035 <title>Version 0 Calling Conventions</title>
2038 We present the <quote>old style</quote> calling convention first — although
2039 this approach is now deprecated, it's easier to get a handle on
2040 initially. In the version-0 method, the arguments and result
2041 of the C function are just declared in normal C style, but being
2042 careful to use the C representation of each SQL data type as shown
2047 Here are some examples:
2049 <programlisting><![CDATA[
2050 #include "postgres.h"
2052 #include "utils/geo_decls.h"
2054 #ifdef PG_MODULE_MAGIC
2066 /* by reference, fixed length */
2069 add_one_float8(float8 *arg)
2071 float8 *result = (float8 *) palloc(sizeof(float8));
2073 *result = *arg + 1.0;
2079 makepoint(Point *pointx, Point *pointy)
2081 Point *new_point = (Point *) palloc(sizeof(Point));
2083 new_point->x = pointx->x;
2084 new_point->y = pointy->y;
2089 /* by reference, variable length */
2095 * VARSIZE is the total size of the struct in bytes.
2097 text *new_t = (text *) palloc(VARSIZE(t));
2098 SET_VARSIZE(new_t, VARSIZE(t));
2100 * VARDATA is a pointer to the data region of the struct.
2102 memcpy((void *) VARDATA(new_t), /* destination */
2103 (void *) VARDATA(t), /* source */
2104 VARSIZE(t) - VARHDRSZ); /* how many bytes */
2109 concat_text(text *arg1, text *arg2)
2111 int32 new_text_size = VARSIZE(arg1) + VARSIZE(arg2) - VARHDRSZ;
2112 text *new_text = (text *) palloc(new_text_size);
2114 SET_VARSIZE(new_text, new_text_size);
2115 memcpy(VARDATA(new_text), VARDATA(arg1), VARSIZE(arg1) - VARHDRSZ);
2116 memcpy(VARDATA(new_text) + (VARSIZE(arg1) - VARHDRSZ),
2117 VARDATA(arg2), VARSIZE(arg2) - VARHDRSZ);
2125 Supposing that the above code has been prepared in file
2126 <filename>funcs.c</filename> and compiled into a shared object,
2127 we could define the functions to <productname>PostgreSQL</productname>
2128 with commands like this:
2131 CREATE FUNCTION add_one(integer) RETURNS integer
2132 AS '<replaceable>DIRECTORY</replaceable>/funcs', 'add_one'
2135 -- note overloading of SQL function name "add_one"
2136 CREATE FUNCTION add_one(double precision) RETURNS double precision
2137 AS '<replaceable>DIRECTORY</replaceable>/funcs', 'add_one_float8'
2140 CREATE FUNCTION makepoint(point, point) RETURNS point
2141 AS '<replaceable>DIRECTORY</replaceable>/funcs', 'makepoint'
2144 CREATE FUNCTION copytext(text) RETURNS text
2145 AS '<replaceable>DIRECTORY</replaceable>/funcs', 'copytext'
2148 CREATE FUNCTION concat_text(text, text) RETURNS text
2149 AS '<replaceable>DIRECTORY</replaceable>/funcs', 'concat_text'
2155 Here, <replaceable>DIRECTORY</replaceable> stands for the
2156 directory of the shared library file (for instance the
2157 <productname>PostgreSQL</productname> tutorial directory, which
2158 contains the code for the examples used in this section).
2159 (Better style would be to use just <literal>'funcs'</> in the
2160 <literal>AS</> clause, after having added
2161 <replaceable>DIRECTORY</replaceable> to the search path. In any
2162 case, we can omit the system-specific extension for a shared
2163 library, commonly <literal>.so</literal> or
2164 <literal>.sl</literal>.)
2168 Notice that we have specified the functions as <quote>strict</quote>,
2170 the system should automatically assume a null result if any input
2171 value is null. By doing this, we avoid having to check for null inputs
2172 in the function code. Without this, we'd have to check for null values
2173 explicitly, by checking for a null pointer for each
2174 pass-by-reference argument. (For pass-by-value arguments, we don't
2175 even have a way to check!)
2179 Although this calling convention is simple to use,
2180 it is not very portable; on some architectures there are problems
2181 with passing data types that are smaller than <type>int</type> this way. Also, there is
2182 no simple way to return a null result, nor to cope with null arguments
2183 in any way other than making the function strict. The version-1
2184 convention, presented next, overcomes these objections.
2189 <title>Version 1 Calling Conventions</title>
2192 The version-1 calling convention relies on macros to suppress most
2193 of the complexity of passing arguments and results. The C declaration
2194 of a version-1 function is always:
2196 Datum funcname(PG_FUNCTION_ARGS)
2198 In addition, the macro call:
2200 PG_FUNCTION_INFO_V1(funcname);
2202 must appear in the same source file. (Conventionally, it's
2203 written just before the function itself.) This macro call is not
2204 needed for <literal>internal</>-language functions, since
2205 <productname>PostgreSQL</> assumes that all internal functions
2206 use the version-1 convention. It is, however, required for
2207 dynamically-loaded functions.
2211 In a version-1 function, each actual argument is fetched using a
2212 <function>PG_GETARG_<replaceable>xxx</replaceable>()</function>
2213 macro that corresponds to the argument's data type, and the
2214 result is returned using a
2215 <function>PG_RETURN_<replaceable>xxx</replaceable>()</function>
2216 macro for the return type.
2217 <function>PG_GETARG_<replaceable>xxx</replaceable>()</function>
2218 takes as its argument the number of the function argument to
2219 fetch, where the count starts at 0.
2220 <function>PG_RETURN_<replaceable>xxx</replaceable>()</function>
2221 takes as its argument the actual value to return.
2225 Here we show the same functions as above, coded in version-1 style:
2227 <programlisting><![CDATA[
2228 #include "postgres.h"
2231 #include "utils/geo_decls.h"
2233 #ifdef PG_MODULE_MAGIC
2239 PG_FUNCTION_INFO_V1(add_one);
2242 add_one(PG_FUNCTION_ARGS)
2244 int32 arg = PG_GETARG_INT32(0);
2246 PG_RETURN_INT32(arg + 1);
2249 /* by reference, fixed length */
2251 PG_FUNCTION_INFO_V1(add_one_float8);
2254 add_one_float8(PG_FUNCTION_ARGS)
2256 /* The macros for FLOAT8 hide its pass-by-reference nature. */
2257 float8 arg = PG_GETARG_FLOAT8(0);
2259 PG_RETURN_FLOAT8(arg + 1.0);
2262 PG_FUNCTION_INFO_V1(makepoint);
2265 makepoint(PG_FUNCTION_ARGS)
2267 /* Here, the pass-by-reference nature of Point is not hidden. */
2268 Point *pointx = PG_GETARG_POINT_P(0);
2269 Point *pointy = PG_GETARG_POINT_P(1);
2270 Point *new_point = (Point *) palloc(sizeof(Point));
2272 new_point->x = pointx->x;
2273 new_point->y = pointy->y;
2275 PG_RETURN_POINT_P(new_point);
2278 /* by reference, variable length */
2280 PG_FUNCTION_INFO_V1(copytext);
2283 copytext(PG_FUNCTION_ARGS)
2285 text *t = PG_GETARG_TEXT_P(0);
2287 * VARSIZE is the total size of the struct in bytes.
2289 text *new_t = (text *) palloc(VARSIZE(t));
2290 SET_VARSIZE(new_t, VARSIZE(t));
2292 * VARDATA is a pointer to the data region of the struct.
2294 memcpy((void *) VARDATA(new_t), /* destination */
2295 (void *) VARDATA(t), /* source */
2296 VARSIZE(t) - VARHDRSZ); /* how many bytes */
2297 PG_RETURN_TEXT_P(new_t);
2300 PG_FUNCTION_INFO_V1(concat_text);
2303 concat_text(PG_FUNCTION_ARGS)
2305 text *arg1 = PG_GETARG_TEXT_P(0);
2306 text *arg2 = PG_GETARG_TEXT_P(1);
2307 int32 new_text_size = VARSIZE(arg1) + VARSIZE(arg2) - VARHDRSZ;
2308 text *new_text = (text *) palloc(new_text_size);
2310 SET_VARSIZE(new_text, new_text_size);
2311 memcpy(VARDATA(new_text), VARDATA(arg1), VARSIZE(arg1) - VARHDRSZ);
2312 memcpy(VARDATA(new_text) + (VARSIZE(arg1) - VARHDRSZ),
2313 VARDATA(arg2), VARSIZE(arg2) - VARHDRSZ);
2314 PG_RETURN_TEXT_P(new_text);
2321 The <command>CREATE FUNCTION</command> commands are the same as
2322 for the version-0 equivalents.
2326 At first glance, the version-1 coding conventions might appear to
2327 be just pointless obscurantism. They do, however, offer a number
2328 of improvements, because the macros can hide unnecessary detail.
2329 An example is that in coding <function>add_one_float8</>, we no longer need to
2330 be aware that <type>float8</type> is a pass-by-reference type. Another
2331 example is that the <literal>GETARG</> macros for variable-length types allow
2332 for more efficient fetching of <quote>toasted</quote> (compressed or
2333 out-of-line) values.
2337 One big improvement in version-1 functions is better handling of null
2338 inputs and results. The macro <function>PG_ARGISNULL(<replaceable>n</>)</function>
2339 allows a function to test whether each input is null. (Of course, doing
2340 this is only necessary in functions not declared <quote>strict</>.)
2342 <function>PG_GETARG_<replaceable>xxx</replaceable>()</function> macros,
2343 the input arguments are counted beginning at zero. Note that one
2344 should refrain from executing
2345 <function>PG_GETARG_<replaceable>xxx</replaceable>()</function> until
2346 one has verified that the argument isn't null.
2347 To return a null result, execute <function>PG_RETURN_NULL()</function>;
2348 this works in both strict and nonstrict functions.
2352 Other options provided in the new-style interface are two
2354 <function>PG_GETARG_<replaceable>xxx</replaceable>()</function>
2355 macros. The first of these,
2356 <function>PG_GETARG_<replaceable>xxx</replaceable>_COPY()</function>,
2357 guarantees to return a copy of the specified argument that is
2358 safe for writing into. (The normal macros will sometimes return a
2359 pointer to a value that is physically stored in a table, which
2360 must not be written to. Using the
2361 <function>PG_GETARG_<replaceable>xxx</replaceable>_COPY()</function>
2362 macros guarantees a writable result.)
2363 The second variant consists of the
2364 <function>PG_GETARG_<replaceable>xxx</replaceable>_SLICE()</function>
2365 macros which take three arguments. The first is the number of the
2366 function argument (as above). The second and third are the offset and
2367 length of the segment to be returned. Offsets are counted from
2368 zero, and a negative length requests that the remainder of the
2369 value be returned. These macros provide more efficient access to
2370 parts of large values in the case where they have storage type
2371 <quote>external</quote>. (The storage type of a column can be specified using
2372 <literal>ALTER TABLE <replaceable>tablename</replaceable> ALTER
2373 COLUMN <replaceable>colname</replaceable> SET STORAGE
2374 <replaceable>storagetype</replaceable></literal>. <replaceable>storagetype</replaceable> is one of
2375 <literal>plain</>, <literal>external</>, <literal>extended</literal>,
2376 or <literal>main</>.)
2380 Finally, the version-1 function call conventions make it possible
2381 to return set results (<xref linkend="xfunc-c-return-set">) and
2382 implement trigger functions (<xref linkend="triggers">) and
2383 procedural-language call handlers (<xref
2384 linkend="plhandler">). Version-1 code is also more
2385 portable than version-0, because it does not break restrictions
2386 on function call protocol in the C standard. For more details
2387 see <filename>src/backend/utils/fmgr/README</filename> in the
2388 source distribution.
2393 <title>Writing Code</title>
2396 Before we turn to the more advanced topics, we should discuss
2397 some coding rules for <productname>PostgreSQL</productname>
2398 C-language functions. While it might be possible to load functions
2399 written in languages other than C into
2400 <productname>PostgreSQL</productname>, this is usually difficult
2401 (when it is possible at all) because other languages, such as
2402 C++, FORTRAN, or Pascal often do not follow the same calling
2403 convention as C. That is, other languages do not pass argument
2404 and return values between functions in the same way. For this
2405 reason, we will assume that your C-language functions are
2406 actually written in C.
2410 The basic rules for writing and building C functions are as follows:
2415 Use <literal>pg_config
2416 --includedir-server</literal><indexterm><primary>pg_config</><secondary>with user-defined C functions</></>
2417 to find out where the <productname>PostgreSQL</> server header
2418 files are installed on your system (or the system that your
2419 users will be running on).
2425 Compiling and linking your code so that it can be dynamically
2426 loaded into <productname>PostgreSQL</productname> always
2427 requires special flags. See <xref linkend="dfunc"> for a
2428 detailed explanation of how to do it for your particular
2435 Remember to define a <quote>magic block</> for your shared library,
2436 as described in <xref linkend="xfunc-c-dynload">.
2442 When allocating memory, use the
2443 <productname>PostgreSQL</productname> functions
2444 <function>palloc</function><indexterm><primary>palloc</></> and <function>pfree</function><indexterm><primary>pfree</></>
2445 instead of the corresponding C library functions
2446 <function>malloc</function> and <function>free</function>.
2447 The memory allocated by <function>palloc</function> will be
2448 freed automatically at the end of each transaction, preventing
2455 Always zero the bytes of your structures using <function>memset</>
2456 (or allocate them with <function>palloc0</> in the first place).
2457 Even if you assign to each field of your structure, there might be
2458 alignment padding (holes in the structure) that contain
2459 garbage values. Without this, it's difficult to
2460 support hash indexes or hash joins, as you must pick out only
2461 the significant bits of your data structure to compute a hash.
2462 The planner also sometimes relies on comparing constants via
2463 bitwise equality, so you can get undesirable planning results if
2464 logically-equivalent values aren't bitwise equal.
2470 Most of the internal <productname>PostgreSQL</productname>
2471 types are declared in <filename>postgres.h</filename>, while
2472 the function manager interfaces
2473 (<symbol>PG_FUNCTION_ARGS</symbol>, etc.) are in
2474 <filename>fmgr.h</filename>, so you will need to include at
2475 least these two files. For portability reasons it's best to
2476 include <filename>postgres.h</filename> <emphasis>first</>,
2477 before any other system or user header files. Including
2478 <filename>postgres.h</filename> will also include
2479 <filename>elog.h</filename> and <filename>palloc.h</filename>
2486 Symbol names defined within object files must not conflict
2487 with each other or with symbols defined in the
2488 <productname>PostgreSQL</productname> server executable. You
2489 will have to rename your functions or variables if you get
2490 error messages to this effect.
2500 <title>Composite-type Arguments</title>
2503 Composite types do not have a fixed layout like C structures.
2504 Instances of a composite type can contain null fields. In
2505 addition, composite types that are part of an inheritance
2506 hierarchy can have different fields than other members of the
2507 same inheritance hierarchy. Therefore,
2508 <productname>PostgreSQL</productname> provides a function
2509 interface for accessing fields of composite types from C.
2513 Suppose we want to write a function to answer the query:
2516 SELECT name, c_overpaid(emp, 1500) AS overpaid
2518 WHERE name = 'Bill' OR name = 'Sam';
2521 Using call conventions version 0, we can define
2522 <function>c_overpaid</> as:
2524 <programlisting><![CDATA[
2525 #include "postgres.h"
2526 #include "executor/executor.h" /* for GetAttributeByName() */
2528 #ifdef PG_MODULE_MAGIC
2533 c_overpaid(HeapTupleHeader t, /* the current row of emp */
2539 salary = DatumGetInt32(GetAttributeByName(t, "salary", &isnull));
2542 return salary > limit;
2547 In version-1 coding, the above would look like this:
2549 <programlisting><![CDATA[
2550 #include "postgres.h"
2551 #include "executor/executor.h" /* for GetAttributeByName() */
2553 #ifdef PG_MODULE_MAGIC
2557 PG_FUNCTION_INFO_V1(c_overpaid);
2560 c_overpaid(PG_FUNCTION_ARGS)
2562 HeapTupleHeader t = PG_GETARG_HEAPTUPLEHEADER(0);
2563 int32 limit = PG_GETARG_INT32(1);
2567 salary = GetAttributeByName(t, "salary", &isnull);
2569 PG_RETURN_BOOL(false);
2570 /* Alternatively, we might prefer to do PG_RETURN_NULL() for null salary. */
2572 PG_RETURN_BOOL(DatumGetInt32(salary) > limit);
2579 <function>GetAttributeByName</function> is the
2580 <productname>PostgreSQL</productname> system function that
2581 returns attributes out of the specified row. It has
2582 three arguments: the argument of type <type>HeapTupleHeader</type> passed
2584 the function, the name of the desired attribute, and a
2585 return parameter that tells whether the attribute
2586 is null. <function>GetAttributeByName</function> returns a <type>Datum</type>
2587 value that you can convert to the proper data type by using the
2588 appropriate <function>DatumGet<replaceable>XXX</replaceable>()</function>
2589 macro. Note that the return value is meaningless if the null flag is
2590 set; always check the null flag before trying to do anything with the
2595 There is also <function>GetAttributeByNum</function>, which selects
2596 the target attribute by column number instead of name.
2600 The following command declares the function
2601 <function>c_overpaid</function> in SQL:
2604 CREATE FUNCTION c_overpaid(emp, integer) RETURNS boolean
2605 AS '<replaceable>DIRECTORY</replaceable>/funcs', 'c_overpaid'
2609 Notice we have used <literal>STRICT</> so that we did not have to
2610 check whether the input arguments were NULL.
2615 <title>Returning Rows (Composite Types)</title>
2618 To return a row or composite-type value from a C-language
2619 function, you can use a special API that provides macros and
2620 functions to hide most of the complexity of building composite
2621 data types. To use this API, the source file must include:
2623 #include "funcapi.h"
2628 There are two ways you can build a composite data value (henceforth
2629 a <quote>tuple</>): you can build it from an array of Datum values,
2630 or from an array of C strings that can be passed to the input
2631 conversion functions of the tuple's column data types. In either
2632 case, you first need to obtain or construct a <structname>TupleDesc</>
2633 descriptor for the tuple structure. When working with Datums, you
2634 pass the <structname>TupleDesc</> to <function>BlessTupleDesc</>,
2635 and then call <function>heap_form_tuple</> for each row. When working
2636 with C strings, you pass the <structname>TupleDesc</> to
2637 <function>TupleDescGetAttInMetadata</>, and then call
2638 <function>BuildTupleFromCStrings</> for each row. In the case of a
2639 function returning a set of tuples, the setup steps can all be done
2640 once during the first call of the function.
2644 Several helper functions are available for setting up the needed
2645 <structname>TupleDesc</>. The recommended way to do this in most
2646 functions returning composite values is to call:
2648 TypeFuncClass get_call_result_type(FunctionCallInfo fcinfo,
2650 TupleDesc *resultTupleDesc)
2652 passing the same <literal>fcinfo</> struct passed to the calling function
2653 itself. (This of course requires that you use the version-1
2654 calling conventions.) <varname>resultTypeId</> can be specified
2655 as <literal>NULL</> or as the address of a local variable to receive the
2656 function's result type OID. <varname>resultTupleDesc</> should be the
2657 address of a local <structname>TupleDesc</> variable. Check that the
2658 result is <literal>TYPEFUNC_COMPOSITE</>; if so,
2659 <varname>resultTupleDesc</> has been filled with the needed
2660 <structname>TupleDesc</>. (If it is not, you can report an error along
2661 the lines of <quote>function returning record called in context that
2662 cannot accept type record</quote>.)
2667 <function>get_call_result_type</> can resolve the actual type of a
2668 polymorphic function result; so it is useful in functions that return
2669 scalar polymorphic results, not only functions that return composites.
2670 The <varname>resultTypeId</> output is primarily useful for functions
2671 returning polymorphic scalars.
2677 <function>get_call_result_type</> has a sibling
2678 <function>get_expr_result_type</>, which can be used to resolve the
2679 expected output type for a function call represented by an expression
2680 tree. This can be used when trying to determine the result type from
2681 outside the function itself. There is also
2682 <function>get_func_result_type</>, which can be used when only the
2683 function's OID is available. However these functions are not able
2684 to deal with functions declared to return <structname>record</>, and
2685 <function>get_func_result_type</> cannot resolve polymorphic types,
2686 so you should preferentially use <function>get_call_result_type</>.
2691 Older, now-deprecated functions for obtaining
2692 <structname>TupleDesc</>s are:
2694 TupleDesc RelationNameGetTupleDesc(const char *relname)
2696 to get a <structname>TupleDesc</> for the row type of a named relation,
2699 TupleDesc TypeGetTupleDesc(Oid typeoid, List *colaliases)
2701 to get a <structname>TupleDesc</> based on a type OID. This can
2702 be used to get a <structname>TupleDesc</> for a base or
2703 composite type. It will not work for a function that returns
2704 <structname>record</>, however, and it cannot resolve polymorphic
2709 Once you have a <structname>TupleDesc</>, call:
2711 TupleDesc BlessTupleDesc(TupleDesc tupdesc)
2713 if you plan to work with Datums, or:
2715 AttInMetadata *TupleDescGetAttInMetadata(TupleDesc tupdesc)
2717 if you plan to work with C strings. If you are writing a function
2718 returning set, you can save the results of these functions in the
2719 <structname>FuncCallContext</> structure — use the
2720 <structfield>tuple_desc</> or <structfield>attinmeta</> field
2725 When working with Datums, use:
2727 HeapTuple heap_form_tuple(TupleDesc tupdesc, Datum *values, bool *isnull)
2729 to build a <structname>HeapTuple</> given user data in Datum form.
2733 When working with C strings, use:
2735 HeapTuple BuildTupleFromCStrings(AttInMetadata *attinmeta, char **values)
2737 to build a <structname>HeapTuple</> given user data
2738 in C string form. <literal>values</literal> is an array of C strings,
2739 one for each attribute of the return row. Each C string should be in
2740 the form expected by the input function of the attribute data
2741 type. In order to return a null value for one of the attributes,
2742 the corresponding pointer in the <parameter>values</> array
2743 should be set to <symbol>NULL</>. This function will need to
2744 be called again for each row you return.
2748 Once you have built a tuple to return from your function, it
2749 must be converted into a <type>Datum</>. Use:
2751 HeapTupleGetDatum(HeapTuple tuple)
2753 to convert a <structname>HeapTuple</> into a valid Datum. This
2754 <type>Datum</> can be returned directly if you intend to return
2755 just a single row, or it can be used as the current return value
2756 in a set-returning function.
2760 An example appears in the next section.
2765 <sect2 id="xfunc-c-return-set">
2766 <title>Returning Sets</title>
2769 There is also a special API that provides support for returning
2770 sets (multiple rows) from a C-language function. A set-returning
2771 function must follow the version-1 calling conventions. Also,
2772 source files must include <filename>funcapi.h</filename>, as
2777 A set-returning function (<acronym>SRF</>) is called
2778 once for each item it returns. The <acronym>SRF</> must
2779 therefore save enough state to remember what it was doing and
2780 return the next item on each call.
2781 The structure <structname>FuncCallContext</> is provided to help
2782 control this process. Within a function, <literal>fcinfo->flinfo->fn_extra</>
2783 is used to hold a pointer to <structname>FuncCallContext</>
2789 * Number of times we've been called before
2791 * call_cntr is initialized to 0 for you by SRF_FIRSTCALL_INIT(), and
2792 * incremented for you every time SRF_RETURN_NEXT() is called.
2797 * OPTIONAL maximum number of calls
2799 * max_calls is here for convenience only and setting it is optional.
2800 * If not set, you must provide alternative means to know when the
2806 * OPTIONAL pointer to result slot
2808 * This is obsolete and only present for backward compatibility, viz,
2809 * user-defined SRFs that use the deprecated TupleDescGetSlot().
2811 TupleTableSlot *slot;
2814 * OPTIONAL pointer to miscellaneous user-provided context information
2816 * user_fctx is for use as a pointer to your own data to retain
2817 * arbitrary context information between calls of your function.
2822 * OPTIONAL pointer to struct containing attribute type input metadata
2824 * attinmeta is for use when returning tuples (i.e., composite data types)
2825 * and is not used when returning base data types. It is only needed
2826 * if you intend to use BuildTupleFromCStrings() to create the return
2829 AttInMetadata *attinmeta;
2832 * memory context used for structures that must live for multiple calls
2834 * multi_call_memory_ctx is set by SRF_FIRSTCALL_INIT() for you, and used
2835 * by SRF_RETURN_DONE() for cleanup. It is the most appropriate memory
2836 * context for any memory that is to be reused across multiple calls
2839 MemoryContext multi_call_memory_ctx;
2842 * OPTIONAL pointer to struct containing tuple description
2844 * tuple_desc is for use when returning tuples (i.e., composite data types)
2845 * and is only needed if you are going to build the tuples with
2846 * heap_form_tuple() rather than with BuildTupleFromCStrings(). Note that
2847 * the TupleDesc pointer stored here should usually have been run through
2848 * BlessTupleDesc() first.
2850 TupleDesc tuple_desc;
2857 An <acronym>SRF</> uses several functions and macros that
2858 automatically manipulate the <structname>FuncCallContext</>
2859 structure (and expect to find it via <literal>fn_extra</>). Use:
2863 to determine if your function is being called for the first or a
2864 subsequent time. On the first call (only) use:
2866 SRF_FIRSTCALL_INIT()
2868 to initialize the <structname>FuncCallContext</>. On every function call,
2869 including the first, use:
2873 to properly set up for using the <structname>FuncCallContext</>
2874 and clearing any previously returned data left over from the
2879 If your function has data to return, use:
2881 SRF_RETURN_NEXT(funcctx, result)
2883 to return it to the caller. (<literal>result</> must be of type
2884 <type>Datum</>, either a single value or a tuple prepared as
2885 described above.) Finally, when your function is finished
2886 returning data, use:
2888 SRF_RETURN_DONE(funcctx)
2890 to clean up and end the <acronym>SRF</>.
2894 The memory context that is current when the <acronym>SRF</> is called is
2895 a transient context that will be cleared between calls. This means
2896 that you do not need to call <function>pfree</> on everything
2897 you allocated using <function>palloc</>; it will go away anyway. However, if you want to allocate
2898 any data structures to live across calls, you need to put them somewhere
2899 else. The memory context referenced by
2900 <structfield>multi_call_memory_ctx</> is a suitable location for any
2901 data that needs to survive until the <acronym>SRF</> is finished running. In most
2902 cases, this means that you should switch into
2903 <structfield>multi_call_memory_ctx</> while doing the first-call setup.
2907 A complete pseudo-code example looks like the following:
2910 my_set_returning_function(PG_FUNCTION_ARGS)
2912 FuncCallContext *funcctx;
2914 <replaceable>further declarations as needed</replaceable>
2916 if (SRF_IS_FIRSTCALL())
2918 MemoryContext oldcontext;
2920 funcctx = SRF_FIRSTCALL_INIT();
2921 oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
2922 /* One-time setup code appears here: */
2923 <replaceable>user code</replaceable>
2924 <replaceable>if returning composite</replaceable>
2925 <replaceable>build TupleDesc, and perhaps AttInMetadata</replaceable>
2926 <replaceable>endif returning composite</replaceable>
2927 <replaceable>user code</replaceable>
2928 MemoryContextSwitchTo(oldcontext);
2931 /* Each-time setup code appears here: */
2932 <replaceable>user code</replaceable>
2933 funcctx = SRF_PERCALL_SETUP();
2934 <replaceable>user code</replaceable>
2936 /* this is just one way we might test whether we are done: */
2937 if (funcctx->call_cntr < funcctx->max_calls)
2939 /* Here we want to return another item: */
2940 <replaceable>user code</replaceable>
2941 <replaceable>obtain result Datum</replaceable>
2942 SRF_RETURN_NEXT(funcctx, result);
2946 /* Here we are done returning items and just need to clean up: */
2947 <replaceable>user code</replaceable>
2948 SRF_RETURN_DONE(funcctx);
2955 A complete example of a simple <acronym>SRF</> returning a composite type
2957 <programlisting><![CDATA[
2958 PG_FUNCTION_INFO_V1(retcomposite);
2961 retcomposite(PG_FUNCTION_ARGS)
2963 FuncCallContext *funcctx;
2967 AttInMetadata *attinmeta;
2969 /* stuff done only on the first call of the function */
2970 if (SRF_IS_FIRSTCALL())
2972 MemoryContext oldcontext;
2974 /* create a function context for cross-call persistence */
2975 funcctx = SRF_FIRSTCALL_INIT();
2977 /* switch to memory context appropriate for multiple function calls */
2978 oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
2980 /* total number of tuples to be returned */
2981 funcctx->max_calls = PG_GETARG_UINT32(0);
2983 /* Build a tuple descriptor for our result type */
2984 if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
2986 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
2987 errmsg("function returning record called in context "
2988 "that cannot accept type record")));
2991 * generate attribute metadata needed later to produce tuples from raw
2994 attinmeta = TupleDescGetAttInMetadata(tupdesc);
2995 funcctx->attinmeta = attinmeta;
2997 MemoryContextSwitchTo(oldcontext);
3000 /* stuff done on every call of the function */
3001 funcctx = SRF_PERCALL_SETUP();
3003 call_cntr = funcctx->call_cntr;
3004 max_calls = funcctx->max_calls;
3005 attinmeta = funcctx->attinmeta;
3007 if (call_cntr < max_calls) /* do when there is more left to send */
3014 * Prepare a values array for building the returned tuple.
3015 * This should be an array of C strings which will
3016 * be processed later by the type input functions.
3018 values = (char **) palloc(3 * sizeof(char *));
3019 values[0] = (char *) palloc(16 * sizeof(char));
3020 values[1] = (char *) palloc(16 * sizeof(char));
3021 values[2] = (char *) palloc(16 * sizeof(char));
3023 snprintf(values[0], 16, "%d", 1 * PG_GETARG_INT32(1));
3024 snprintf(values[1], 16, "%d", 2 * PG_GETARG_INT32(1));
3025 snprintf(values[2], 16, "%d", 3 * PG_GETARG_INT32(1));
3028 tuple = BuildTupleFromCStrings(attinmeta, values);
3030 /* make the tuple into a datum */
3031 result = HeapTupleGetDatum(tuple);
3033 /* clean up (this is not really necessary) */
3039 SRF_RETURN_NEXT(funcctx, result);
3041 else /* do when there is no more left */
3043 SRF_RETURN_DONE(funcctx);
3049 One way to declare this function in SQL is:
3051 CREATE TYPE __retcomposite AS (f1 integer, f2 integer, f3 integer);
3053 CREATE OR REPLACE FUNCTION retcomposite(integer, integer)
3054 RETURNS SETOF __retcomposite
3055 AS '<replaceable>filename</>', 'retcomposite'
3056 LANGUAGE C IMMUTABLE STRICT;
3058 A different way is to use OUT parameters:
3060 CREATE OR REPLACE FUNCTION retcomposite(IN integer, IN integer,
3061 OUT f1 integer, OUT f2 integer, OUT f3 integer)
3062 RETURNS SETOF record
3063 AS '<replaceable>filename</>', 'retcomposite'
3064 LANGUAGE C IMMUTABLE STRICT;
3066 Notice that in this method the output type of the function is formally
3067 an anonymous <structname>record</> type.
3071 The directory <link linkend="tablefunc">contrib/tablefunc</>
3072 module in the source distribution contains more examples of
3073 set-returning functions.
3078 <title>Polymorphic Arguments and Return Types</title>
3081 C-language functions can be declared to accept and
3082 return the polymorphic types
3083 <type>anyelement</type>, <type>anyarray</type>, <type>anynonarray</type>,
3084 <type>anyenum</type>, and <type>anyrange</type>.
3085 See <xref linkend="extend-types-polymorphic"> for a more detailed explanation
3086 of polymorphic functions. When function arguments or return types
3087 are defined as polymorphic types, the function author cannot know
3088 in advance what data type it will be called with, or
3089 need to return. There are two routines provided in <filename>fmgr.h</>
3090 to allow a version-1 C function to discover the actual data types
3091 of its arguments and the type it is expected to return. The routines are
3092 called <literal>get_fn_expr_rettype(FmgrInfo *flinfo)</> and
3093 <literal>get_fn_expr_argtype(FmgrInfo *flinfo, int argnum)</>.
3094 They return the result or argument type OID, or <symbol>InvalidOid</symbol> if the
3095 information is not available.
3096 The structure <literal>flinfo</> is normally accessed as
3097 <literal>fcinfo->flinfo</>. The parameter <literal>argnum</>
3098 is zero based. <function>get_call_result_type</> can also be used
3099 as an alternative to <function>get_fn_expr_rettype</>.
3103 For example, suppose we want to write a function to accept a single
3104 element of any type, and return a one-dimensional array of that type:
3107 PG_FUNCTION_INFO_V1(make_array);
3109 make_array(PG_FUNCTION_ARGS)
3112 Oid element_type = get_fn_expr_argtype(fcinfo->flinfo, 0);
3122 if (!OidIsValid(element_type))
3123 elog(ERROR, "could not determine data type of input");
3125 /* get the provided element, being careful in case it's NULL */
3126 isnull = PG_ARGISNULL(0);
3128 element = (Datum) 0;
3130 element = PG_GETARG_DATUM(0);
3132 /* we have one dimension */
3134 /* and one element */
3136 /* and lower bound is 1 */
3139 /* get required info about the element type */
3140 get_typlenbyvalalign(element_type, &typlen, &typbyval, &typalign);
3142 /* now build the array */
3143 result = construct_md_array(&element, &isnull, ndims, dims, lbs,
3144 element_type, typlen, typbyval, typalign);
3146 PG_RETURN_ARRAYTYPE_P(result);
3152 The following command declares the function
3153 <function>make_array</function> in SQL:
3156 CREATE FUNCTION make_array(anyelement) RETURNS anyarray
3157 AS '<replaceable>DIRECTORY</replaceable>/funcs', 'make_array'
3158 LANGUAGE C IMMUTABLE;
3163 There is a variant of polymorphism that is only available to C-language
3164 functions: they can be declared to take parameters of type
3165 <literal>"any"</>. (Note that this type name must be double-quoted,
3166 since it's also a SQL reserved word.) This works like
3167 <type>anyelement</> except that it does not constrain different
3168 <literal>"any"</> arguments to be the same type, nor do they help
3169 determine the function's result type. A C-language function can also
3170 declare its final parameter to be <literal>VARIADIC "any"</>. This will
3171 match one or more actual arguments of any type (not necessarily the same
3172 type). These arguments will <emphasis>not</> be gathered into an array
3173 as happens with normal variadic functions; they will just be passed to
3174 the function separately. The <function>PG_NARGS()</> macro and the
3175 methods described above must be used to determine the number of actual
3176 arguments and their types when using this feature.
3180 <sect2 id="xfunc-transform-functions">
3181 <title>Transform Functions</title>
3184 Some function calls can be simplified during planning based on
3185 properties specific to the function. For example,
3186 <literal>int4mul(n, 1)</> could be simplified to just <literal>n</>.
3187 To define such function-specific optimizations, write a
3188 <firstterm>transform function</> and place its OID in the
3189 <structfield>protransform</> field of the primary function's
3190 <structname>pg_proc</> entry. The transform function must have the SQL
3191 signature <literal>protransform(internal) RETURNS internal</>. The
3192 argument, actually <type>FuncExpr *</>, is a dummy node representing a
3193 call to the primary function. If the transform function's study of the
3194 expression tree proves that a simplified expression tree can substitute
3195 for all possible concrete calls represented thereby, build and return
3196 that simplified expression. Otherwise, return a <literal>NULL</>
3197 pointer (<emphasis>not</> a SQL null).
3201 We make no guarantee that <productname>PostgreSQL</> will never call the
3202 primary function in cases that the transform function could simplify.
3203 Ensure rigorous equivalence between the simplified expression and an
3204 actual call to the primary function.
3208 Currently, this facility is not exposed to users at the SQL level
3209 because of security concerns, so it is only practical to use for
3210 optimizing built-in functions.
3215 <title>Shared Memory and LWLocks</title>
3218 Add-ins can reserve LWLocks and an allocation of shared memory on server
3219 startup. The add-in's shared library must be preloaded by specifying
3221 <xref linkend="guc-shared-preload-libraries"><indexterm><primary>shared_preload_libraries</></>.
3222 Shared memory is reserved by calling:
3224 void RequestAddinShmemSpace(int size)
3226 from your <function>_PG_init</> function.
3229 LWLocks are reserved by calling:
3231 void RequestAddinLWLocks(int n)
3233 from <function>_PG_init</>.
3236 To avoid possible race-conditions, each backend should use the LWLock
3237 <function>AddinShmemInitLock</> when connecting to and initializing
3238 its allocation of shared memory, as shown here:
3240 static mystruct *ptr = NULL;
3246 LWLockAcquire(AddinShmemInitLock, LW_EXCLUSIVE);
3247 ptr = ShmemInitStruct("my struct name", size, &found);
3250 initialize contents of shmem area;
3251 acquire any requested LWLocks using:
3252 ptr->mylockid = LWLockAssign();
3254 LWLockRelease(AddinShmemInitLock);
3260 <sect2 id="extend-Cpp">
3261 <title>Using C++ for Extensibility</title>
3263 <indexterm zone="extend-Cpp">
3264 <primary>C++</primary>
3268 Although the <productname>PostgreSQL</productname> backend is written in
3269 C, it is possible to write extensions in C++ if these guidelines are
3275 All functions accessed by the backend must present a C interface
3276 to the backend; these C functions can then call C++ functions.
3277 For example, <literal>extern C</> linkage is required for
3278 backend-accessed functions. This is also necessary for any
3279 functions that are passed as pointers between the backend and
3285 Free memory using the appropriate deallocation method. For example,
3286 most backend memory is allocated using <function>palloc()</>, so use
3287 <function>pfree()</> to free it. Using C++
3288 <function>delete</> in such cases will fail.
3293 Prevent exceptions from propagating into the C code (use a catch-all
3294 block at the top level of all <literal>extern C</> functions). This
3295 is necessary even if the C++ code does not explicitly throw any
3296 exceptions, because events like out-of-memory can still throw
3297 exceptions. Any exceptions must be caught and appropriate errors
3298 passed back to the C interface. If possible, compile C++ with
3299 <option>-fno-exceptions</> to eliminate exceptions entirely; in such
3300 cases, you must check for failures in your C++ code, e.g. check for
3301 NULL returned by <function>new()</>.
3306 If calling backend functions from C++ code, be sure that the
3307 C++ call stack contains only plain old data structures
3308 (<acronym>POD</>). This is necessary because backend errors
3309 generate a distant <function>longjmp()</> that does not properly
3310 unroll a C++ call stack with non-POD objects.
3317 In summary, it is best to place C++ code behind a wall of
3318 <literal>extern C</> functions that interface to the backend,
3319 and avoid exception, memory, and call stack leakage.
projects / postgresql / blob