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</>, into an <acronym>SQL</acronym> function.)
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">).
158 Arguments to the SQL function are referenced in the function
159 body using the syntax <literal>$<replaceable>n</></>: <literal>$1</>
160 refers to the first argument, <literal>$2</> to the second, and so on.
161 If an argument is of a composite type, then the dot notation,
162 e.g., <literal>$1.name</literal>, can be used to access attributes
163 of the argument. The arguments can only be used as data values,
164 not as identifiers. Thus for example this is reasonable:
166 INSERT INTO mytable VALUES ($1);
168 but this will not work:
170 INSERT INTO $1 VALUES (42);
174 <sect2 id="xfunc-sql-base-functions">
175 <title><acronym>SQL</acronym> Functions on Base Types</title>
178 The simplest possible <acronym>SQL</acronym> function has no arguments and
179 simply returns a base type, such as <type>integer</type>:
182 CREATE FUNCTION one() RETURNS integer AS $$
186 -- Alternative syntax for string literal:
187 CREATE FUNCTION one() RETURNS integer AS '
200 Notice that we defined a column alias within the function body for the result of the function
201 (with the name <literal>result</>), but this column alias is not visible
202 outside the function. Hence, the result is labeled <literal>one</>
203 instead of <literal>result</>.
207 It is almost as easy to define <acronym>SQL</acronym> functions
208 that take base types as arguments. In the example below, notice
209 how we refer to the arguments within the function as <literal>$1</>
213 CREATE FUNCTION add_em(integer, integer) RETURNS integer AS $$
217 SELECT add_em(1, 2) AS answer;
226 Here is a more useful function, which might be used to debit a
230 CREATE FUNCTION tf1 (integer, numeric) RETURNS integer AS $$
232 SET balance = balance - $2
233 WHERE accountno = $1;
238 A user could execute this function to debit account 17 by $100.00 as
242 SELECT tf1(17, 100.0);
247 In practice one would probably like a more useful result from the
248 function than a constant 1, so a more likely definition
252 CREATE FUNCTION tf1 (integer, numeric) RETURNS numeric AS $$
254 SET balance = balance - $2
255 WHERE accountno = $1;
256 SELECT balance FROM bank WHERE accountno = $1;
260 which adjusts the balance and returns the new balance.
261 The same thing could be done in one command using <literal>RETURNING</>:
264 CREATE FUNCTION tf1 (integer, numeric) RETURNS numeric AS $$
266 SET balance = balance - $2
274 <sect2 id="xfunc-sql-composite-functions">
275 <title><acronym>SQL</acronym> Functions on Composite Types</title>
278 When writing functions with arguments of composite
279 types, we must not only specify which
280 argument we want (as we did above with <literal>$1</> and <literal>$2</literal>) but
281 also the desired attribute (field) of that argument. For example,
283 <type>emp</type> is a table containing employee data, and therefore
284 also the name of the composite type of each row of the table. Here
285 is a function <function>double_salary</function> that computes what someone's
286 salary would be if it were doubled:
296 INSERT INTO emp VALUES ('Bill', 4200, 45, '(2,1)');
298 CREATE FUNCTION double_salary(emp) RETURNS numeric AS $$
299 SELECT $1.salary * 2 AS salary;
302 SELECT name, double_salary(emp.*) AS dream
304 WHERE emp.cubicle ~= point '(2,1)';
313 Notice the use of the syntax <literal>$1.salary</literal>
314 to select one field of the argument row value. Also notice
315 how the calling <command>SELECT</> command uses <literal>*</>
317 the entire current row of a table as a composite value. The table
318 row can alternatively be referenced using just the table name,
321 SELECT name, double_salary(emp) AS dream
323 WHERE emp.cubicle ~= point '(2,1)';
325 but this usage is deprecated since it's easy to get confused.
329 Sometimes it is handy to construct a composite argument value
330 on-the-fly. This can be done with the <literal>ROW</> construct.
331 For example, we could adjust the data being passed to the function:
333 SELECT name, double_salary(ROW(name, salary*1.1, age, cubicle)) AS dream
339 It is also possible to build a function that returns a composite type.
340 This is an example of a function
341 that returns a single <type>emp</type> row:
344 CREATE FUNCTION new_emp() RETURNS emp AS $$
345 SELECT text 'None' AS name,
348 point '(2,2)' AS cubicle;
352 In this example we have specified each of the attributes
353 with a constant value, but any computation
354 could have been substituted for these constants.
358 Note two important things about defining the function:
363 The select list order in the query must be exactly the same as
364 that in which the columns appear in the table associated
365 with the composite type. (Naming the columns, as we did above,
366 is irrelevant to the system.)
371 You must typecast the expressions to match the
372 definition of the composite type, or you will get errors like this:
375 ERROR: function declared to return emp returns varchar instead of text at column 1
384 A different way to define the same function is:
387 CREATE FUNCTION new_emp() RETURNS emp AS $$
388 SELECT ROW('None', 1000.0, 25, '(2,2)')::emp;
392 Here we wrote a <command>SELECT</> that returns just a single
393 column of the correct composite type. This isn't really better
394 in this situation, but it is a handy alternative in some cases
395 — for example, if we need to compute the result by calling
396 another function that returns the desired composite value.
400 We could call this function directly in either of two ways:
406 --------------------------
407 (None,1000.0,25,"(2,2)")
409 SELECT * FROM new_emp();
411 name | salary | age | cubicle
412 ------+--------+-----+---------
413 None | 1000.0 | 25 | (2,2)
416 The second way is described more fully in <xref
417 linkend="xfunc-sql-table-functions">.
421 When you use a function that returns a composite type,
422 you might want only one field (attribute) from its result.
423 You can do that with syntax like this:
426 SELECT (new_emp()).name;
433 The extra parentheses are needed to keep the parser from getting
434 confused. If you try to do it without them, you get something like this:
437 SELECT new_emp().name;
438 ERROR: syntax error at or near "."
439 LINE 1: SELECT new_emp().name;
445 Another option is to use
446 functional notation for extracting an attribute. The simple way
447 to explain this is that we can use the
448 notations <literal>attribute(table)</> and <literal>table.attribute</>
452 SELECT name(new_emp());
460 -- This is the same as:
461 -- SELECT emp.name AS youngster FROM emp WHERE emp.age < 30;
463 SELECT name(emp) AS youngster FROM emp WHERE age(emp) < 30;
474 The equivalence between functional notation and attribute notation
475 makes it possible to use functions on composite types to emulate
476 <quote>computed fields</>.
478 <primary>computed field</primary>
481 <primary>field</primary>
482 <secondary>computed</secondary>
484 For example, using the previous definition
485 for <literal>double_salary(emp)</>, we can write
488 SELECT emp.name, emp.double_salary FROM emp;
491 An application using this wouldn't need to be directly aware that
492 <literal>double_salary</> isn't a real column of the table.
493 (You can also emulate computed fields with views.)
497 Because of this behavior, it's unwise to give a function that takes
498 a single composite-type argument the same name as any of the fields of
504 Another way to use a function returning a composite type is to pass the
505 result to another function that accepts the correct row type as input:
508 CREATE FUNCTION getname(emp) RETURNS text AS $$
512 SELECT getname(new_emp());
521 Still another way to use a function that returns a composite type is to
522 call it as a table function, as described in <xref
523 linkend="xfunc-sql-table-functions">.
527 <sect2 id="xfunc-named-parameters">
528 <title><acronym>SQL</> Functions with Parameter Names</title>
531 <primary>function</primary>
532 <secondary>named parameter</secondary>
536 It is possible to attach names to a function's parameters, for example
539 CREATE FUNCTION tf1 (acct_no integer, debit numeric) RETURNS numeric AS $$
541 SET balance = balance - $2
547 Here the first parameter has been given the name <literal>acct_no</>,
548 and the second parameter the name <literal>debit</>.
549 So far as the SQL function itself is concerned, these names are just
550 decoration; you must still refer to the parameters as <literal>$1</>,
551 <literal>$2</>, etc within the function body. (Some procedural
552 languages let you use the parameter names instead.) However,
553 attaching names to the parameters is useful for documentation purposes.
554 When a function has many parameters, it is also useful to use the names
555 while calling the function, as described in
556 <xref linkend="sql-syntax-calling-funcs">.
560 <sect2 id="xfunc-output-parameters">
561 <title><acronym>SQL</> Functions with Output Parameters</title>
564 <primary>function</primary>
565 <secondary>output parameter</secondary>
569 An alternative way of describing a function's results is to define it
570 with <firstterm>output parameters</>, as in this example:
573 CREATE FUNCTION add_em (IN x int, IN y int, OUT sum int)
584 This is not essentially different from the version of <literal>add_em</>
585 shown in <xref linkend="xfunc-sql-base-functions">. The real value of
586 output parameters is that they provide a convenient way of defining
587 functions that return several columns. For example,
590 CREATE FUNCTION sum_n_product (x int, y int, OUT sum int, OUT product int)
591 AS 'SELECT $1 + $2, $1 * $2'
594 SELECT * FROM sum_n_product(11,42);
601 What has essentially happened here is that we have created an anonymous
602 composite type for the result of the function. The above example has
603 the same end result as
606 CREATE TYPE sum_prod AS (sum int, product int);
608 CREATE FUNCTION sum_n_product (int, int) RETURNS sum_prod
609 AS 'SELECT $1 + $2, $1 * $2'
613 but not having to bother with the separate composite type definition
614 is often handy. Notice that the names attached to the output parameters
615 are not just decoration, but determine the column names of the anonymous
616 composite type. (If you omit a name for an output parameter, the
617 system will choose a name on its own.)
621 Notice that output parameters are not included in the calling argument
622 list when invoking such a function from SQL. This is because
623 <productname>PostgreSQL</productname> considers only the input
624 parameters to define the function's calling signature. That means
625 also that only the input parameters matter when referencing the function
626 for purposes such as dropping it. We could drop the above function
630 DROP FUNCTION sum_n_product (x int, y int, OUT sum int, OUT product int);
631 DROP FUNCTION sum_n_product (int, int);
636 Parameters can be marked as <literal>IN</> (the default),
637 <literal>OUT</>, <literal>INOUT</>, or <literal>VARIADIC</>.
639 parameter serves as both an input parameter (part of the calling
640 argument list) and an output parameter (part of the result record type).
641 <literal>VARIADIC</> parameters are input parameters, but are treated
642 specially as described next.
646 <sect2 id="xfunc-sql-variadic-functions">
647 <title><acronym>SQL</> Functions with Variable Numbers of Arguments</title>
650 <primary>function</primary>
651 <secondary>variadic</secondary>
655 <primary>variadic function</primary>
659 <acronym>SQL</acronym> functions can be declared to accept
660 variable numbers of arguments, so long as all the <quote>optional</>
661 arguments are of the same data type. The optional arguments will be
662 passed to the function as an array. The function is declared by
663 marking the last parameter as <literal>VARIADIC</>; this parameter
664 must be declared as being of an array type. For example:
667 CREATE FUNCTION mleast(VARIADIC arr numeric[]) RETURNS numeric AS $$
668 SELECT min($1[i]) FROM generate_subscripts($1, 1) g(i);
671 SELECT mleast(10, -1, 5, 4.4);
678 Effectively, all the actual arguments at or beyond the
679 <literal>VARIADIC</> position are gathered up into a one-dimensional
680 array, as if you had written
683 SELECT mleast(ARRAY[10, -1, 5, 4.4]); -- doesn't work
686 You can't actually write that, though — or at least, it will
687 not match this function definition. A parameter marked
688 <literal>VARIADIC</> matches one or more occurrences of its element
689 type, not of its own type.
693 Sometimes it is useful to be able to pass an already-constructed array
694 to a variadic function; this is particularly handy when one variadic
695 function wants to pass on its array parameter to another one. You can
696 do that by specifying <literal>VARIADIC</> in the call:
699 SELECT mleast(VARIADIC ARRAY[10, -1, 5, 4.4]);
702 This prevents expansion of the function's variadic parameter into its
703 element type, thereby allowing the array argument value to match
704 normally. <literal>VARIADIC</> can only be attached to the last
705 actual argument of a function call.
709 The array element parameters generated from a variadic parameter are
710 treated as not having any names of their own. This means it is not
711 possible to call a variadic function using named arguments (<xref
712 linkend="sql-syntax-calling-funcs">), except when you specify
713 <literal>VARIADIC</>. For example, this will work:
716 SELECT mleast(VARIADIC arr := ARRAY[10, -1, 5, 4.4]);
722 SELECT mleast(arr := 10);
723 SELECT mleast(arr := ARRAY[10, -1, 5, 4.4]);
728 <sect2 id="xfunc-sql-parameter-defaults">
729 <title><acronym>SQL</> Functions with Default Values for Arguments</title>
732 <primary>function</primary>
733 <secondary>default values for arguments</secondary>
737 Functions can be declared with default values for some or all input
738 arguments. The default values are inserted whenever the function is
739 called with insufficiently many actual arguments. Since arguments
740 can only be omitted from the end of the actual argument list, all
741 parameters after a parameter with a default value have to have
742 default values as well. (Although the use of named argument notation
743 could allow this restriction to be relaxed, it's still enforced so that
744 positional argument notation works sensibly.)
750 CREATE FUNCTION foo(a int, b int DEFAULT 2, c int DEFAULT 3)
757 SELECT foo(10, 20, 30);
775 SELECT foo(); -- fails since there is no default for the first argument
776 ERROR: function foo() does not exist
778 The <literal>=</literal> sign can also be used in place of the
779 key word <literal>DEFAULT</literal>.
783 <sect2 id="xfunc-sql-table-functions">
784 <title><acronym>SQL</acronym> Functions as Table Sources</title>
787 All SQL functions can be used in the <literal>FROM</> clause of a query,
788 but it is particularly useful for functions returning composite types.
789 If the function is defined to return a base type, the table function
790 produces a one-column table. If the function is defined to return
791 a composite type, the table function produces a column for each attribute
792 of the composite type.
799 CREATE TABLE foo (fooid int, foosubid int, fooname text);
800 INSERT INTO foo VALUES (1, 1, 'Joe');
801 INSERT INTO foo VALUES (1, 2, 'Ed');
802 INSERT INTO foo VALUES (2, 1, 'Mary');
804 CREATE FUNCTION getfoo(int) RETURNS foo AS $$
805 SELECT * FROM foo WHERE fooid = $1;
808 SELECT *, upper(fooname) FROM getfoo(1) AS t1;
810 fooid | foosubid | fooname | upper
811 -------+----------+---------+-------
816 As the example shows, we can work with the columns of the function's
817 result just the same as if they were columns of a regular table.
821 Note that we only got one row out of the function. This is because
822 we did not use <literal>SETOF</>. That is described in the next section.
826 <sect2 id="xfunc-sql-functions-returning-set">
827 <title><acronym>SQL</acronym> Functions Returning Sets</title>
830 <primary>function</primary>
831 <secondary>with SETOF</secondary>
835 When an SQL function is declared as returning <literal>SETOF
836 <replaceable>sometype</></literal>, the function's final
837 query is executed to completion, and each row it
838 outputs is returned as an element of the result set.
842 This feature is normally used when calling the function in the <literal>FROM</>
843 clause. In this case each row returned by the function becomes
844 a row of the table seen by the query. For example, assume that
845 table <literal>foo</> has the same contents as above, and we say:
848 CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
849 SELECT * FROM foo WHERE fooid = $1;
852 SELECT * FROM getfoo(1) AS t1;
857 fooid | foosubid | fooname
858 -------+----------+---------
866 It is also possible to return multiple rows with the columns defined by
867 output parameters, like this:
870 CREATE TABLE tab (y int, z int);
871 INSERT INTO tab VALUES (1, 2), (3, 4), (5, 6), (7, 8);
873 CREATE FUNCTION sum_n_product_with_tab (x int, OUT sum int, OUT product int)
876 SELECT $1 + tab.y, $1 * tab.y FROM tab;
879 SELECT * FROM sum_n_product_with_tab(10);
889 The key point here is that you must write <literal>RETURNS SETOF record</>
890 to indicate that the function returns multiple rows instead of just one.
891 If there is only one output parameter, write that parameter's type
892 instead of <type>record</>.
896 Currently, functions returning sets can also be called in the select list
897 of a query. For each row that the query
898 generates by itself, the function returning set is invoked, and an output
899 row is generated for each element of the function's result set. Note,
900 however, that this capability is deprecated and might be removed in future
901 releases. The following is an example function returning a set from the
905 CREATE FUNCTION listchildren(text) RETURNS SETOF text AS $$
906 SELECT name FROM nodes WHERE parent = $1
920 SELECT listchildren('Top');
928 SELECT name, listchildren(name) FROM nodes;
930 --------+--------------
939 In the last <command>SELECT</command>,
940 notice that no output row appears for <literal>Child2</>, <literal>Child3</>, etc.
941 This happens because <function>listchildren</function> returns an empty set
942 for those arguments, so no result rows are generated.
947 If a function's last command is <command>INSERT</>, <command>UPDATE</>,
948 or <command>DELETE</> with <literal>RETURNING</>, that command will
949 always be executed to completion, even if the function is not declared
950 with <literal>SETOF</> or the calling query does not fetch all the
951 result rows. Any extra rows produced by the <literal>RETURNING</>
952 clause are silently dropped, but the commanded table modifications
953 still happen (and are all completed before returning from the function).
958 <sect2 id="xfunc-sql-functions-returning-table">
959 <title><acronym>SQL</acronym> Functions Returning <literal>TABLE</></title>
962 <primary>function</primary>
963 <secondary>RETURNS TABLE</secondary>
967 There is another way to declare a function as returning a set,
968 which is to use the syntax
969 <literal>RETURNS TABLE(<replaceable>columns</>)</literal>.
970 This is equivalent to using one or more <literal>OUT</> parameters plus
971 marking the function as returning <literal>SETOF record</> (or
972 <literal>SETOF</> a single output parameter's type, as appropriate).
973 This notation is specified in recent versions of the SQL standard, and
974 thus may be more portable than using <literal>SETOF</>.
978 For example, the preceding sum-and-product example could also be
982 CREATE FUNCTION sum_n_product_with_tab (x int)
983 RETURNS TABLE(sum int, product int) AS $$
984 SELECT $1 + tab.y, $1 * tab.y FROM tab;
988 It is not allowed to use explicit <literal>OUT</> or <literal>INOUT</>
989 parameters with the <literal>RETURNS TABLE</> notation — you must
990 put all the output columns in the <literal>TABLE</> list.
995 <title>Polymorphic <acronym>SQL</acronym> Functions</title>
998 <acronym>SQL</acronym> functions can be declared to accept and
999 return the polymorphic types <type>anyelement</type>,
1000 <type>anyarray</type>, <type>anynonarray</type>, and
1001 <type>anyenum</type>. See <xref
1002 linkend="extend-types-polymorphic"> for a more detailed
1003 explanation of polymorphic functions. Here is a polymorphic
1004 function <function>make_array</function> that builds up an array
1005 from two arbitrary data type elements:
1007 CREATE FUNCTION make_array(anyelement, anyelement) RETURNS anyarray AS $$
1008 SELECT ARRAY[$1, $2];
1011 SELECT make_array(1, 2) AS intarray, make_array('a'::text, 'b') AS textarray;
1012 intarray | textarray
1013 ----------+-----------
1020 Notice the use of the typecast <literal>'a'::text</literal>
1021 to specify that the argument is of type <type>text</type>. This is
1022 required if the argument is just a string literal, since otherwise
1023 it would be treated as type
1024 <type>unknown</type>, and array of <type>unknown</type> is not a valid
1026 Without the typecast, you will get errors like this:
1029 ERROR: could not determine polymorphic type because input has type "unknown"
1035 It is permitted to have polymorphic arguments with a fixed
1036 return type, but the converse is not. For example:
1038 CREATE FUNCTION is_greater(anyelement, anyelement) RETURNS boolean AS $$
1042 SELECT is_greater(1, 2);
1048 CREATE FUNCTION invalid_func() RETURNS anyelement AS $$
1051 ERROR: cannot determine result data type
1052 DETAIL: A function returning a polymorphic type must have at least one polymorphic argument.
1057 Polymorphism can be used with functions that have output arguments.
1060 CREATE FUNCTION dup (f1 anyelement, OUT f2 anyelement, OUT f3 anyarray)
1061 AS 'select $1, array[$1,$1]' LANGUAGE SQL;
1063 SELECT * FROM dup(22);
1072 Polymorphism can also be used with variadic functions.
1075 CREATE FUNCTION anyleast (VARIADIC anyarray) RETURNS anyelement AS $$
1076 SELECT min($1[i]) FROM generate_subscripts($1, 1) g(i);
1079 SELECT anyleast(10, -1, 5, 4);
1085 SELECT anyleast('abc'::text, 'def');
1091 CREATE FUNCTION concat_values(text, VARIADIC anyarray) RETURNS text AS $$
1092 SELECT array_to_string($2, $1);
1095 SELECT concat_values('|', 1, 4, 2);
1105 <sect1 id="xfunc-overload">
1106 <title>Function Overloading</title>
1108 <indexterm zone="xfunc-overload">
1109 <primary>overloading</primary>
1110 <secondary>functions</secondary>
1114 More than one function can be defined with the same SQL name, so long
1115 as the arguments they take are different. In other words,
1116 function names can be <firstterm>overloaded</firstterm>. When a
1117 query is executed, the server will determine which function to
1118 call from the data types and the number of the provided arguments.
1119 Overloading can also be used to simulate functions with a variable
1120 number of arguments, up to a finite maximum number.
1124 When creating a family of overloaded functions, one should be
1125 careful not to create ambiguities. For instance, given the
1128 CREATE FUNCTION test(int, real) RETURNS ...
1129 CREATE FUNCTION test(smallint, double precision) RETURNS ...
1131 it is not immediately clear which function would be called with
1132 some trivial input like <literal>test(1, 1.5)</literal>. The
1133 currently implemented resolution rules are described in
1134 <xref linkend="typeconv">, but it is unwise to design a system that subtly
1135 relies on this behavior.
1139 A function that takes a single argument of a composite type should
1140 generally not have the same name as any attribute (field) of that type.
1141 Recall that <literal>attribute(table)</literal> is considered equivalent
1142 to <literal>table.attribute</literal>. In the case that there is an
1143 ambiguity between a function on a composite type and an attribute of
1144 the composite type, the attribute will always be used. It is possible
1145 to override that choice by schema-qualifying the function name
1146 (that is, <literal>schema.func(table)</literal>) but it's better to
1147 avoid the problem by not choosing conflicting names.
1151 Another possible conflict is between variadic and non-variadic functions.
1152 For instance, it is possible to create both <literal>foo(numeric)</> and
1153 <literal>foo(VARIADIC numeric[])</>. In this case it is unclear which one
1154 should be matched to a call providing a single numeric argument, such as
1155 <literal>foo(10.1)</>. The rule is that the function appearing
1156 earlier in the search path is used, or if the two functions are in the
1157 same schema, the non-variadic one is preferred.
1161 When overloading C-language functions, there is an additional
1162 constraint: The C name of each function in the family of
1163 overloaded functions must be different from the C names of all
1164 other functions, either internal or dynamically loaded. If this
1165 rule is violated, the behavior is not portable. You might get a
1166 run-time linker error, or one of the functions will get called
1167 (usually the internal one). The alternative form of the
1168 <literal>AS</> clause for the SQL <command>CREATE
1169 FUNCTION</command> command decouples the SQL function name from
1170 the function name in the C source code. For instance:
1172 CREATE FUNCTION test(int) RETURNS int
1173 AS '<replaceable>filename</>', 'test_1arg'
1175 CREATE FUNCTION test(int, int) RETURNS int
1176 AS '<replaceable>filename</>', 'test_2arg'
1179 The names of the C functions here reflect one of many possible conventions.
1183 <sect1 id="xfunc-volatility">
1184 <title>Function Volatility Categories</title>
1186 <indexterm zone="xfunc-volatility">
1187 <primary>volatility</primary>
1188 <secondary>functions</secondary>
1190 <indexterm zone="xfunc-volatility">
1191 <primary>VOLATILE</primary>
1193 <indexterm zone="xfunc-volatility">
1194 <primary>STABLE</primary>
1196 <indexterm zone="xfunc-volatility">
1197 <primary>IMMUTABLE</primary>
1201 Every function has a <firstterm>volatility</> classification, with
1202 the possibilities being <literal>VOLATILE</>, <literal>STABLE</>, or
1203 <literal>IMMUTABLE</>. <literal>VOLATILE</> is the default if the
1204 <xref linkend="sql-createfunction">
1205 command does not specify a category. The volatility category is a
1206 promise to the optimizer about the behavior of the function:
1211 A <literal>VOLATILE</> function can do anything, including modifying
1212 the database. It can return different results on successive calls with
1213 the same arguments. The optimizer makes no assumptions about the
1214 behavior of such functions. A query using a volatile function will
1215 re-evaluate the function at every row where its value is needed.
1220 A <literal>STABLE</> function cannot modify the database and is
1221 guaranteed to return the same results given the same arguments
1222 for all rows within a single statement. This category allows the
1223 optimizer to optimize multiple calls of the function to a single
1224 call. In particular, it is safe to use an expression containing
1225 such a function in an index scan condition. (Since an index scan
1226 will evaluate the comparison value only once, not once at each
1227 row, it is not valid to use a <literal>VOLATILE</> function in an
1228 index scan condition.)
1233 An <literal>IMMUTABLE</> function cannot modify the database and is
1234 guaranteed to return the same results given the same arguments forever.
1235 This category allows the optimizer to pre-evaluate the function when
1236 a query calls it with constant arguments. For example, a query like
1237 <literal>SELECT ... WHERE x = 2 + 2</> can be simplified on sight to
1238 <literal>SELECT ... WHERE x = 4</>, because the function underlying
1239 the integer addition operator is marked <literal>IMMUTABLE</>.
1246 For best optimization results, you should label your functions with the
1247 strictest volatility category that is valid for them.
1251 Any function with side-effects <emphasis>must</> be labeled
1252 <literal>VOLATILE</>, so that calls to it cannot be optimized away.
1253 Even a function with no side-effects needs to be labeled
1254 <literal>VOLATILE</> if its value can change within a single query;
1255 some examples are <literal>random()</>, <literal>currval()</>,
1256 <literal>timeofday()</>.
1260 Another important example is that the <function>current_timestamp</>
1261 family of functions qualify as <literal>STABLE</>, since their values do
1262 not change within a transaction.
1266 There is relatively little difference between <literal>STABLE</> and
1267 <literal>IMMUTABLE</> categories when considering simple interactive
1268 queries that are planned and immediately executed: it doesn't matter
1269 a lot whether a function is executed once during planning or once during
1270 query execution startup. But there is a big difference if the plan is
1271 saved and reused later. Labeling a function <literal>IMMUTABLE</> when
1272 it really isn't might allow it to be prematurely folded to a constant during
1273 planning, resulting in a stale value being re-used during subsequent uses
1274 of the plan. This is a hazard when using prepared statements or when
1275 using function languages that cache plans (such as
1276 <application>PL/pgSQL</>).
1280 For functions written in SQL or in any of the standard procedural
1281 languages, there is a second important property determined by the
1282 volatility category, namely the visibility of any data changes that have
1283 been made by the SQL command that is calling the function. A
1284 <literal>VOLATILE</> function will see such changes, a <literal>STABLE</>
1285 or <literal>IMMUTABLE</> function will not. This behavior is implemented
1286 using the snapshotting behavior of MVCC (see <xref linkend="mvcc">):
1287 <literal>STABLE</> and <literal>IMMUTABLE</> functions use a snapshot
1288 established as of the start of the calling query, whereas
1289 <literal>VOLATILE</> functions obtain a fresh snapshot at the start of
1290 each query they execute.
1295 Functions written in C can manage snapshots however they want, but it's
1296 usually a good idea to make C functions work this way too.
1301 Because of this snapshotting behavior,
1302 a function containing only <command>SELECT</> commands can safely be
1303 marked <literal>STABLE</>, even if it selects from tables that might be
1304 undergoing modifications by concurrent queries.
1305 <productname>PostgreSQL</productname> will execute all commands of a
1306 <literal>STABLE</> function using the snapshot established for the
1307 calling query, and so it will see a fixed view of the database throughout
1312 The same snapshotting behavior is used for <command>SELECT</> commands
1313 within <literal>IMMUTABLE</> functions. It is generally unwise to select
1314 from database tables within an <literal>IMMUTABLE</> function at all,
1315 since the immutability will be broken if the table contents ever change.
1316 However, <productname>PostgreSQL</productname> does not enforce that you
1321 A common error is to label a function <literal>IMMUTABLE</> when its
1322 results depend on a configuration parameter. For example, a function
1323 that manipulates timestamps might well have results that depend on the
1324 <xref linkend="guc-timezone"> setting. For safety, such functions should
1325 be labeled <literal>STABLE</> instead.
1330 Before <productname>PostgreSQL</productname> release 8.0, the requirement
1331 that <literal>STABLE</> and <literal>IMMUTABLE</> functions cannot modify
1332 the database was not enforced by the system. Releases 8.0 and later enforce it
1333 by requiring SQL functions and procedural language functions of these
1334 categories to contain no SQL commands other than <command>SELECT</>.
1335 (This is not a completely bulletproof test, since such functions could
1336 still call <literal>VOLATILE</> functions that modify the database.
1337 If you do that, you will find that the <literal>STABLE</> or
1338 <literal>IMMUTABLE</> function does not notice the database changes
1339 applied by the called function, since they are hidden from its snapshot.)
1344 <sect1 id="xfunc-pl">
1345 <title>Procedural Language Functions</title>
1348 <productname>PostgreSQL</productname> allows user-defined functions
1349 to be written in other languages besides SQL and C. These other
1350 languages are generically called <firstterm>procedural
1351 languages</firstterm> (<acronym>PL</>s).
1352 Procedural languages aren't built into the
1353 <productname>PostgreSQL</productname> server; they are offered
1354 by loadable modules.
1355 See <xref linkend="xplang"> and following chapters for more
1360 <sect1 id="xfunc-internal">
1361 <title>Internal Functions</title>
1363 <indexterm zone="xfunc-internal"><primary>function</><secondary>internal</></>
1366 Internal functions are functions written in C that have been statically
1367 linked into the <productname>PostgreSQL</productname> server.
1368 The <quote>body</quote> of the function definition
1369 specifies the C-language name of the function, which need not be the
1370 same as the name being declared for SQL use.
1371 (For reasons of backwards compatibility, an empty body
1372 is accepted as meaning that the C-language function name is the
1373 same as the SQL name.)
1377 Normally, all internal functions present in the
1378 server are declared during the initialization of the database cluster
1379 (see <xref linkend="creating-cluster">),
1380 but a user could use <command>CREATE FUNCTION</command>
1381 to create additional alias names for an internal function.
1382 Internal functions are declared in <command>CREATE FUNCTION</command>
1383 with language name <literal>internal</literal>. For instance, to
1384 create an alias for the <function>sqrt</function> function:
1386 CREATE FUNCTION square_root(double precision) RETURNS double precision
1391 (Most internal functions expect to be declared <quote>strict</quote>.)
1396 Not all <quote>predefined</quote> functions are
1397 <quote>internal</quote> in the above sense. Some predefined
1398 functions are written in SQL.
1403 <sect1 id="xfunc-c">
1404 <title>C-Language Functions</title>
1406 <indexterm zone="xfunc-c">
1407 <primary>function</primary>
1408 <secondary>user-defined</secondary>
1409 <tertiary>in C</tertiary>
1413 User-defined functions can be written in C (or a language that can
1414 be made compatible with C, such as C++). Such functions are
1415 compiled into dynamically loadable objects (also called shared
1416 libraries) and are loaded by the server on demand. The dynamic
1417 loading feature is what distinguishes <quote>C language</> functions
1418 from <quote>internal</> functions — the actual coding conventions
1419 are essentially the same for both. (Hence, the standard internal
1420 function library is a rich source of coding examples for user-defined
1425 Two different calling conventions are currently used for C functions.
1426 The newer <quote>version 1</quote> calling convention is indicated by writing
1427 a <literal>PG_FUNCTION_INFO_V1()</literal> macro call for the function,
1428 as illustrated below. Lack of such a macro indicates an old-style
1429 (<quote>version 0</quote>) function. The language name specified in <command>CREATE FUNCTION</command>
1430 is <literal>C</literal> in either case. Old-style functions are now deprecated
1431 because of portability problems and lack of functionality, but they
1432 are still supported for compatibility reasons.
1435 <sect2 id="xfunc-c-dynload">
1436 <title>Dynamic Loading</title>
1438 <indexterm zone="xfunc-c-dynload">
1439 <primary>dynamic loading</primary>
1443 The first time a user-defined function in a particular
1444 loadable object file is called in a session,
1445 the dynamic loader loads that object file into memory so that the
1446 function can be called. The <command>CREATE FUNCTION</command>
1447 for a user-defined C function must therefore specify two pieces of
1448 information for the function: the name of the loadable
1449 object file, and the C name (link symbol) of the specific function to call
1450 within that object file. If the C name is not explicitly specified then
1451 it is assumed to be the same as the SQL function name.
1455 The following algorithm is used to locate the shared object file
1456 based on the name given in the <command>CREATE FUNCTION</command>
1462 If the name is an absolute path, the given file is loaded.
1468 If the name starts with the string <literal>$libdir</literal>,
1469 that part is replaced by the <productname>PostgreSQL</> package
1471 name, which is determined at build time.<indexterm><primary>$libdir</></>
1477 If the name does not contain a directory part, the file is
1478 searched for in the path specified by the configuration variable
1479 <xref linkend="guc-dynamic-library-path">.<indexterm><primary>dynamic_library_path</></>
1485 Otherwise (the file was not found in the path, or it contains a
1486 non-absolute directory part), the dynamic loader will try to
1487 take the name as given, which will most likely fail. (It is
1488 unreliable to depend on the current working directory.)
1493 If this sequence does not work, the platform-specific shared
1494 library file name extension (often <filename>.so</filename>) is
1495 appended to the given name and this sequence is tried again. If
1496 that fails as well, the load will fail.
1500 It is recommended to locate shared libraries either relative to
1501 <literal>$libdir</literal> or through the dynamic library path.
1502 This simplifies version upgrades if the new installation is at a
1503 different location. The actual directory that
1504 <literal>$libdir</literal> stands for can be found out with the
1505 command <literal>pg_config --pkglibdir</literal>.
1509 The user ID the <productname>PostgreSQL</productname> server runs
1510 as must be able to traverse the path to the file you intend to
1511 load. Making the file or a higher-level directory not readable
1512 and/or not executable by the <systemitem>postgres</systemitem>
1513 user is a common mistake.
1517 In any case, the file name that is given in the
1518 <command>CREATE FUNCTION</command> command is recorded literally
1519 in the system catalogs, so if the file needs to be loaded again
1520 the same procedure is applied.
1525 <productname>PostgreSQL</productname> will not compile a C function
1526 automatically. The object file must be compiled before it is referenced
1527 in a <command>CREATE
1528 FUNCTION</> command. See <xref linkend="dfunc"> for additional
1533 <indexterm zone="xfunc-c-dynload">
1534 <primary>magic block</primary>
1538 To ensure that a dynamically loaded object file is not loaded into an
1539 incompatible server, <productname>PostgreSQL</productname> checks that the
1540 file contains a <quote>magic block</> with the appropriate contents.
1541 This allows the server to detect obvious incompatibilities, such as code
1542 compiled for a different major version of
1543 <productname>PostgreSQL</productname>. A magic block is required as of
1544 <productname>PostgreSQL</productname> 8.2. To include a magic block,
1545 write this in one (and only one) of the module source files, after having
1546 included the header <filename>fmgr.h</>:
1549 #ifdef PG_MODULE_MAGIC
1554 The <literal>#ifdef</> test can be omitted if the code doesn't
1555 need to compile against pre-8.2 <productname>PostgreSQL</productname>
1560 After it is used for the first time, a dynamically loaded object
1561 file is retained in memory. Future calls in the same session to
1562 the function(s) in that file will only incur the small overhead of
1563 a symbol table lookup. If you need to force a reload of an object
1564 file, for example after recompiling it, begin a fresh session.
1567 <indexterm zone="xfunc-c-dynload">
1568 <primary>_PG_init</primary>
1570 <indexterm zone="xfunc-c-dynload">
1571 <primary>_PG_fini</primary>
1573 <indexterm zone="xfunc-c-dynload">
1574 <primary>library initialization function</primary>
1576 <indexterm zone="xfunc-c-dynload">
1577 <primary>library finalization function</primary>
1581 Optionally, a dynamically loaded file can contain initialization and
1582 finalization functions. If the file includes a function named
1583 <function>_PG_init</>, that function will be called immediately after
1584 loading the file. The function receives no parameters and should
1585 return void. If the file includes a function named
1586 <function>_PG_fini</>, that function will be called immediately before
1587 unloading the file. Likewise, the function receives no parameters and
1588 should return void. Note that <function>_PG_fini</> will only be called
1589 during an unload of the file, not during process termination.
1590 (Presently, unloads are disabled and will never occur, but this may
1591 change in the future.)
1596 <sect2 id="xfunc-c-basetype">
1597 <title>Base Types in C-Language Functions</title>
1599 <indexterm zone="xfunc-c-basetype">
1600 <primary>data type</primary>
1601 <secondary>internal organization</secondary>
1605 To know how to write C-language functions, you need to know how
1606 <productname>PostgreSQL</productname> internally represents base
1607 data types and how they can be passed to and from functions.
1608 Internally, <productname>PostgreSQL</productname> regards a base
1609 type as a <quote>blob of memory</quote>. The user-defined
1610 functions that you define over a type in turn define the way that
1611 <productname>PostgreSQL</productname> can operate on it. That
1612 is, <productname>PostgreSQL</productname> will only store and
1613 retrieve the data from disk and use your user-defined functions
1614 to input, process, and output the data.
1618 Base types can have one of three internal formats:
1623 pass by value, fixed-length
1628 pass by reference, fixed-length
1633 pass by reference, variable-length
1640 By-value types can only be 1, 2, or 4 bytes in length
1641 (also 8 bytes, if <literal>sizeof(Datum)</literal> is 8 on your machine).
1642 You should be careful to define your types such that they will be the
1643 same size (in bytes) on all architectures. For example, the
1644 <literal>long</literal> type is dangerous because it is 4 bytes on some
1645 machines and 8 bytes on others, whereas <type>int</type> type is 4 bytes
1646 on most Unix machines. A reasonable implementation of the
1647 <type>int4</type> type on Unix machines might be:
1650 /* 4-byte integer, passed by value */
1656 On the other hand, fixed-length types of any size can
1657 be passed by-reference. For example, here is a sample
1658 implementation of a <productname>PostgreSQL</productname> type:
1661 /* 16-byte structure, passed by reference */
1668 Only pointers to such types can be used when passing
1669 them in and out of <productname>PostgreSQL</productname> functions.
1670 To return a value of such a type, allocate the right amount of
1671 memory with <literal>palloc</literal>, fill in the allocated memory,
1672 and return a pointer to it. (Also, if you just want to return the
1673 same value as one of your input arguments that's of the same data type,
1674 you can skip the extra <literal>palloc</literal> and just return the
1675 pointer to the input value.)
1679 Finally, all variable-length types must also be passed
1680 by reference. All variable-length types must begin
1681 with a length field of exactly 4 bytes, and all data to
1682 be stored within that type must be located in the memory
1683 immediately following that length field. The
1684 length field contains the total length of the structure,
1685 that is, it includes the size of the length field
1691 <emphasis>Never</> modify the contents of a pass-by-reference input
1692 value. If you do so you are likely to corrupt on-disk data, since
1693 the pointer you are given might point directly into a disk buffer.
1694 The sole exception to this rule is explained in
1695 <xref linkend="xaggr">.
1700 As an example, we can define the type <type>text</type> as
1710 Obviously, the data field declared here is not long enough to hold
1711 all possible strings. Since it's impossible to declare a variable-size
1712 structure in <acronym>C</acronym>, we rely on the knowledge that the
1713 <acronym>C</acronym> compiler won't range-check array subscripts. We
1714 just allocate the necessary amount of space and then access the array as
1715 if it were declared the right length. (This is a common trick, which
1716 you can read about in many textbooks about C.)
1721 variable-length types, we must be careful to allocate
1722 the correct amount of memory and set the length field correctly.
1723 For example, if we wanted to store 40 bytes in a <structname>text</>
1724 structure, we might use a code fragment like this:
1726 <programlisting><![CDATA[
1727 #include "postgres.h"
1729 char buffer[40]; /* our source data */
1731 text *destination = (text *) palloc(VARHDRSZ + 40);
1732 destination->length = VARHDRSZ + 40;
1733 memcpy(destination->data, buffer, 40);
1738 <literal>VARHDRSZ</> is the same as <literal>sizeof(int4)</>, but
1739 it's considered good style to use the macro <literal>VARHDRSZ</>
1740 to refer to the size of the overhead for a variable-length type.
1744 <xref linkend="xfunc-c-type-table"> specifies which C type
1745 corresponds to which SQL type when writing a C-language function
1746 that uses a built-in type of <productname>PostgreSQL</>.
1747 The <quote>Defined In</quote> column gives the header file that
1748 needs to be included to get the type definition. (The actual
1749 definition might be in a different file that is included by the
1750 listed file. It is recommended that users stick to the defined
1751 interface.) Note that you should always include
1752 <filename>postgres.h</filename> first in any source file, because
1753 it declares a number of things that you will need anyway.
1756 <table tocentry="1" id="xfunc-c-type-table">
1757 <title>Equivalent C Types for Built-in SQL Types</title>
1774 <entry><type>abstime</type></entry>
1775 <entry><type>AbsoluteTime</type></entry>
1776 <entry><filename>utils/nabstime.h</filename></entry>
1779 <entry><type>boolean</type></entry>
1780 <entry><type>bool</type></entry>
1781 <entry><filename>postgres.h</filename> (maybe compiler built-in)</entry>
1784 <entry><type>box</type></entry>
1785 <entry><type>BOX*</type></entry>
1786 <entry><filename>utils/geo_decls.h</filename></entry>
1789 <entry><type>bytea</type></entry>
1790 <entry><type>bytea*</type></entry>
1791 <entry><filename>postgres.h</filename></entry>
1794 <entry><type>"char"</type></entry>
1795 <entry><type>char</type></entry>
1796 <entry>(compiler built-in)</entry>
1799 <entry><type>character</type></entry>
1800 <entry><type>BpChar*</type></entry>
1801 <entry><filename>postgres.h</filename></entry>
1804 <entry><type>cid</type></entry>
1805 <entry><type>CommandId</type></entry>
1806 <entry><filename>postgres.h</filename></entry>
1809 <entry><type>date</type></entry>
1810 <entry><type>DateADT</type></entry>
1811 <entry><filename>utils/date.h</filename></entry>
1814 <entry><type>smallint</type> (<type>int2</type>)</entry>
1815 <entry><type>int2</type> or <type>int16</type></entry>
1816 <entry><filename>postgres.h</filename></entry>
1819 <entry><type>int2vector</type></entry>
1820 <entry><type>int2vector*</type></entry>
1821 <entry><filename>postgres.h</filename></entry>
1824 <entry><type>integer</type> (<type>int4</type>)</entry>
1825 <entry><type>int4</type> or <type>int32</type></entry>
1826 <entry><filename>postgres.h</filename></entry>
1829 <entry><type>real</type> (<type>float4</type>)</entry>
1830 <entry><type>float4*</type></entry>
1831 <entry><filename>postgres.h</filename></entry>
1834 <entry><type>double precision</type> (<type>float8</type>)</entry>
1835 <entry><type>float8*</type></entry>
1836 <entry><filename>postgres.h</filename></entry>
1839 <entry><type>interval</type></entry>
1840 <entry><type>Interval*</type></entry>
1841 <entry><filename>utils/timestamp.h</filename></entry>
1844 <entry><type>lseg</type></entry>
1845 <entry><type>LSEG*</type></entry>
1846 <entry><filename>utils/geo_decls.h</filename></entry>
1849 <entry><type>name</type></entry>
1850 <entry><type>Name</type></entry>
1851 <entry><filename>postgres.h</filename></entry>
1854 <entry><type>oid</type></entry>
1855 <entry><type>Oid</type></entry>
1856 <entry><filename>postgres.h</filename></entry>
1859 <entry><type>oidvector</type></entry>
1860 <entry><type>oidvector*</type></entry>
1861 <entry><filename>postgres.h</filename></entry>
1864 <entry><type>path</type></entry>
1865 <entry><type>PATH*</type></entry>
1866 <entry><filename>utils/geo_decls.h</filename></entry>
1869 <entry><type>point</type></entry>
1870 <entry><type>POINT*</type></entry>
1871 <entry><filename>utils/geo_decls.h</filename></entry>
1874 <entry><type>regproc</type></entry>
1875 <entry><type>regproc</type></entry>
1876 <entry><filename>postgres.h</filename></entry>
1879 <entry><type>reltime</type></entry>
1880 <entry><type>RelativeTime</type></entry>
1881 <entry><filename>utils/nabstime.h</filename></entry>
1884 <entry><type>text</type></entry>
1885 <entry><type>text*</type></entry>
1886 <entry><filename>postgres.h</filename></entry>
1889 <entry><type>tid</type></entry>
1890 <entry><type>ItemPointer</type></entry>
1891 <entry><filename>storage/itemptr.h</filename></entry>
1894 <entry><type>time</type></entry>
1895 <entry><type>TimeADT</type></entry>
1896 <entry><filename>utils/date.h</filename></entry>
1899 <entry><type>time with time zone</type></entry>
1900 <entry><type>TimeTzADT</type></entry>
1901 <entry><filename>utils/date.h</filename></entry>
1904 <entry><type>timestamp</type></entry>
1905 <entry><type>Timestamp*</type></entry>
1906 <entry><filename>utils/timestamp.h</filename></entry>
1909 <entry><type>tinterval</type></entry>
1910 <entry><type>TimeInterval</type></entry>
1911 <entry><filename>utils/nabstime.h</filename></entry>
1914 <entry><type>varchar</type></entry>
1915 <entry><type>VarChar*</type></entry>
1916 <entry><filename>postgres.h</filename></entry>
1919 <entry><type>xid</type></entry>
1920 <entry><type>TransactionId</type></entry>
1921 <entry><filename>postgres.h</filename></entry>
1928 Now that we've gone over all of the possible structures
1929 for base types, we can show some examples of real functions.
1934 <title>Version 0 Calling Conventions</title>
1937 We present the <quote>old style</quote> calling convention first — although
1938 this approach is now deprecated, it's easier to get a handle on
1939 initially. In the version-0 method, the arguments and result
1940 of the C function are just declared in normal C style, but being
1941 careful to use the C representation of each SQL data type as shown
1946 Here are some examples:
1948 <programlisting><![CDATA[
1949 #include "postgres.h"
1951 #include "utils/geo_decls.h"
1953 #ifdef PG_MODULE_MAGIC
1965 /* by reference, fixed length */
1968 add_one_float8(float8 *arg)
1970 float8 *result = (float8 *) palloc(sizeof(float8));
1972 *result = *arg + 1.0;
1978 makepoint(Point *pointx, Point *pointy)
1980 Point *new_point = (Point *) palloc(sizeof(Point));
1982 new_point->x = pointx->x;
1983 new_point->y = pointy->y;
1988 /* by reference, variable length */
1994 * VARSIZE is the total size of the struct in bytes.
1996 text *new_t = (text *) palloc(VARSIZE(t));
1997 SET_VARSIZE(new_t, VARSIZE(t));
1999 * VARDATA is a pointer to the data region of the struct.
2001 memcpy((void *) VARDATA(new_t), /* destination */
2002 (void *) VARDATA(t), /* source */
2003 VARSIZE(t) - VARHDRSZ); /* how many bytes */
2008 concat_text(text *arg1, text *arg2)
2010 int32 new_text_size = VARSIZE(arg1) + VARSIZE(arg2) - VARHDRSZ;
2011 text *new_text = (text *) palloc(new_text_size);
2013 SET_VARSIZE(new_text, new_text_size);
2014 memcpy(VARDATA(new_text), VARDATA(arg1), VARSIZE(arg1) - VARHDRSZ);
2015 memcpy(VARDATA(new_text) + (VARSIZE(arg1) - VARHDRSZ),
2016 VARDATA(arg2), VARSIZE(arg2) - VARHDRSZ);
2024 Supposing that the above code has been prepared in file
2025 <filename>funcs.c</filename> and compiled into a shared object,
2026 we could define the functions to <productname>PostgreSQL</productname>
2027 with commands like this:
2030 CREATE FUNCTION add_one(integer) RETURNS integer
2031 AS '<replaceable>DIRECTORY</replaceable>/funcs', 'add_one'
2034 -- note overloading of SQL function name "add_one"
2035 CREATE FUNCTION add_one(double precision) RETURNS double precision
2036 AS '<replaceable>DIRECTORY</replaceable>/funcs', 'add_one_float8'
2039 CREATE FUNCTION makepoint(point, point) RETURNS point
2040 AS '<replaceable>DIRECTORY</replaceable>/funcs', 'makepoint'
2043 CREATE FUNCTION copytext(text) RETURNS text
2044 AS '<replaceable>DIRECTORY</replaceable>/funcs', 'copytext'
2047 CREATE FUNCTION concat_text(text, text) RETURNS text
2048 AS '<replaceable>DIRECTORY</replaceable>/funcs', 'concat_text'
2054 Here, <replaceable>DIRECTORY</replaceable> stands for the
2055 directory of the shared library file (for instance the
2056 <productname>PostgreSQL</productname> tutorial directory, which
2057 contains the code for the examples used in this section).
2058 (Better style would be to use just <literal>'funcs'</> in the
2059 <literal>AS</> clause, after having added
2060 <replaceable>DIRECTORY</replaceable> to the search path. In any
2061 case, we can omit the system-specific extension for a shared
2062 library, commonly <literal>.so</literal> or
2063 <literal>.sl</literal>.)
2067 Notice that we have specified the functions as <quote>strict</quote>,
2069 the system should automatically assume a null result if any input
2070 value is null. By doing this, we avoid having to check for null inputs
2071 in the function code. Without this, we'd have to check for null values
2072 explicitly, by checking for a null pointer for each
2073 pass-by-reference argument. (For pass-by-value arguments, we don't
2074 even have a way to check!)
2078 Although this calling convention is simple to use,
2079 it is not very portable; on some architectures there are problems
2080 with passing data types that are smaller than <type>int</type> this way. Also, there is
2081 no simple way to return a null result, nor to cope with null arguments
2082 in any way other than making the function strict. The version-1
2083 convention, presented next, overcomes these objections.
2088 <title>Version 1 Calling Conventions</title>
2091 The version-1 calling convention relies on macros to suppress most
2092 of the complexity of passing arguments and results. The C declaration
2093 of a version-1 function is always:
2095 Datum funcname(PG_FUNCTION_ARGS)
2097 In addition, the macro call:
2099 PG_FUNCTION_INFO_V1(funcname);
2101 must appear in the same source file. (Conventionally, it's
2102 written just before the function itself.) This macro call is not
2103 needed for <literal>internal</>-language functions, since
2104 <productname>PostgreSQL</> assumes that all internal functions
2105 use the version-1 convention. It is, however, required for
2106 dynamically-loaded functions.
2110 In a version-1 function, each actual argument is fetched using a
2111 <function>PG_GETARG_<replaceable>xxx</replaceable>()</function>
2112 macro that corresponds to the argument's data type, and the
2113 result is returned using a
2114 <function>PG_RETURN_<replaceable>xxx</replaceable>()</function>
2115 macro for the return type.
2116 <function>PG_GETARG_<replaceable>xxx</replaceable>()</function>
2117 takes as its argument the number of the function argument to
2118 fetch, where the count starts at 0.
2119 <function>PG_RETURN_<replaceable>xxx</replaceable>()</function>
2120 takes as its argument the actual value to return.
2124 Here we show the same functions as above, coded in version-1 style:
2126 <programlisting><![CDATA[
2127 #include "postgres.h"
2130 #include "utils/geo_decls.h"
2132 #ifdef PG_MODULE_MAGIC
2138 PG_FUNCTION_INFO_V1(add_one);
2141 add_one(PG_FUNCTION_ARGS)
2143 int32 arg = PG_GETARG_INT32(0);
2145 PG_RETURN_INT32(arg + 1);
2148 /* by reference, fixed length */
2150 PG_FUNCTION_INFO_V1(add_one_float8);
2153 add_one_float8(PG_FUNCTION_ARGS)
2155 /* The macros for FLOAT8 hide its pass-by-reference nature. */
2156 float8 arg = PG_GETARG_FLOAT8(0);
2158 PG_RETURN_FLOAT8(arg + 1.0);
2161 PG_FUNCTION_INFO_V1(makepoint);
2164 makepoint(PG_FUNCTION_ARGS)
2166 /* Here, the pass-by-reference nature of Point is not hidden. */
2167 Point *pointx = PG_GETARG_POINT_P(0);
2168 Point *pointy = PG_GETARG_POINT_P(1);
2169 Point *new_point = (Point *) palloc(sizeof(Point));
2171 new_point->x = pointx->x;
2172 new_point->y = pointy->y;
2174 PG_RETURN_POINT_P(new_point);
2177 /* by reference, variable length */
2179 PG_FUNCTION_INFO_V1(copytext);
2182 copytext(PG_FUNCTION_ARGS)
2184 text *t = PG_GETARG_TEXT_P(0);
2186 * VARSIZE is the total size of the struct in bytes.
2188 text *new_t = (text *) palloc(VARSIZE(t));
2189 SET_VARSIZE(new_t, VARSIZE(t));
2191 * VARDATA is a pointer to the data region of the struct.
2193 memcpy((void *) VARDATA(new_t), /* destination */
2194 (void *) VARDATA(t), /* source */
2195 VARSIZE(t) - VARHDRSZ); /* how many bytes */
2196 PG_RETURN_TEXT_P(new_t);
2199 PG_FUNCTION_INFO_V1(concat_text);
2202 concat_text(PG_FUNCTION_ARGS)
2204 text *arg1 = PG_GETARG_TEXT_P(0);
2205 text *arg2 = PG_GETARG_TEXT_P(1);
2206 int32 new_text_size = VARSIZE(arg1) + VARSIZE(arg2) - VARHDRSZ;
2207 text *new_text = (text *) palloc(new_text_size);
2209 SET_VARSIZE(new_text, new_text_size);
2210 memcpy(VARDATA(new_text), VARDATA(arg1), VARSIZE(arg1) - VARHDRSZ);
2211 memcpy(VARDATA(new_text) + (VARSIZE(arg1) - VARHDRSZ),
2212 VARDATA(arg2), VARSIZE(arg2) - VARHDRSZ);
2213 PG_RETURN_TEXT_P(new_text);
2220 The <command>CREATE FUNCTION</command> commands are the same as
2221 for the version-0 equivalents.
2225 At first glance, the version-1 coding conventions might appear to
2226 be just pointless obscurantism. They do, however, offer a number
2227 of improvements, because the macros can hide unnecessary detail.
2228 An example is that in coding <function>add_one_float8</>, we no longer need to
2229 be aware that <type>float8</type> is a pass-by-reference type. Another
2230 example is that the <literal>GETARG</> macros for variable-length types allow
2231 for more efficient fetching of <quote>toasted</quote> (compressed or
2232 out-of-line) values.
2236 One big improvement in version-1 functions is better handling of null
2237 inputs and results. The macro <function>PG_ARGISNULL(<replaceable>n</>)</function>
2238 allows a function to test whether each input is null. (Of course, doing
2239 this is only necessary in functions not declared <quote>strict</>.)
2241 <function>PG_GETARG_<replaceable>xxx</replaceable>()</function> macros,
2242 the input arguments are counted beginning at zero. Note that one
2243 should refrain from executing
2244 <function>PG_GETARG_<replaceable>xxx</replaceable>()</function> until
2245 one has verified that the argument isn't null.
2246 To return a null result, execute <function>PG_RETURN_NULL()</function>;
2247 this works in both strict and nonstrict functions.
2251 Other options provided in the new-style interface are two
2253 <function>PG_GETARG_<replaceable>xxx</replaceable>()</function>
2254 macros. The first of these,
2255 <function>PG_GETARG_<replaceable>xxx</replaceable>_COPY()</function>,
2256 guarantees to return a copy of the specified argument that is
2257 safe for writing into. (The normal macros will sometimes return a
2258 pointer to a value that is physically stored in a table, which
2259 must not be written to. Using the
2260 <function>PG_GETARG_<replaceable>xxx</replaceable>_COPY()</function>
2261 macros guarantees a writable result.)
2262 The second variant consists of the
2263 <function>PG_GETARG_<replaceable>xxx</replaceable>_SLICE()</function>
2264 macros which take three arguments. The first is the number of the
2265 function argument (as above). The second and third are the offset and
2266 length of the segment to be returned. Offsets are counted from
2267 zero, and a negative length requests that the remainder of the
2268 value be returned. These macros provide more efficient access to
2269 parts of large values in the case where they have storage type
2270 <quote>external</quote>. (The storage type of a column can be specified using
2271 <literal>ALTER TABLE <replaceable>tablename</replaceable> ALTER
2272 COLUMN <replaceable>colname</replaceable> SET STORAGE
2273 <replaceable>storagetype</replaceable></literal>. <replaceable>storagetype</replaceable> is one of
2274 <literal>plain</>, <literal>external</>, <literal>extended</literal>,
2275 or <literal>main</>.)
2279 Finally, the version-1 function call conventions make it possible
2280 to return set results (<xref linkend="xfunc-c-return-set">) and
2281 implement trigger functions (<xref linkend="triggers">) and
2282 procedural-language call handlers (<xref
2283 linkend="plhandler">). Version-1 code is also more
2284 portable than version-0, because it does not break restrictions
2285 on function call protocol in the C standard. For more details
2286 see <filename>src/backend/utils/fmgr/README</filename> in the
2287 source distribution.
2292 <title>Writing Code</title>
2295 Before we turn to the more advanced topics, we should discuss
2296 some coding rules for <productname>PostgreSQL</productname>
2297 C-language functions. While it might be possible to load functions
2298 written in languages other than C into
2299 <productname>PostgreSQL</productname>, this is usually difficult
2300 (when it is possible at all) because other languages, such as
2301 C++, FORTRAN, or Pascal often do not follow the same calling
2302 convention as C. That is, other languages do not pass argument
2303 and return values between functions in the same way. For this
2304 reason, we will assume that your C-language functions are
2305 actually written in C.
2309 The basic rules for writing and building C functions are as follows:
2314 Use <literal>pg_config
2315 --includedir-server</literal><indexterm><primary>pg_config</><secondary>with user-defined C functions</></>
2316 to find out where the <productname>PostgreSQL</> server header
2317 files are installed on your system (or the system that your
2318 users will be running on).
2324 Compiling and linking your code so that it can be dynamically
2325 loaded into <productname>PostgreSQL</productname> always
2326 requires special flags. See <xref linkend="dfunc"> for a
2327 detailed explanation of how to do it for your particular
2334 Remember to define a <quote>magic block</> for your shared library,
2335 as described in <xref linkend="xfunc-c-dynload">.
2341 When allocating memory, use the
2342 <productname>PostgreSQL</productname> functions
2343 <function>palloc</function><indexterm><primary>palloc</></> and <function>pfree</function><indexterm><primary>pfree</></>
2344 instead of the corresponding C library functions
2345 <function>malloc</function> and <function>free</function>.
2346 The memory allocated by <function>palloc</function> will be
2347 freed automatically at the end of each transaction, preventing
2354 Always zero the bytes of your structures using
2355 <function>memset</function>. Without this, it's difficult to
2356 support hash indexes or hash joins, as you must pick out only
2357 the significant bits of your data structure to compute a hash.
2358 Even if you initialize all fields of your structure, there might be
2359 alignment padding (holes in the structure) that contain
2366 Most of the internal <productname>PostgreSQL</productname>
2367 types are declared in <filename>postgres.h</filename>, while
2368 the function manager interfaces
2369 (<symbol>PG_FUNCTION_ARGS</symbol>, etc.) are in
2370 <filename>fmgr.h</filename>, so you will need to include at
2371 least these two files. For portability reasons it's best to
2372 include <filename>postgres.h</filename> <emphasis>first</>,
2373 before any other system or user header files. Including
2374 <filename>postgres.h</filename> will also include
2375 <filename>elog.h</filename> and <filename>palloc.h</filename>
2382 Symbol names defined within object files must not conflict
2383 with each other or with symbols defined in the
2384 <productname>PostgreSQL</productname> server executable. You
2385 will have to rename your functions or variables if you get
2386 error messages to this effect.
2396 <title>Composite-type Arguments</title>
2399 Composite types do not have a fixed layout like C structures.
2400 Instances of a composite type can contain null fields. In
2401 addition, composite types that are part of an inheritance
2402 hierarchy can have different fields than other members of the
2403 same inheritance hierarchy. Therefore,
2404 <productname>PostgreSQL</productname> provides a function
2405 interface for accessing fields of composite types from C.
2409 Suppose we want to write a function to answer the query:
2412 SELECT name, c_overpaid(emp, 1500) AS overpaid
2414 WHERE name = 'Bill' OR name = 'Sam';
2417 Using call conventions version 0, we can define
2418 <function>c_overpaid</> as:
2420 <programlisting><![CDATA[
2421 #include "postgres.h"
2422 #include "executor/executor.h" /* for GetAttributeByName() */
2424 #ifdef PG_MODULE_MAGIC
2429 c_overpaid(HeapTupleHeader t, /* the current row of emp */
2435 salary = DatumGetInt32(GetAttributeByName(t, "salary", &isnull));
2438 return salary > limit;
2443 In version-1 coding, the above would look like this:
2445 <programlisting><![CDATA[
2446 #include "postgres.h"
2447 #include "executor/executor.h" /* for GetAttributeByName() */
2449 #ifdef PG_MODULE_MAGIC
2453 PG_FUNCTION_INFO_V1(c_overpaid);
2456 c_overpaid(PG_FUNCTION_ARGS)
2458 HeapTupleHeader t = PG_GETARG_HEAPTUPLEHEADER(0);
2459 int32 limit = PG_GETARG_INT32(1);
2463 salary = GetAttributeByName(t, "salary", &isnull);
2465 PG_RETURN_BOOL(false);
2466 /* Alternatively, we might prefer to do PG_RETURN_NULL() for null salary. */
2468 PG_RETURN_BOOL(DatumGetInt32(salary) > limit);
2475 <function>GetAttributeByName</function> is the
2476 <productname>PostgreSQL</productname> system function that
2477 returns attributes out of the specified row. It has
2478 three arguments: the argument of type <type>HeapTupleHeader</type> passed
2480 the function, the name of the desired attribute, and a
2481 return parameter that tells whether the attribute
2482 is null. <function>GetAttributeByName</function> returns a <type>Datum</type>
2483 value that you can convert to the proper data type by using the
2484 appropriate <function>DatumGet<replaceable>XXX</replaceable>()</function>
2485 macro. Note that the return value is meaningless if the null flag is
2486 set; always check the null flag before trying to do anything with the
2491 There is also <function>GetAttributeByNum</function>, which selects
2492 the target attribute by column number instead of name.
2496 The following command declares the function
2497 <function>c_overpaid</function> in SQL:
2500 CREATE FUNCTION c_overpaid(emp, integer) RETURNS boolean
2501 AS '<replaceable>DIRECTORY</replaceable>/funcs', 'c_overpaid'
2505 Notice we have used <literal>STRICT</> so that we did not have to
2506 check whether the input arguments were NULL.
2511 <title>Returning Rows (Composite Types)</title>
2514 To return a row or composite-type value from a C-language
2515 function, you can use a special API that provides macros and
2516 functions to hide most of the complexity of building composite
2517 data types. To use this API, the source file must include:
2519 #include "funcapi.h"
2524 There are two ways you can build a composite data value (henceforth
2525 a <quote>tuple</>): you can build it from an array of Datum values,
2526 or from an array of C strings that can be passed to the input
2527 conversion functions of the tuple's column data types. In either
2528 case, you first need to obtain or construct a <structname>TupleDesc</>
2529 descriptor for the tuple structure. When working with Datums, you
2530 pass the <structname>TupleDesc</> to <function>BlessTupleDesc</>,
2531 and then call <function>heap_form_tuple</> for each row. When working
2532 with C strings, you pass the <structname>TupleDesc</> to
2533 <function>TupleDescGetAttInMetadata</>, and then call
2534 <function>BuildTupleFromCStrings</> for each row. In the case of a
2535 function returning a set of tuples, the setup steps can all be done
2536 once during the first call of the function.
2540 Several helper functions are available for setting up the needed
2541 <structname>TupleDesc</>. The recommended way to do this in most
2542 functions returning composite values is to call:
2544 TypeFuncClass get_call_result_type(FunctionCallInfo fcinfo,
2546 TupleDesc *resultTupleDesc)
2548 passing the same <literal>fcinfo</> struct passed to the calling function
2549 itself. (This of course requires that you use the version-1
2550 calling conventions.) <varname>resultTypeId</> can be specified
2551 as <literal>NULL</> or as the address of a local variable to receive the
2552 function's result type OID. <varname>resultTupleDesc</> should be the
2553 address of a local <structname>TupleDesc</> variable. Check that the
2554 result is <literal>TYPEFUNC_COMPOSITE</>; if so,
2555 <varname>resultTupleDesc</> has been filled with the needed
2556 <structname>TupleDesc</>. (If it is not, you can report an error along
2557 the lines of <quote>function returning record called in context that
2558 cannot accept type record</quote>.)
2563 <function>get_call_result_type</> can resolve the actual type of a
2564 polymorphic function result; so it is useful in functions that return
2565 scalar polymorphic results, not only functions that return composites.
2566 The <varname>resultTypeId</> output is primarily useful for functions
2567 returning polymorphic scalars.
2573 <function>get_call_result_type</> has a sibling
2574 <function>get_expr_result_type</>, which can be used to resolve the
2575 expected output type for a function call represented by an expression
2576 tree. This can be used when trying to determine the result type from
2577 outside the function itself. There is also
2578 <function>get_func_result_type</>, which can be used when only the
2579 function's OID is available. However these functions are not able
2580 to deal with functions declared to return <structname>record</>, and
2581 <function>get_func_result_type</> cannot resolve polymorphic types,
2582 so you should preferentially use <function>get_call_result_type</>.
2587 Older, now-deprecated functions for obtaining
2588 <structname>TupleDesc</>s are:
2590 TupleDesc RelationNameGetTupleDesc(const char *relname)
2592 to get a <structname>TupleDesc</> for the row type of a named relation,
2595 TupleDesc TypeGetTupleDesc(Oid typeoid, List *colaliases)
2597 to get a <structname>TupleDesc</> based on a type OID. This can
2598 be used to get a <structname>TupleDesc</> for a base or
2599 composite type. It will not work for a function that returns
2600 <structname>record</>, however, and it cannot resolve polymorphic
2605 Once you have a <structname>TupleDesc</>, call:
2607 TupleDesc BlessTupleDesc(TupleDesc tupdesc)
2609 if you plan to work with Datums, or:
2611 AttInMetadata *TupleDescGetAttInMetadata(TupleDesc tupdesc)
2613 if you plan to work with C strings. If you are writing a function
2614 returning set, you can save the results of these functions in the
2615 <structname>FuncCallContext</> structure — use the
2616 <structfield>tuple_desc</> or <structfield>attinmeta</> field
2621 When working with Datums, use:
2623 HeapTuple heap_form_tuple(TupleDesc tupdesc, Datum *values, bool *isnull)
2625 to build a <structname>HeapTuple</> given user data in Datum form.
2629 When working with C strings, use:
2631 HeapTuple BuildTupleFromCStrings(AttInMetadata *attinmeta, char **values)
2633 to build a <structname>HeapTuple</> given user data
2634 in C string form. <literal>values</literal> is an array of C strings,
2635 one for each attribute of the return row. Each C string should be in
2636 the form expected by the input function of the attribute data
2637 type. In order to return a null value for one of the attributes,
2638 the corresponding pointer in the <parameter>values</> array
2639 should be set to <symbol>NULL</>. This function will need to
2640 be called again for each row you return.
2644 Once you have built a tuple to return from your function, it
2645 must be converted into a <type>Datum</>. Use:
2647 HeapTupleGetDatum(HeapTuple tuple)
2649 to convert a <structname>HeapTuple</> into a valid Datum. This
2650 <type>Datum</> can be returned directly if you intend to return
2651 just a single row, or it can be used as the current return value
2652 in a set-returning function.
2656 An example appears in the next section.
2661 <sect2 id="xfunc-c-return-set">
2662 <title>Returning Sets</title>
2665 There is also a special API that provides support for returning
2666 sets (multiple rows) from a C-language function. A set-returning
2667 function must follow the version-1 calling conventions. Also,
2668 source files must include <filename>funcapi.h</filename>, as
2673 A set-returning function (<acronym>SRF</>) is called
2674 once for each item it returns. The <acronym>SRF</> must
2675 therefore save enough state to remember what it was doing and
2676 return the next item on each call.
2677 The structure <structname>FuncCallContext</> is provided to help
2678 control this process. Within a function, <literal>fcinfo->flinfo->fn_extra</>
2679 is used to hold a pointer to <structname>FuncCallContext</>
2685 * Number of times we've been called before
2687 * call_cntr is initialized to 0 for you by SRF_FIRSTCALL_INIT(), and
2688 * incremented for you every time SRF_RETURN_NEXT() is called.
2693 * OPTIONAL maximum number of calls
2695 * max_calls is here for convenience only and setting it is optional.
2696 * If not set, you must provide alternative means to know when the
2702 * OPTIONAL pointer to result slot
2704 * This is obsolete and only present for backwards compatibility, viz,
2705 * user-defined SRFs that use the deprecated TupleDescGetSlot().
2707 TupleTableSlot *slot;
2710 * OPTIONAL pointer to miscellaneous user-provided context information
2712 * user_fctx is for use as a pointer to your own data to retain
2713 * arbitrary context information between calls of your function.
2718 * OPTIONAL pointer to struct containing attribute type input metadata
2720 * attinmeta is for use when returning tuples (i.e., composite data types)
2721 * and is not used when returning base data types. It is only needed
2722 * if you intend to use BuildTupleFromCStrings() to create the return
2725 AttInMetadata *attinmeta;
2728 * memory context used for structures that must live for multiple calls
2730 * multi_call_memory_ctx is set by SRF_FIRSTCALL_INIT() for you, and used
2731 * by SRF_RETURN_DONE() for cleanup. It is the most appropriate memory
2732 * context for any memory that is to be reused across multiple calls
2735 MemoryContext multi_call_memory_ctx;
2738 * OPTIONAL pointer to struct containing tuple description
2740 * tuple_desc is for use when returning tuples (i.e., composite data types)
2741 * and is only needed if you are going to build the tuples with
2742 * heap_form_tuple() rather than with BuildTupleFromCStrings(). Note that
2743 * the TupleDesc pointer stored here should usually have been run through
2744 * BlessTupleDesc() first.
2746 TupleDesc tuple_desc;
2753 An <acronym>SRF</> uses several functions and macros that
2754 automatically manipulate the <structname>FuncCallContext</>
2755 structure (and expect to find it via <literal>fn_extra</>). Use:
2759 to determine if your function is being called for the first or a
2760 subsequent time. On the first call (only) use:
2762 SRF_FIRSTCALL_INIT()
2764 to initialize the <structname>FuncCallContext</>. On every function call,
2765 including the first, use:
2769 to properly set up for using the <structname>FuncCallContext</>
2770 and clearing any previously returned data left over from the
2775 If your function has data to return, use:
2777 SRF_RETURN_NEXT(funcctx, result)
2779 to return it to the caller. (<literal>result</> must be of type
2780 <type>Datum</>, either a single value or a tuple prepared as
2781 described above.) Finally, when your function is finished
2782 returning data, use:
2784 SRF_RETURN_DONE(funcctx)
2786 to clean up and end the <acronym>SRF</>.
2790 The memory context that is current when the <acronym>SRF</> is called is
2791 a transient context that will be cleared between calls. This means
2792 that you do not need to call <function>pfree</> on everything
2793 you allocated using <function>palloc</>; it will go away anyway. However, if you want to allocate
2794 any data structures to live across calls, you need to put them somewhere
2795 else. The memory context referenced by
2796 <structfield>multi_call_memory_ctx</> is a suitable location for any
2797 data that needs to survive until the <acronym>SRF</> is finished running. In most
2798 cases, this means that you should switch into
2799 <structfield>multi_call_memory_ctx</> while doing the first-call setup.
2803 A complete pseudo-code example looks like the following:
2806 my_set_returning_function(PG_FUNCTION_ARGS)
2808 FuncCallContext *funcctx;
2810 <replaceable>further declarations as needed</replaceable>
2812 if (SRF_IS_FIRSTCALL())
2814 MemoryContext oldcontext;
2816 funcctx = SRF_FIRSTCALL_INIT();
2817 oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
2818 /* One-time setup code appears here: */
2819 <replaceable>user code</replaceable>
2820 <replaceable>if returning composite</replaceable>
2821 <replaceable>build TupleDesc, and perhaps AttInMetadata</replaceable>
2822 <replaceable>endif returning composite</replaceable>
2823 <replaceable>user code</replaceable>
2824 MemoryContextSwitchTo(oldcontext);
2827 /* Each-time setup code appears here: */
2828 <replaceable>user code</replaceable>
2829 funcctx = SRF_PERCALL_SETUP();
2830 <replaceable>user code</replaceable>
2832 /* this is just one way we might test whether we are done: */
2833 if (funcctx->call_cntr < funcctx->max_calls)
2835 /* Here we want to return another item: */
2836 <replaceable>user code</replaceable>
2837 <replaceable>obtain result Datum</replaceable>
2838 SRF_RETURN_NEXT(funcctx, result);
2842 /* Here we are done returning items and just need to clean up: */
2843 <replaceable>user code</replaceable>
2844 SRF_RETURN_DONE(funcctx);
2851 A complete example of a simple <acronym>SRF</> returning a composite type
2853 <programlisting><![CDATA[
2854 PG_FUNCTION_INFO_V1(retcomposite);
2857 retcomposite(PG_FUNCTION_ARGS)
2859 FuncCallContext *funcctx;
2863 AttInMetadata *attinmeta;
2865 /* stuff done only on the first call of the function */
2866 if (SRF_IS_FIRSTCALL())
2868 MemoryContext oldcontext;
2870 /* create a function context for cross-call persistence */
2871 funcctx = SRF_FIRSTCALL_INIT();
2873 /* switch to memory context appropriate for multiple function calls */
2874 oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
2876 /* total number of tuples to be returned */
2877 funcctx->max_calls = PG_GETARG_UINT32(0);
2879 /* Build a tuple descriptor for our result type */
2880 if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
2882 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
2883 errmsg("function returning record called in context "
2884 "that cannot accept type record")));
2887 * generate attribute metadata needed later to produce tuples from raw
2890 attinmeta = TupleDescGetAttInMetadata(tupdesc);
2891 funcctx->attinmeta = attinmeta;
2893 MemoryContextSwitchTo(oldcontext);
2896 /* stuff done on every call of the function */
2897 funcctx = SRF_PERCALL_SETUP();
2899 call_cntr = funcctx->call_cntr;
2900 max_calls = funcctx->max_calls;
2901 attinmeta = funcctx->attinmeta;
2903 if (call_cntr < max_calls) /* do when there is more left to send */
2910 * Prepare a values array for building the returned tuple.
2911 * This should be an array of C strings which will
2912 * be processed later by the type input functions.
2914 values = (char **) palloc(3 * sizeof(char *));
2915 values[0] = (char *) palloc(16 * sizeof(char));
2916 values[1] = (char *) palloc(16 * sizeof(char));
2917 values[2] = (char *) palloc(16 * sizeof(char));
2919 snprintf(values[0], 16, "%d", 1 * PG_GETARG_INT32(1));
2920 snprintf(values[1], 16, "%d", 2 * PG_GETARG_INT32(1));
2921 snprintf(values[2], 16, "%d", 3 * PG_GETARG_INT32(1));
2924 tuple = BuildTupleFromCStrings(attinmeta, values);
2926 /* make the tuple into a datum */
2927 result = HeapTupleGetDatum(tuple);
2929 /* clean up (this is not really necessary) */
2935 SRF_RETURN_NEXT(funcctx, result);
2937 else /* do when there is no more left */
2939 SRF_RETURN_DONE(funcctx);
2945 One way to declare this function in SQL is:
2947 CREATE TYPE __retcomposite AS (f1 integer, f2 integer, f3 integer);
2949 CREATE OR REPLACE FUNCTION retcomposite(integer, integer)
2950 RETURNS SETOF __retcomposite
2951 AS '<replaceable>filename</>', 'retcomposite'
2952 LANGUAGE C IMMUTABLE STRICT;
2954 A different way is to use OUT parameters:
2956 CREATE OR REPLACE FUNCTION retcomposite(IN integer, IN integer,
2957 OUT f1 integer, OUT f2 integer, OUT f3 integer)
2958 RETURNS SETOF record
2959 AS '<replaceable>filename</>', 'retcomposite'
2960 LANGUAGE C IMMUTABLE STRICT;
2962 Notice that in this method the output type of the function is formally
2963 an anonymous <structname>record</> type.
2967 The directory <link linkend="tablefunc">contrib/tablefunc</>
2968 module in the source distribution contains more examples of
2969 set-returning functions.
2974 <title>Polymorphic Arguments and Return Types</title>
2977 C-language functions can be declared to accept and
2978 return the polymorphic types
2979 <type>anyelement</type>, <type>anyarray</type>, <type>anynonarray</type>,
2980 and <type>anyenum</type>.
2981 See <xref linkend="extend-types-polymorphic"> for a more detailed explanation
2982 of polymorphic functions. When function arguments or return types
2983 are defined as polymorphic types, the function author cannot know
2984 in advance what data type it will be called with, or
2985 need to return. There are two routines provided in <filename>fmgr.h</>
2986 to allow a version-1 C function to discover the actual data types
2987 of its arguments and the type it is expected to return. The routines are
2988 called <literal>get_fn_expr_rettype(FmgrInfo *flinfo)</> and
2989 <literal>get_fn_expr_argtype(FmgrInfo *flinfo, int argnum)</>.
2990 They return the result or argument type OID, or <symbol>InvalidOid</symbol> if the
2991 information is not available.
2992 The structure <literal>flinfo</> is normally accessed as
2993 <literal>fcinfo->flinfo</>. The parameter <literal>argnum</>
2994 is zero based. <function>get_call_result_type</> can also be used
2995 as an alternative to <function>get_fn_expr_rettype</>.
2999 For example, suppose we want to write a function to accept a single
3000 element of any type, and return a one-dimensional array of that type:
3003 PG_FUNCTION_INFO_V1(make_array);
3005 make_array(PG_FUNCTION_ARGS)
3008 Oid element_type = get_fn_expr_argtype(fcinfo->flinfo, 0);
3018 if (!OidIsValid(element_type))
3019 elog(ERROR, "could not determine data type of input");
3021 /* get the provided element, being careful in case it's NULL */
3022 isnull = PG_ARGISNULL(0);
3024 element = (Datum) 0;
3026 element = PG_GETARG_DATUM(0);
3028 /* we have one dimension */
3030 /* and one element */
3032 /* and lower bound is 1 */
3035 /* get required info about the element type */
3036 get_typlenbyvalalign(element_type, &typlen, &typbyval, &typalign);
3038 /* now build the array */
3039 result = construct_md_array(&element, &isnull, ndims, dims, lbs,
3040 element_type, typlen, typbyval, typalign);
3042 PG_RETURN_ARRAYTYPE_P(result);
3048 The following command declares the function
3049 <function>make_array</function> in SQL:
3052 CREATE FUNCTION make_array(anyelement) RETURNS anyarray
3053 AS '<replaceable>DIRECTORY</replaceable>/funcs', 'make_array'
3054 LANGUAGE C IMMUTABLE;
3059 There is a variant of polymorphism that is only available to C-language
3060 functions: they can be declared to take parameters of type
3061 <literal>"any"</>. (Note that this type name must be double-quoted,
3062 since it's also a SQL reserved word.) This works like
3063 <type>anyelement</> except that it does not constrain different
3064 <literal>"any"</> arguments to be the same type, nor do they help
3065 determine the function's result type. A C-language function can also
3066 declare its final parameter to be <literal>VARIADIC "any"</>. This will
3067 match one or more actual arguments of any type (not necessarily the same
3068 type). These arguments will <emphasis>not</> be gathered into an array
3069 as happens with normal variadic functions; they will just be passed to
3070 the function separately. The <function>PG_NARGS()</> macro and the
3071 methods described above must be used to determine the number of actual
3072 arguments and their types when using this feature.
3077 <title>Shared Memory and LWLocks</title>
3080 Add-ins can reserve LWLocks and an allocation of shared memory on server
3081 startup. The add-in's shared library must be preloaded by specifying
3083 <xref linkend="guc-shared-preload-libraries"><indexterm><primary>shared_preload_libraries</></>.
3084 Shared memory is reserved by calling:
3086 void RequestAddinShmemSpace(int size)
3088 from your <function>_PG_init</> function.
3091 LWLocks are reserved by calling:
3093 void RequestAddinLWLocks(int n)
3095 from <function>_PG_init</>.
3098 To avoid possible race-conditions, each backend should use the LWLock
3099 <function>AddinShmemInitLock</> when connecting to and initializing
3100 its allocation of shared memory, as shown here:
3102 static mystruct *ptr = NULL;
3108 LWLockAcquire(AddinShmemInitLock, LW_EXCLUSIVE);
3109 ptr = ShmemInitStruct("my struct name", size, &found);
3112 initialize contents of shmem area;
3113 acquire any requested LWLocks using:
3114 ptr->mylockid = LWLockAssign();
3116 LWLockRelease(AddinShmemInitLock);
3122 <sect2 id="extend-Cpp">
3123 <title>Using C++ for Extensibility</title>
3125 <indexterm zone="extend-Cpp">
3126 <primary>C++</primary>
3130 Although the <productname>PostgreSQL</productname> backend is written in
3131 C, it is possible to write extensions in C++ if these guidelines are
3137 All functions accessed by the backend must present a C interface
3138 to the backend; these C functions can then call C++ functions.
3139 For example, <literal>extern C</> linkage is required for
3140 backend-accessed functions. This is also necessary for any
3141 functions that are passed as pointers between the backend and
3147 Free memory using the appropriate deallocation method. For example,
3148 most backend memory is allocated using <function>palloc()</>, so use
3149 <function>pfree()</> to free it. Using C++
3150 <function>delete</> in such cases will fail.
3155 Prevent exceptions from propagating into the C code (use a catch-all
3156 block at the top level of all <literal>extern C</> functions). This
3157 is necessary even if the C++ code does not explicitly throw any
3158 exceptions, because events like out-of-memory can still throw
3159 exceptions. Any exceptions must be caught and appropriate errors
3160 passed back to the C interface. If possible, compile C++ with
3161 <option>-fno-exceptions</> to eliminate exceptions entirely; in such
3162 cases, you must check for failures in your C++ code, e.g. check for
3163 NULL returned by <function>new()</>.
3168 If calling backend functions from C++ code, be sure that the
3169 C++ call stack contains only plain old data structures
3170 (<acronym>POD</>). This is necessary because backend errors
3171 generate a distant <function>longjmp()</> that does not properly
3172 unroll a C++ call stack with non-POD objects.
3179 In summary, it is best to place C++ code behind a wall of
3180 <literal>extern C</> functions that interface to the backend,
3181 and avoid exception, memory, and call stack leakage.
projects / postgresql / blob