2 $PostgreSQL: pgsql/doc/src/sgml/xfunc.sgml,v 1.104 2005/09/14 21:14:26 neilc Exp $
6 <title>User-Defined Functions</title>
8 <indexterm zone="xfunc">
9 <primary>function</primary>
10 <secondary>user-defined</secondary>
14 <productname>PostgreSQL</productname> provides four kinds of
20 query language functions (functions written in
21 <acronym>SQL</acronym>) (<xref linkend="xfunc-sql">)
26 procedural language functions (functions written in, for
27 example, <application>PL/pgSQL</> or <application>PL/Tcl</>)
28 (<xref linkend="xfunc-pl">)
33 internal functions (<xref linkend="xfunc-internal">)
38 C-language functions (<xref linkend="xfunc-c">)
46 of function can take base types, composite types, or
47 combinations of these as arguments (parameters). In addition,
48 every kind of function can return a base type or
49 a composite type. Functions may also be defined to return
50 sets of base or composite values.
54 Many kinds of functions can take or return certain pseudo-types
55 (such as polymorphic types), but the available facilities vary.
56 Consult the description of each kind of function for more details.
60 It's easiest to define <acronym>SQL</acronym>
61 functions, so we'll start by discussing those.
62 Most of the concepts presented for <acronym>SQL</acronym> functions
63 will carry over to the other types of functions.
67 Throughout this chapter, it can be useful to look at the reference
68 page of the <xref linkend="sql-createfunction"
69 endterm="sql-createfunction-title"> command to
70 understand the examples better. Some examples from this chapter
71 can be found in <filename>funcs.sql</filename> and
72 <filename>funcs.c</filename> in the <filename>src/tutorial</>
73 directory in the <productname>PostgreSQL</productname> source
78 <sect1 id="xfunc-sql">
79 <title>Query Language (<acronym>SQL</acronym>) Functions</title>
81 <indexterm zone="xfunc-sql">
82 <primary>function</primary>
83 <secondary>user-defined</secondary>
84 <tertiary>in SQL</tertiary>
88 SQL functions execute an arbitrary list of SQL statements, returning
89 the result of the last query in the list.
90 In the simple (non-set)
91 case, the first row of the last query's result will be returned.
92 (Bear in mind that <quote>the first row</quote> of a multirow
93 result is not well-defined unless you use <literal>ORDER BY</>.)
94 If the last query happens
95 to return no rows at all, the null value will be returned.
99 <indexterm><primary>SETOF</><seealso>function</></> Alternatively,
100 an SQL function may be declared to return a set, by specifying the
101 function's return type as <literal>SETOF
102 <replaceable>sometype</></literal>. In this case all rows of the
103 last query's result are returned. Further details appear below.
107 The body of an SQL function must be a list of SQL
108 statements separated by semicolons. A semicolon after the last
109 statement is optional. Unless the function is declared to return
110 <type>void</>, the last statement must be a <command>SELECT</>.
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. (The only exception is that you can't put
120 <command>BEGIN</>, <command>COMMIT</>, <command>ROLLBACK</>, or
121 <command>SAVEPOINT</> commands into a <acronym>SQL</acronym> function.)
122 However, the final command
123 must be a <command>SELECT</command> that returns whatever is
124 specified as the function's return type. Alternatively, if you
125 want to define a SQL function that performs actions but has no
126 useful value to return, you can define it as returning <type>void</>.
127 In that case, the function body must not end with a <command>SELECT</command>.
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 escape single quote marks (<literal>'</>) and backslashes
153 (<literal>\</>) used in the body of the function, typically by
154 doubling them (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>, may 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.
265 <title><acronym>SQL</acronym> Functions on Composite Types</title>
268 When writing functions with arguments of composite
269 types, we must not only specify which
270 argument we want (as we did above with <literal>$1</> and <literal>$2</literal>) but
271 also the desired attribute (field) of that argument. For example,
273 <type>emp</type> is a table containing employee data, and therefore
274 also the name of the composite type of each row of the table. Here
275 is a function <function>double_salary</function> that computes what someone's
276 salary would be if it were doubled:
286 CREATE FUNCTION double_salary(emp) RETURNS numeric AS $$
287 SELECT $1.salary * 2 AS salary;
290 SELECT name, double_salary(emp.*) AS dream
292 WHERE emp.cubicle ~= point '(2,1)';
301 Notice the use of the syntax <literal>$1.salary</literal>
302 to select one field of the argument row value. Also notice
303 how the calling <command>SELECT</> command uses <literal>*</>
305 the entire current row of a table as a composite value. The table
306 row can alternatively be referenced using just the table name,
309 SELECT name, double_salary(emp) AS dream
311 WHERE emp.cubicle ~= point '(2,1)';
313 but this usage is deprecated since it's easy to get confused.
317 Sometimes it is handy to construct a composite argument value
318 on-the-fly. This can be done with the <literal>ROW</> construct.
319 For example, we could adjust the data being passed to the function:
321 SELECT name, double_salary(ROW(name, salary*1.1, age, cubicle)) AS dream
327 It is also possible to build a function that returns a composite type.
328 This is an example of a function
329 that returns a single <type>emp</type> row:
332 CREATE FUNCTION new_emp() RETURNS emp AS $$
333 SELECT text 'None' AS name,
336 point '(2,2)' AS cubicle;
340 In this example we have specified each of the attributes
341 with a constant value, but any computation
342 could have been substituted for these constants.
346 Note two important things about defining the function:
351 The select list order in the query must be exactly the same as
352 that in which the columns appear in the table associated
353 with the composite type. (Naming the columns, as we did above,
354 is irrelevant to the system.)
359 You must typecast the expressions to match the
360 definition of the composite type, or you will get errors like this:
363 ERROR: function declared to return emp returns varchar instead of text at column 1
372 A different way to define the same function is:
375 CREATE FUNCTION new_emp() RETURNS emp AS $$
376 SELECT ROW('None', 1000.0, 25, '(2,2)')::emp;
380 Here we wrote a <command>SELECT</> that returns just a single
381 column of the correct composite type. This isn't really better
382 in this situation, but it is a handy alternative in some cases
383 — for example, if we need to compute the result by calling
384 another function that returns the desired composite value.
388 We could call this function directly in either of two ways:
394 --------------------------
395 (None,1000.0,25,"(2,2)")
397 SELECT * FROM new_emp();
399 name | salary | age | cubicle
400 ------+--------+-----+---------
401 None | 1000.0 | 25 | (2,2)
404 The second way is described more fully in <xref
405 linkend="xfunc-sql-table-functions">.
409 When you use a function that returns a composite type,
410 you might want only one field (attribute) from its result.
411 You can do that with syntax like this:
414 SELECT (new_emp()).name;
421 The extra parentheses are needed to keep the parser from getting
422 confused. If you try to do it without them, you get something like this:
425 SELECT new_emp().name;
426 ERROR: syntax error at or near "." at character 17
427 LINE 1: SELECT new_emp().name;
433 Another option is to use
434 functional notation for extracting an attribute. The simple way
435 to explain this is that we can use the
436 notations <literal>attribute(table)</> and <literal>table.attribute</>
440 SELECT name(new_emp());
448 -- This is the same as:
449 -- SELECT emp.name AS youngster FROM emp WHERE emp.age < 30;
451 SELECT name(emp) AS youngster FROM emp WHERE age(emp) < 30;
462 The equivalence between functional notation and attribute notation
463 makes it possible to use functions on composite types to emulate
464 <quote>computed fields</>.
466 <primary>computed field</primary>
469 <primary>field</primary>
470 <secondary>computed</secondary>
472 For example, using the previous definition
473 for <literal>double_salary(emp)</>, we can write
476 SELECT emp.name, emp.double_salary FROM emp;
479 An application using this wouldn't need to be directly aware that
480 <literal>double_salary</> isn't a real column of the table.
481 (You can also emulate computed fields with views.)
486 Another way to use a function returning a composite type is to pass the
487 result to another function that accepts the correct row type as input:
490 CREATE FUNCTION getname(emp) RETURNS text AS $$
494 SELECT getname(new_emp());
503 Still another way to use a function that returns a composite type is to
504 call it as a table function, as described in <xref
505 linkend="xfunc-sql-table-functions">.
509 <sect2 id="xfunc-output-parameters">
510 <title>Functions with Output Parameters</title>
513 <primary>function</primary>
514 <secondary>output parameter</secondary>
518 An alternative way of describing a function's results is to define it
519 with <firstterm>output parameters</>, as in this example:
522 CREATE FUNCTION add_em (IN x int, IN y int, OUT sum int)
533 This is not essentially different from the version of <literal>add_em</>
534 shown in <xref linkend="xfunc-sql-base-functions">. The real value of
535 output parameters is that they provide a convenient way of defining
536 functions that return several columns. For example,
539 CREATE FUNCTION sum_n_product (x int, y int, OUT sum int, OUT product int)
540 AS 'SELECT $1 + $2, $1 * $2'
543 SELECT * FROM sum_n_product(11,42);
550 What has essentially happened here is that we have created an anonymous
551 composite type for the result of the function. The above example has
552 the same end result as
555 CREATE TYPE sum_prod AS (sum int, product int);
557 CREATE FUNCTION sum_n_product (int, int) RETURNS sum_prod
558 AS 'SELECT $1 + $2, $1 * $2'
562 but not having to bother with the separate composite type definition
567 Notice that output parameters are not included in the calling argument
568 list when invoking such a function from SQL. This is because
569 <productname>PostgreSQL</productname> considers only the input
570 parameters to define the function's calling signature. That means
571 also that only the input parameters matter when referencing the function
572 for purposes such as dropping it. We could drop the above function
576 DROP FUNCTION sum_n_product (x int, y int, OUT sum int, OUT product int);
577 DROP FUNCTION sum_n_product (int, int);
582 Parameters can be marked as <literal>IN</> (the default),
583 <literal>OUT</>, or <literal>INOUT</>. An <literal>INOUT</>
584 parameter serves as both an input parameter (part of the calling
585 argument list) and an output parameter (part of the result record type).
589 <sect2 id="xfunc-sql-table-functions">
590 <title><acronym>SQL</acronym> Functions as Table Sources</title>
593 All SQL functions may be used in the <literal>FROM</> clause of a query,
594 but it is particularly useful for functions returning composite types.
595 If the function is defined to return a base type, the table function
596 produces a one-column table. If the function is defined to return
597 a composite type, the table function produces a column for each attribute
598 of the composite type.
605 CREATE TABLE foo (fooid int, foosubid int, fooname text);
606 INSERT INTO foo VALUES (1, 1, 'Joe');
607 INSERT INTO foo VALUES (1, 2, 'Ed');
608 INSERT INTO foo VALUES (2, 1, 'Mary');
610 CREATE FUNCTION getfoo(int) RETURNS foo AS $$
611 SELECT * FROM foo WHERE fooid = $1;
614 SELECT *, upper(fooname) FROM getfoo(1) AS t1;
616 fooid | foosubid | fooname | upper
617 -------+----------+---------+-------
622 As the example shows, we can work with the columns of the function's
623 result just the same as if they were columns of a regular table.
627 Note that we only got one row out of the function. This is because
628 we did not use <literal>SETOF</>. That is described in the next section.
633 <title><acronym>SQL</acronym> Functions Returning Sets</title>
636 When an SQL function is declared as returning <literal>SETOF
637 <replaceable>sometype</></literal>, the function's final
638 <command>SELECT</> query is executed to completion, and each row it
639 outputs is returned as an element of the result set.
643 This feature is normally used when calling the function in the <literal>FROM</>
644 clause. In this case each row returned by the function becomes
645 a row of the table seen by the query. For example, assume that
646 table <literal>foo</> has the same contents as above, and we say:
649 CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
650 SELECT * FROM foo WHERE fooid = $1;
653 SELECT * FROM getfoo(1) AS t1;
658 fooid | foosubid | fooname
659 -------+----------+---------
667 Currently, functions returning sets may also be called in the select list
668 of a query. For each row that the query
669 generates by itself, the function returning set is invoked, and an output
670 row is generated for each element of the function's result set. Note,
671 however, that this capability is deprecated and may be removed in future
672 releases. The following is an example function returning a set from the
676 CREATE FUNCTION listchildren(text) RETURNS SETOF text AS $$
677 SELECT name FROM nodes WHERE parent = $1
691 SELECT listchildren('Top');
699 SELECT name, listchildren(name) FROM nodes;
701 --------+--------------
710 In the last <command>SELECT</command>,
711 notice that no output row appears for <literal>Child2</>, <literal>Child3</>, etc.
712 This happens because <function>listchildren</function> returns an empty set
713 for those arguments, so no result rows are generated.
718 <title>Polymorphic <acronym>SQL</acronym> Functions</title>
721 <acronym>SQL</acronym> functions may be declared to accept and
722 return the polymorphic types <type>anyelement</type> and
723 <type>anyarray</type>. See <xref
724 linkend="extend-types-polymorphic"> for a more detailed
725 explanation of polymorphic functions. Here is a polymorphic
726 function <function>make_array</function> that builds up an array
727 from two arbitrary data type elements:
729 CREATE FUNCTION make_array(anyelement, anyelement) RETURNS anyarray AS $$
730 SELECT ARRAY[$1, $2];
733 SELECT make_array(1, 2) AS intarray, make_array('a'::text, 'b') AS textarray;
735 ----------+-----------
742 Notice the use of the typecast <literal>'a'::text</literal>
743 to specify that the argument is of type <type>text</type>. This is
744 required if the argument is just a string literal, since otherwise
745 it would be treated as type
746 <type>unknown</type>, and array of <type>unknown</type> is not a valid
748 Without the typecast, you will get errors like this:
751 ERROR: could not determine "anyarray"/"anyelement" type because input has type "unknown"
757 It is permitted to have polymorphic arguments with a fixed
758 return type, but the converse is not. For example:
760 CREATE FUNCTION is_greater(anyelement, anyelement) RETURNS boolean AS $$
764 SELECT is_greater(1, 2);
770 CREATE FUNCTION invalid_func() RETURNS anyelement AS $$
773 ERROR: cannot determine result data type
774 DETAIL: A function returning "anyarray" or "anyelement" must have at least one argument of either type.
779 Polymorphism can be used with functions that have output arguments.
782 CREATE FUNCTION dup (f1 anyelement, OUT f2 anyelement, OUT f3 anyarray)
783 AS 'select $1, array[$1,$1]' LANGUAGE sql;
785 SELECT * FROM dup(22);
795 <sect1 id="xfunc-overload">
796 <title>Function Overloading</title>
798 <indexterm zone="xfunc-overload">
799 <primary>overloading</primary>
800 <secondary>functions</secondary>
804 More than one function may be defined with the same SQL name, so long
805 as the arguments they take are different. In other words,
806 function names can be <firstterm>overloaded</firstterm>. When a
807 query is executed, the server will determine which function to
808 call from the data types and the number of the provided arguments.
809 Overloading can also be used to simulate functions with a variable
810 number of arguments, up to a finite maximum number.
814 When creating a family of overloaded functions, one should be
815 careful not to create ambiguities. For instance, given the
818 CREATE FUNCTION test(int, real) RETURNS ...
819 CREATE FUNCTION test(smallint, double precision) RETURNS ...
821 it is not immediately clear which function would be called with
822 some trivial input like <literal>test(1, 1.5)</literal>. The
823 currently implemented resolution rules are described in
824 <xref linkend="typeconv">, but it is unwise to design a system that subtly
825 relies on this behavior.
829 A function that takes a single argument of a composite type should
830 generally not have the same name as any attribute (field) of that type.
831 Recall that <literal>attribute(table)</literal> is considered equivalent
832 to <literal>table.attribute</literal>. In the case that there is an
833 ambiguity between a function on a composite type and an attribute of
834 the composite type, the attribute will always be used. It is possible
835 to override that choice by schema-qualifying the function name
836 (that is, <literal>schema.func(table)</literal>) but it's better to
837 avoid the problem by not choosing conflicting names.
841 When overloading C-language functions, there is an additional
842 constraint: The C name of each function in the family of
843 overloaded functions must be different from the C names of all
844 other functions, either internal or dynamically loaded. If this
845 rule is violated, the behavior is not portable. You might get a
846 run-time linker error, or one of the functions will get called
847 (usually the internal one). The alternative form of the
848 <literal>AS</> clause for the SQL <command>CREATE
849 FUNCTION</command> command decouples the SQL function name from
850 the function name in the C source code. For instance,
852 CREATE FUNCTION test(int) RETURNS int
853 AS '<replaceable>filename</>', 'test_1arg'
855 CREATE FUNCTION test(int, int) RETURNS int
856 AS '<replaceable>filename</>', 'test_2arg'
859 The names of the C functions here reflect one of many possible conventions.
863 <sect1 id="xfunc-volatility">
864 <title>Function Volatility Categories</title>
866 <indexterm zone="xfunc-volatility">
867 <primary>volatility</primary>
868 <secondary>functions</secondary>
870 <indexterm zone="xfunc-volatility">
871 <primary>VOLATILE</primary>
873 <indexterm zone="xfunc-volatility">
874 <primary>STABLE</primary>
876 <indexterm zone="xfunc-volatility">
877 <primary>IMMUTABLE</primary>
881 Every function has a <firstterm>volatility</> classification, with
882 the possibilities being <literal>VOLATILE</>, <literal>STABLE</>, or
883 <literal>IMMUTABLE</>. <literal>VOLATILE</> is the default if the
884 <command>CREATE FUNCTION</command> command does not specify a category.
885 The volatility category is a promise to the optimizer about the behavior
891 A <literal>VOLATILE</> function can do anything, including modifying
892 the database. It can return different results on successive calls with
893 the same arguments. The optimizer makes no assumptions about the
894 behavior of such functions. A query using a volatile function will
895 re-evaluate the function at every row where its value is needed.
900 A <literal>STABLE</> function cannot modify the database and is
901 guaranteed to return the same results given the same arguments
902 for all calls within a single surrounding query. This category
903 allows the optimizer to optimize away multiple calls of the function
904 within a single query. In particular, it is safe to use an expression
905 containing such a function in an index scan condition. (Since an
906 index scan will evaluate the comparison value only once, not once at
907 each row, it is not valid to use a <literal>VOLATILE</> function in
908 an index scan condition.)
913 An <literal>IMMUTABLE</> function cannot modify the database and is
914 guaranteed to return the same results given the same arguments forever.
915 This category allows the optimizer to pre-evaluate the function when
916 a query calls it with constant arguments. For example, a query like
917 <literal>SELECT ... WHERE x = 2 + 2</> can be simplified on sight to
918 <literal>SELECT ... WHERE x = 4</>, because the function underlying
919 the integer addition operator is marked <literal>IMMUTABLE</>.
926 For best optimization results, you should label your functions with the
927 strictest volatility category that is valid for them.
931 Any function with side-effects <emphasis>must</> be labeled
932 <literal>VOLATILE</>, so that calls to it cannot be optimized away.
933 Even a function with no side-effects needs to be labeled
934 <literal>VOLATILE</> if its value can change within a single query;
935 some examples are <literal>random()</>, <literal>currval()</>,
936 <literal>timeofday()</>.
940 There is relatively little difference between <literal>STABLE</> and
941 <literal>IMMUTABLE</> categories when considering simple interactive
942 queries that are planned and immediately executed: it doesn't matter
943 a lot whether a function is executed once during planning or once during
944 query execution startup. But there is a big difference if the plan is
945 saved and reused later. Labeling a function <literal>IMMUTABLE</> when
946 it really isn't may allow it to be prematurely folded to a constant during
947 planning, resulting in a stale value being re-used during subsequent uses
948 of the plan. This is a hazard when using prepared statements or when
949 using function languages that cache plans (such as
950 <application>PL/pgSQL</>).
954 Because of the snapshotting behavior of MVCC (see <xref linkend="mvcc">)
955 a function containing only <command>SELECT</> commands can safely be
956 marked <literal>STABLE</>, even if it selects from tables that might be
957 undergoing modifications by concurrent queries.
958 <productname>PostgreSQL</productname> will execute a <literal>STABLE</>
959 function using the snapshot established for the calling query, and so it
960 will see a fixed view of the database throughout that query.
962 that the <function>current_timestamp</> family of functions qualify
963 as stable, since their values do not change within a transaction.
967 The same snapshotting behavior is used for <command>SELECT</> commands
968 within <literal>IMMUTABLE</> functions. It is generally unwise to select
969 from database tables within an <literal>IMMUTABLE</> function at all,
970 since the immutability will be broken if the table contents ever change.
971 However, <productname>PostgreSQL</productname> does not enforce that you
976 A common error is to label a function <literal>IMMUTABLE</> when its
977 results depend on a configuration parameter. For example, a function
978 that manipulates timestamps might well have results that depend on the
979 <xref linkend="guc-timezone"> setting. For safety, such functions should
980 be labeled <literal>STABLE</> instead.
985 Before <productname>PostgreSQL</productname> release 8.0, the requirement
986 that <literal>STABLE</> and <literal>IMMUTABLE</> functions cannot modify
987 the database was not enforced by the system. Release 8.0 enforces it
988 by requiring SQL functions and procedural language functions of these
989 categories to contain no SQL commands other than <command>SELECT</>.
990 (This is not a completely bulletproof test, since such functions could
991 still call <literal>VOLATILE</> functions that modify the database.
992 If you do that, you will find that the <literal>STABLE</> or
993 <literal>IMMUTABLE</> function does not notice the database changes
994 applied by the called function.)
999 <sect1 id="xfunc-pl">
1000 <title>Procedural Language Functions</title>
1003 <productname>PostgreSQL</productname> allows user-defined functions
1004 to be written in other languages besides SQL and C. These other
1005 languages are generically called <firstterm>procedural
1006 languages</firstterm> (<acronym>PL</>s).
1007 Procedural languages aren't built into the
1008 <productname>PostgreSQL</productname> server; they are offered
1009 by loadable modules.
1010 See <xref linkend="xplang"> and following chapters for more
1015 <sect1 id="xfunc-internal">
1016 <title>Internal Functions</title>
1018 <indexterm zone="xfunc-internal"><primary>function</><secondary>internal</></>
1021 Internal functions are functions written in C that have been statically
1022 linked into the <productname>PostgreSQL</productname> server.
1023 The <quote>body</quote> of the function definition
1024 specifies the C-language name of the function, which need not be the
1025 same as the name being declared for SQL use.
1026 (For reasons of backwards compatibility, an empty body
1027 is accepted as meaning that the C-language function name is the
1028 same as the SQL name.)
1032 Normally, all internal functions present in the
1033 server are declared during the initialization of the database cluster (<command>initdb</command>),
1034 but a user could use <command>CREATE FUNCTION</command>
1035 to create additional alias names for an internal function.
1036 Internal functions are declared in <command>CREATE FUNCTION</command>
1037 with language name <literal>internal</literal>. For instance, to
1038 create an alias for the <function>sqrt</function> function:
1040 CREATE FUNCTION square_root(double precision) RETURNS double precision
1045 (Most internal functions expect to be declared <quote>strict</quote>.)
1050 Not all <quote>predefined</quote> functions are
1051 <quote>internal</quote> in the above sense. Some predefined
1052 functions are written in SQL.
1057 <sect1 id="xfunc-c">
1058 <title>C-Language Functions</title>
1060 <indexterm zone="xfunc-c">
1061 <primary>function</primary>
1062 <secondary>user-defined</secondary>
1063 <tertiary>in C</tertiary>
1067 User-defined functions can be written in C (or a language that can
1068 be made compatible with C, such as C++). Such functions are
1069 compiled into dynamically loadable objects (also called shared
1070 libraries) and are loaded by the server on demand. The dynamic
1071 loading feature is what distinguishes <quote>C language</> functions
1072 from <quote>internal</> functions — the actual coding conventions
1073 are essentially the same for both. (Hence, the standard internal
1074 function library is a rich source of coding examples for user-defined
1079 Two different calling conventions are currently used for C functions.
1080 The newer <quote>version 1</quote> calling convention is indicated by writing
1081 a <literal>PG_FUNCTION_INFO_V1()</literal> macro call for the function,
1082 as illustrated below. Lack of such a macro indicates an old-style
1083 (<quote>version 0</quote>) function. The language name specified in <command>CREATE FUNCTION</command>
1084 is <literal>C</literal> in either case. Old-style functions are now deprecated
1085 because of portability problems and lack of functionality, but they
1086 are still supported for compatibility reasons.
1089 <sect2 id="xfunc-c-dynload">
1090 <title>Dynamic Loading</title>
1092 <indexterm zone="xfunc-c-dynload">
1093 <primary>dynamic loading</primary>
1097 The first time a user-defined function in a particular
1098 loadable object file is called in a session,
1099 the dynamic loader loads that object file into memory so that the
1100 function can be called. The <command>CREATE FUNCTION</command>
1101 for a user-defined C function must therefore specify two pieces of
1102 information for the function: the name of the loadable
1103 object file, and the C name (link symbol) of the specific function to call
1104 within that object file. If the C name is not explicitly specified then
1105 it is assumed to be the same as the SQL function name.
1109 The following algorithm is used to locate the shared object file
1110 based on the name given in the <command>CREATE FUNCTION</command>
1116 If the name is an absolute path, the given file is loaded.
1122 If the name starts with the string <literal>$libdir</literal>,
1123 that part is replaced by the <productname>PostgreSQL</> package
1125 name, which is determined at build time.<indexterm><primary>$libdir</></>
1131 If the name does not contain a directory part, the file is
1132 searched for in the path specified by the configuration variable
1133 <xref linkend="guc-dynamic-library-path">.<indexterm><primary>dynamic_library_path</></>
1139 Otherwise (the file was not found in the path, or it contains a
1140 non-absolute directory part), the dynamic loader will try to
1141 take the name as given, which will most likely fail. (It is
1142 unreliable to depend on the current working directory.)
1147 If this sequence does not work, the platform-specific shared
1148 library file name extension (often <filename>.so</filename>) is
1149 appended to the given name and this sequence is tried again. If
1150 that fails as well, the load will fail.
1154 The user ID the <productname>PostgreSQL</productname> server runs
1155 as must be able to traverse the path to the file you intend to
1156 load. Making the file or a higher-level directory not readable
1157 and/or not executable by the <systemitem>postgres</systemitem>
1158 user is a common mistake.
1162 In any case, the file name that is given in the
1163 <command>CREATE FUNCTION</command> command is recorded literally
1164 in the system catalogs, so if the file needs to be loaded again
1165 the same procedure is applied.
1170 <productname>PostgreSQL</productname> will not compile a C function
1171 automatically. The object file must be compiled before it is referenced
1172 in a <command>CREATE
1173 FUNCTION</> command. See <xref linkend="dfunc"> for additional
1179 After it is used for the first time, a dynamically loaded object
1180 file is retained in memory. Future calls in the same session to
1181 the function(s) in that file will only incur the small overhead of
1182 a symbol table lookup. If you need to force a reload of an object
1183 file, for example after recompiling it, use the <command>LOAD</>
1184 command or begin a fresh session.
1188 It is recommended to locate shared libraries either relative to
1189 <literal>$libdir</literal> or through the dynamic library path.
1190 This simplifies version upgrades if the new installation is at a
1191 different location. The actual directory that
1192 <literal>$libdir</literal> stands for can be found out with the
1193 command <literal>pg_config --pkglibdir</literal>.
1197 Before <productname>PostgreSQL</productname> release 7.2, only
1198 exact absolute paths to object files could be specified in
1199 <command>CREATE FUNCTION</>. This approach is now deprecated
1200 since it makes the function definition unnecessarily unportable.
1201 It's best to specify just the shared library name with no path nor
1202 extension, and let the search mechanism provide that information
1207 <sect2 id="xfunc-c-basetype">
1208 <title>Base Types in C-Language Functions</title>
1210 <indexterm zone="xfunc-c-basetype">
1211 <primary>data type</primary>
1212 <secondary>internal organisation</secondary>
1216 To know how to write C-language functions, you need to know how
1217 <productname>PostgreSQL</productname> internally represents base
1218 data types and how they can be passed to and from functions.
1219 Internally, <productname>PostgreSQL</productname> regards a base
1220 type as a <quote>blob of memory</quote>. The user-defined
1221 functions that you define over a type in turn define the way that
1222 <productname>PostgreSQL</productname> can operate on it. That
1223 is, <productname>PostgreSQL</productname> will only store and
1224 retrieve the data from disk and use your user-defined functions
1225 to input, process, and output the data.
1229 Base types can have one of three internal formats:
1234 pass by value, fixed-length
1239 pass by reference, fixed-length
1244 pass by reference, variable-length
1251 By-value types can only be 1, 2, or 4 bytes in length
1252 (also 8 bytes, if <literal>sizeof(Datum)</literal> is 8 on your machine).
1253 You should be careful
1254 to define your types such that they will be the same
1255 size (in bytes) on all architectures. For example, the
1256 <literal>long</literal> type is dangerous because it
1257 is 4 bytes on some machines and 8 bytes on others, whereas
1258 <type>int</type> type is 4 bytes on most
1259 Unix machines. A reasonable implementation of
1260 the <type>int4</type> type on Unix
1264 /* 4-byte integer, passed by value */
1270 On the other hand, fixed-length types of any size may
1271 be passed by-reference. For example, here is a sample
1272 implementation of a <productname>PostgreSQL</productname> type:
1275 /* 16-byte structure, passed by reference */
1282 Only pointers to such types can be used when passing
1283 them in and out of <productname>PostgreSQL</productname> functions.
1284 To return a value of such a type, allocate the right amount of
1285 memory with <literal>palloc</literal>, fill in the allocated memory,
1286 and return a pointer to it. (Also, if you just want to return the
1287 same value as one of your input arguments that's of the same data type,
1288 you can skip the extra <literal>palloc</literal> and just return the
1289 pointer to the input value.)
1293 Finally, all variable-length types must also be passed
1294 by reference. All variable-length types must begin
1295 with a length field of exactly 4 bytes, and all data to
1296 be stored within that type must be located in the memory
1297 immediately following that length field. The
1298 length field contains the total length of the structure,
1299 that is, it includes the size of the length field
1305 <emphasis>Never</> modify the contents of a pass-by-reference input
1306 value. If you do so you are likely to corrupt on-disk data, since
1307 the pointer you are given may well point directly into a disk buffer.
1308 The sole exception to this rule is explained in
1309 <xref linkend="xaggr">.
1314 As an example, we can define the type <type>text</type> as
1324 Obviously, the data field declared here is not long enough to hold
1325 all possible strings. Since it's impossible to declare a variable-size
1326 structure in <acronym>C</acronym>, we rely on the knowledge that the
1327 <acronym>C</acronym> compiler won't range-check array subscripts. We
1328 just allocate the necessary amount of space and then access the array as
1329 if it were declared the right length. (This is a common trick, which
1330 you can read about in many textbooks about C.)
1335 variable-length types, we must be careful to allocate
1336 the correct amount of memory and set the length field correctly.
1337 For example, if we wanted to store 40 bytes in a <structname>text</>
1338 structure, we might use a code fragment like this:
1341 #include "postgres.h"
1343 char buffer[40]; /* our source data */
1345 text *destination = (text *) palloc(VARHDRSZ + 40);
1346 destination->length = VARHDRSZ + 40;
1347 memcpy(destination->data, buffer, 40);
1351 <literal>VARHDRSZ</> is the same as <literal>sizeof(int4)</>, but
1352 it's considered good style to use the macro <literal>VARHDRSZ</>
1353 to refer to the size of the overhead for a variable-length type.
1357 <xref linkend="xfunc-c-type-table"> specifies which C type
1358 corresponds to which SQL type when writing a C-language function
1359 that uses a built-in type of <productname>PostgreSQL</>.
1360 The <quote>Defined In</quote> column gives the header file that
1361 needs to be included to get the type definition. (The actual
1362 definition may be in a different file that is included by the
1363 listed file. It is recommended that users stick to the defined
1364 interface.) Note that you should always include
1365 <filename>postgres.h</filename> first in any source file, because
1366 it declares a number of things that you will need anyway.
1369 <table tocentry="1" id="xfunc-c-type-table">
1370 <title>Equivalent C Types for Built-In SQL Types</title>
1387 <entry><type>abstime</type></entry>
1388 <entry><type>AbsoluteTime</type></entry>
1389 <entry><filename>utils/nabstime.h</filename></entry>
1392 <entry><type>boolean</type></entry>
1393 <entry><type>bool</type></entry>
1394 <entry><filename>postgres.h</filename> (maybe compiler built-in)</entry>
1397 <entry><type>box</type></entry>
1398 <entry><type>BOX*</type></entry>
1399 <entry><filename>utils/geo_decls.h</filename></entry>
1402 <entry><type>bytea</type></entry>
1403 <entry><type>bytea*</type></entry>
1404 <entry><filename>postgres.h</filename></entry>
1407 <entry><type>"char"</type></entry>
1408 <entry><type>char</type></entry>
1409 <entry>(compiler built-in)</entry>
1412 <entry><type>character</type></entry>
1413 <entry><type>BpChar*</type></entry>
1414 <entry><filename>postgres.h</filename></entry>
1417 <entry><type>cid</type></entry>
1418 <entry><type>CommandId</type></entry>
1419 <entry><filename>postgres.h</filename></entry>
1422 <entry><type>date</type></entry>
1423 <entry><type>DateADT</type></entry>
1424 <entry><filename>utils/date.h</filename></entry>
1427 <entry><type>smallint</type> (<type>int2</type>)</entry>
1428 <entry><type>int2</type> or <type>int16</type></entry>
1429 <entry><filename>postgres.h</filename></entry>
1432 <entry><type>int2vector</type></entry>
1433 <entry><type>int2vector*</type></entry>
1434 <entry><filename>postgres.h</filename></entry>
1437 <entry><type>integer</type> (<type>int4</type>)</entry>
1438 <entry><type>int4</type> or <type>int32</type></entry>
1439 <entry><filename>postgres.h</filename></entry>
1442 <entry><type>real</type> (<type>float4</type>)</entry>
1443 <entry><type>float4*</type></entry>
1444 <entry><filename>postgres.h</filename></entry>
1447 <entry><type>double precision</type> (<type>float8</type>)</entry>
1448 <entry><type>float8*</type></entry>
1449 <entry><filename>postgres.h</filename></entry>
1452 <entry><type>interval</type></entry>
1453 <entry><type>Interval*</type></entry>
1454 <entry><filename>utils/timestamp.h</filename></entry>
1457 <entry><type>lseg</type></entry>
1458 <entry><type>LSEG*</type></entry>
1459 <entry><filename>utils/geo_decls.h</filename></entry>
1462 <entry><type>name</type></entry>
1463 <entry><type>Name</type></entry>
1464 <entry><filename>postgres.h</filename></entry>
1467 <entry><type>oid</type></entry>
1468 <entry><type>Oid</type></entry>
1469 <entry><filename>postgres.h</filename></entry>
1472 <entry><type>oidvector</type></entry>
1473 <entry><type>oidvector*</type></entry>
1474 <entry><filename>postgres.h</filename></entry>
1477 <entry><type>path</type></entry>
1478 <entry><type>PATH*</type></entry>
1479 <entry><filename>utils/geo_decls.h</filename></entry>
1482 <entry><type>point</type></entry>
1483 <entry><type>POINT*</type></entry>
1484 <entry><filename>utils/geo_decls.h</filename></entry>
1487 <entry><type>regproc</type></entry>
1488 <entry><type>regproc</type></entry>
1489 <entry><filename>postgres.h</filename></entry>
1492 <entry><type>reltime</type></entry>
1493 <entry><type>RelativeTime</type></entry>
1494 <entry><filename>utils/nabstime.h</filename></entry>
1497 <entry><type>text</type></entry>
1498 <entry><type>text*</type></entry>
1499 <entry><filename>postgres.h</filename></entry>
1502 <entry><type>tid</type></entry>
1503 <entry><type>ItemPointer</type></entry>
1504 <entry><filename>storage/itemptr.h</filename></entry>
1507 <entry><type>time</type></entry>
1508 <entry><type>TimeADT</type></entry>
1509 <entry><filename>utils/date.h</filename></entry>
1512 <entry><type>time with time zone</type></entry>
1513 <entry><type>TimeTzADT</type></entry>
1514 <entry><filename>utils/date.h</filename></entry>
1517 <entry><type>timestamp</type></entry>
1518 <entry><type>Timestamp*</type></entry>
1519 <entry><filename>utils/timestamp.h</filename></entry>
1522 <entry><type>tinterval</type></entry>
1523 <entry><type>TimeInterval</type></entry>
1524 <entry><filename>utils/nabstime.h</filename></entry>
1527 <entry><type>varchar</type></entry>
1528 <entry><type>VarChar*</type></entry>
1529 <entry><filename>postgres.h</filename></entry>
1532 <entry><type>xid</type></entry>
1533 <entry><type>TransactionId</type></entry>
1534 <entry><filename>postgres.h</filename></entry>
1541 Now that we've gone over all of the possible structures
1542 for base types, we can show some examples of real functions.
1547 <title>Calling Conventions Version 0 for C-Language Functions</title>
1550 We present the <quote>old style</quote> calling convention first — although
1551 this approach is now deprecated, it's easier to get a handle on
1552 initially. In the version-0 method, the arguments and result
1553 of the C function are just declared in normal C style, but being
1554 careful to use the C representation of each SQL data type as shown
1559 Here are some examples:
1562 #include "postgres.h"
1563 #include <string.h>
1573 /* by reference, fixed length */
1576 add_one_float8(float8 *arg)
1578 float8 *result = (float8 *) palloc(sizeof(float8));
1580 *result = *arg + 1.0;
1586 makepoint(Point *pointx, Point *pointy)
1588 Point *new_point = (Point *) palloc(sizeof(Point));
1590 new_point->x = pointx->x;
1591 new_point->y = pointy->y;
1596 /* by reference, variable length */
1602 * VARSIZE is the total size of the struct in bytes.
1604 text *new_t = (text *) palloc(VARSIZE(t));
1605 VARATT_SIZEP(new_t) = VARSIZE(t);
1607 * VARDATA is a pointer to the data region of the struct.
1609 memcpy((void *) VARDATA(new_t), /* destination */
1610 (void *) VARDATA(t), /* source */
1611 VARSIZE(t)-VARHDRSZ); /* how many bytes */
1616 concat_text(text *arg1, text *arg2)
1618 int32 new_text_size = VARSIZE(arg1) + VARSIZE(arg2) - VARHDRSZ;
1619 text *new_text = (text *) palloc(new_text_size);
1621 VARATT_SIZEP(new_text) = new_text_size;
1622 memcpy(VARDATA(new_text), VARDATA(arg1), VARSIZE(arg1)-VARHDRSZ);
1623 memcpy(VARDATA(new_text) + (VARSIZE(arg1)-VARHDRSZ),
1624 VARDATA(arg2), VARSIZE(arg2)-VARHDRSZ);
1631 Supposing that the above code has been prepared in file
1632 <filename>funcs.c</filename> and compiled into a shared object,
1633 we could define the functions to <productname>PostgreSQL</productname>
1634 with commands like this:
1637 CREATE FUNCTION add_one(integer) RETURNS integer
1638 AS '<replaceable>DIRECTORY</replaceable>/funcs', 'add_one'
1641 -- note overloading of SQL function name "add_one"
1642 CREATE FUNCTION add_one(double precision) RETURNS double precision
1643 AS '<replaceable>DIRECTORY</replaceable>/funcs', 'add_one_float8'
1646 CREATE FUNCTION makepoint(point, point) RETURNS point
1647 AS '<replaceable>DIRECTORY</replaceable>/funcs', 'makepoint'
1650 CREATE FUNCTION copytext(text) RETURNS text
1651 AS '<replaceable>DIRECTORY</replaceable>/funcs', 'copytext'
1654 CREATE FUNCTION concat_text(text, text) RETURNS text
1655 AS '<replaceable>DIRECTORY</replaceable>/funcs', 'concat_text',
1661 Here, <replaceable>DIRECTORY</replaceable> stands for the
1662 directory of the shared library file (for instance the
1663 <productname>PostgreSQL</productname> tutorial directory, which
1664 contains the code for the examples used in this section).
1665 (Better style would be to use just <literal>'funcs'</> in the
1666 <literal>AS</> clause, after having added
1667 <replaceable>DIRECTORY</replaceable> to the search path. In any
1668 case, we may omit the system-specific extension for a shared
1669 library, commonly <literal>.so</literal> or
1670 <literal>.sl</literal>.)
1674 Notice that we have specified the functions as <quote>strict</quote>,
1676 the system should automatically assume a null result if any input
1677 value is null. By doing this, we avoid having to check for null inputs
1678 in the function code. Without this, we'd have to check for null values
1679 explicitly, by checking for a null pointer for each
1680 pass-by-reference argument. (For pass-by-value arguments, we don't
1681 even have a way to check!)
1685 Although this calling convention is simple to use,
1686 it is not very portable; on some architectures there are problems
1687 with passing data types that are smaller than <type>int</type> this way. Also, there is
1688 no simple way to return a null result, nor to cope with null arguments
1689 in any way other than making the function strict. The version-1
1690 convention, presented next, overcomes these objections.
1695 <title>Calling Conventions Version 1 for C-Language Functions</title>
1698 The version-1 calling convention relies on macros to suppress most
1699 of the complexity of passing arguments and results. The C declaration
1700 of a version-1 function is always
1702 Datum funcname(PG_FUNCTION_ARGS)
1704 In addition, the macro call
1706 PG_FUNCTION_INFO_V1(funcname);
1708 must appear in the same source file. (Conventionally. it's
1709 written just before the function itself.) This macro call is not
1710 needed for <literal>internal</>-language functions, since
1711 <productname>PostgreSQL</> assumes that all internal functions
1712 use the version-1 convention. It is, however, required for
1713 dynamically-loaded functions.
1717 In a version-1 function, each actual argument is fetched using a
1718 <function>PG_GETARG_<replaceable>xxx</replaceable>()</function>
1719 macro that corresponds to the argument's data type, and the
1720 result is returned using a
1721 <function>PG_RETURN_<replaceable>xxx</replaceable>()</function>
1722 macro for the return type.
1723 <function>PG_GETARG_<replaceable>xxx</replaceable>()</function>
1724 takes as its argument the number of the function argument to
1725 fetch, where the count starts at 0.
1726 <function>PG_RETURN_<replaceable>xxx</replaceable>()</function>
1727 takes as its argument the actual value to return.
1731 Here we show the same functions as above, coded in version-1 style:
1734 #include "postgres.h"
1735 #include <string.h>
1740 PG_FUNCTION_INFO_V1(add_one);
1743 add_one(PG_FUNCTION_ARGS)
1745 int32 arg = PG_GETARG_INT32(0);
1747 PG_RETURN_INT32(arg + 1);
1750 /* by reference, fixed length */
1752 PG_FUNCTION_INFO_V1(add_one_float8);
1755 add_one_float8(PG_FUNCTION_ARGS)
1757 /* The macros for FLOAT8 hide its pass-by-reference nature. */
1758 float8 arg = PG_GETARG_FLOAT8(0);
1760 PG_RETURN_FLOAT8(arg + 1.0);
1763 PG_FUNCTION_INFO_V1(makepoint);
1766 makepoint(PG_FUNCTION_ARGS)
1768 /* Here, the pass-by-reference nature of Point is not hidden. */
1769 Point *pointx = PG_GETARG_POINT_P(0);
1770 Point *pointy = PG_GETARG_POINT_P(1);
1771 Point *new_point = (Point *) palloc(sizeof(Point));
1773 new_point->x = pointx->x;
1774 new_point->y = pointy->y;
1776 PG_RETURN_POINT_P(new_point);
1779 /* by reference, variable length */
1781 PG_FUNCTION_INFO_V1(copytext);
1784 copytext(PG_FUNCTION_ARGS)
1786 text *t = PG_GETARG_TEXT_P(0);
1788 * VARSIZE is the total size of the struct in bytes.
1790 text *new_t = (text *) palloc(VARSIZE(t));
1791 VARATT_SIZEP(new_t) = VARSIZE(t);
1793 * VARDATA is a pointer to the data region of the struct.
1795 memcpy((void *) VARDATA(new_t), /* destination */
1796 (void *) VARDATA(t), /* source */
1797 VARSIZE(t)-VARHDRSZ); /* how many bytes */
1798 PG_RETURN_TEXT_P(new_t);
1801 PG_FUNCTION_INFO_V1(concat_text);
1804 concat_text(PG_FUNCTION_ARGS)
1806 text *arg1 = PG_GETARG_TEXT_P(0);
1807 text *arg2 = PG_GETARG_TEXT_P(1);
1808 int32 new_text_size = VARSIZE(arg1) + VARSIZE(arg2) - VARHDRSZ;
1809 text *new_text = (text *) palloc(new_text_size);
1811 VARATT_SIZEP(new_text) = new_text_size;
1812 memcpy(VARDATA(new_text), VARDATA(arg1), VARSIZE(arg1)-VARHDRSZ);
1813 memcpy(VARDATA(new_text) + (VARSIZE(arg1)-VARHDRSZ),
1814 VARDATA(arg2), VARSIZE(arg2)-VARHDRSZ);
1815 PG_RETURN_TEXT_P(new_text);
1821 The <command>CREATE FUNCTION</command> commands are the same as
1822 for the version-0 equivalents.
1826 At first glance, the version-1 coding conventions may appear to
1827 be just pointless obscurantism. They do, however, offer a number
1828 of improvements, because the macros can hide unnecessary detail.
1829 An example is that in coding <function>add_one_float8</>, we no longer need to
1830 be aware that <type>float8</type> is a pass-by-reference type. Another
1831 example is that the <literal>GETARG</> macros for variable-length types allow
1832 for more efficient fetching of <quote>toasted</quote> (compressed or
1833 out-of-line) values.
1837 One big improvement in version-1 functions is better handling of null
1838 inputs and results. The macro <function>PG_ARGISNULL(<replaceable>n</>)</function>
1839 allows a function to test whether each input is null. (Of course, doing
1840 this is only necessary in functions not declared <quote>strict</>.)
1842 <function>PG_GETARG_<replaceable>xxx</replaceable>()</function> macros,
1843 the input arguments are counted beginning at zero. Note that one
1844 should refrain from executing
1845 <function>PG_GETARG_<replaceable>xxx</replaceable>()</function> until
1846 one has verified that the argument isn't null.
1847 To return a null result, execute <function>PG_RETURN_NULL()</function>;
1848 this works in both strict and nonstrict functions.
1852 Other options provided in the new-style interface are two
1854 <function>PG_GETARG_<replaceable>xxx</replaceable>()</function>
1855 macros. The first of these,
1856 <function>PG_GETARG_<replaceable>xxx</replaceable>_COPY()</function>,
1857 guarantees to return a copy of the specified argument that is
1858 safe for writing into. (The normal macros will sometimes return a
1859 pointer to a value that is physically stored in a table, which
1860 must not be written to. Using the
1861 <function>PG_GETARG_<replaceable>xxx</replaceable>_COPY()</function>
1862 macros guarantees a writable result.)
1863 The second variant consists of the
1864 <function>PG_GETARG_<replaceable>xxx</replaceable>_SLICE()</function>
1865 macros which take three arguments. The first is the number of the
1866 function argument (as above). The second and third are the offset and
1867 length of the segment to be returned. Offsets are counted from
1868 zero, and a negative length requests that the remainder of the
1869 value be returned. These macros provide more efficient access to
1870 parts of large values in the case where they have storage type
1871 <quote>external</quote>. (The storage type of a column can be specified using
1872 <literal>ALTER TABLE <replaceable>tablename</replaceable> ALTER
1873 COLUMN <replaceable>colname</replaceable> SET STORAGE
1874 <replaceable>storagetype</replaceable></literal>. <replaceable>storagetype</replaceable> is one of
1875 <literal>plain</>, <literal>external</>, <literal>extended</literal>,
1876 or <literal>main</>.)
1880 Finally, the version-1 function call conventions make it possible
1881 to return set results (<xref linkend="xfunc-c-return-set">) and
1882 implement trigger functions (<xref linkend="triggers">) and
1883 procedural-language call handlers (<xref
1884 linkend="plhandler">). Version-1 code is also more
1885 portable than version-0, because it does not break restrictions
1886 on function call protocol in the C standard. For more details
1887 see <filename>src/backend/utils/fmgr/README</filename> in the
1888 source distribution.
1893 <title>Writing Code</title>
1896 Before we turn to the more advanced topics, we should discuss
1897 some coding rules for <productname>PostgreSQL</productname>
1898 C-language functions. While it may be possible to load functions
1899 written in languages other than C into
1900 <productname>PostgreSQL</productname>, this is usually difficult
1901 (when it is possible at all) because other languages, such as
1902 C++, FORTRAN, or Pascal often do not follow the same calling
1903 convention as C. That is, other languages do not pass argument
1904 and return values between functions in the same way. For this
1905 reason, we will assume that your C-language functions are
1906 actually written in C.
1910 The basic rules for writing and building C functions are as follows:
1915 Use <literal>pg_config
1916 --includedir-server</literal><indexterm><primary>pg_config</><secondary>with user-defined C functions</></>
1917 to find out where the <productname>PostgreSQL</> server header
1918 files are installed on your system (or the system that your
1919 users will be running on). This option is new with
1920 <productname>PostgreSQL</> 7.2. For
1921 <productname>PostgreSQL</> 7.1 you should use the option
1922 <option>--includedir</option>. (<command>pg_config</command>
1923 will exit with a non-zero status if it encounters an unknown
1924 option.) For releases prior to 7.1 you will have to guess,
1925 but since that was before the current calling conventions were
1926 introduced, it is unlikely that you want to support those
1933 When allocating memory, use the
1934 <productname>PostgreSQL</productname> functions
1935 <function>palloc</function><indexterm><primary>palloc</></> and <function>pfree</function><indexterm><primary>pfree</></>
1936 instead of the corresponding C library functions
1937 <function>malloc</function> and <function>free</function>.
1938 The memory allocated by <function>palloc</function> will be
1939 freed automatically at the end of each transaction, preventing
1946 Always zero the bytes of your structures using
1947 <function>memset</function>. Without this, it's difficult to
1948 support hash indexes or hash joins, as you must pick out only
1949 the significant bits of your data structure to compute a hash.
1950 Even if you initialize all fields of your structure, there may be
1951 alignment padding (holes in the structure) that may contain
1958 Most of the internal <productname>PostgreSQL</productname>
1959 types are declared in <filename>postgres.h</filename>, while
1960 the function manager interfaces
1961 (<symbol>PG_FUNCTION_ARGS</symbol>, etc.) are in
1962 <filename>fmgr.h</filename>, so you will need to include at
1963 least these two files. For portability reasons it's best to
1964 include <filename>postgres.h</filename> <emphasis>first</>,
1965 before any other system or user header files. Including
1966 <filename>postgres.h</filename> will also include
1967 <filename>elog.h</filename> and <filename>palloc.h</filename>
1974 Symbol names defined within object files must not conflict
1975 with each other or with symbols defined in the
1976 <productname>PostgreSQL</productname> server executable. You
1977 will have to rename your functions or variables if you get
1978 error messages to this effect.
1984 Compiling and linking your code so that it can be dynamically
1985 loaded into <productname>PostgreSQL</productname> always
1986 requires special flags. See <xref linkend="dfunc"> for a
1987 detailed explanation of how to do it for your particular
1997 <sect2 id="xfunc-c-pgxs">
1998 <title>Extension Building Infrastructure</title>
2000 <indexterm zone="xfunc-c-pgxs">
2001 <primary>pgxs</primary>
2005 If you are thinking about distributing your
2006 <productname>PostgreSQL</> extension modules, setting up a
2007 portable build system for them can be fairly difficult. Therefore
2008 the <productname>PostgreSQL</> installation provides a build
2009 infrastructure for extensions, called <acronym>PGXS</acronym>, so
2010 that simple extension modules can be built simply against an
2011 already installed server. Note that this infrastructure is not
2012 intended to be a universal build system framework that can be used
2013 to build all software interfacing to <productname>PostgreSQL</>;
2014 it simply automates common build rules for simple server extension
2015 modules. For more complicated packages, you need to write your
2020 To use the infrastructure for your extension, you must write a
2021 simple makefile. In that makefile, you need to set some variables
2022 and finally include the global <acronym>PGXS</acronym> makefile.
2023 Here is an example that builds an extension module named
2024 <literal>isbn_issn</literal> consisting of a shared library, an
2025 SQL script, and a documentation text file:
2028 DATA_built = isbn_issn.sql
2029 DOCS = README.isbn_issn
2031 PGXS := $(shell pg_config --pgxs)
2034 The last two lines should always be the same. Earlier in the
2035 file, you assign variables or add custom
2036 <application>make</application> rules.
2040 The following variables can be set:
2044 <term><varname>MODULES</varname></term>
2047 list of shared objects to be built from source file with same
2048 stem (do not include suffix in this list)
2054 <term><varname>DATA</varname></term>
2057 random files to install into <literal><replaceable>prefix</replaceable>/share/contrib</literal>
2063 <term><varname>DATA_built</varname></term>
2066 random files to install into
2067 <literal><replaceable>prefix</replaceable>/share/contrib</literal>,
2068 which need to be built first
2074 <term><varname>DOCS</varname></term>
2077 random files to install under
2078 <literal><replaceable>prefix</replaceable>/doc/contrib</literal>
2084 <term><varname>SCRIPTS</varname></term>
2087 script files (not binaries) to install into
2088 <literal><replaceable>prefix</replaceable>/bin</literal>
2094 <term><varname>SCRIPTS_built</varname></term>
2097 script files (not binaries) to install into
2098 <literal><replaceable>prefix</replaceable>/bin</literal>,
2099 which need to be built first
2105 <term><varname>REGRESS</varname></term>
2108 list of regression test cases (without suffix)
2114 or at most one of these two:
2118 <term><varname>PROGRAM</varname></term>
2121 a binary program to build (list objects files in <varname>OBJS</varname>)
2127 <term><varname>MODULE_big</varname></term>
2130 a shared object to build (list object files in <varname>OBJS</varname>)
2136 The following can also be set:
2141 <term><varname>EXTRA_CLEAN</varname></term>
2144 extra files to remove in <literal>make clean</literal>
2150 <term><varname>PG_CPPFLAGS</varname></term>
2153 will be added to <varname>CPPFLAGS</varname>
2159 <term><varname>PG_LIBS</varname></term>
2162 will be added to <varname>PROGRAM</varname> link line
2168 <term><varname>SHLIB_LINK</varname></term>
2171 will be added to <varname>MODULE_big</varname> link line
2179 Put this makefile as <literal>Makefile</literal> in the directory
2180 which holds your extension. Then you can do
2181 <literal>make</literal> to compile, and later <literal>make
2182 install</literal> to install your module. The extension is
2183 compiled and installed for the
2184 <productname>PostgreSQL</productname> installation that
2185 corresponds to the first <command>pg_config</command> command
2192 <title>Composite-Type Arguments in C-Language Functions</title>
2195 Composite types do not have a fixed layout like C structures.
2196 Instances of a composite type may contain null fields. In
2197 addition, composite types that are part of an inheritance
2198 hierarchy may have different fields than other members of the
2199 same inheritance hierarchy. Therefore,
2200 <productname>PostgreSQL</productname> provides a function
2201 interface for accessing fields of composite types from C.
2205 Suppose we want to write a function to answer the query
2208 SELECT name, c_overpaid(emp, 1500) AS overpaid
2210 WHERE name = 'Bill' OR name = 'Sam';
2213 Using call conventions version 0, we can define
2214 <function>c_overpaid</> as:
2217 #include "postgres.h"
2218 #include "executor/executor.h" /* for GetAttributeByName() */
2221 c_overpaid(HeapTupleHeader t, /* the current row of emp */
2227 salary = DatumGetInt32(GetAttributeByName(t, "salary", &isnull));
2230 return salary > limit;
2234 In version-1 coding, the above would look like this:
2237 #include "postgres.h"
2238 #include "executor/executor.h" /* for GetAttributeByName() */
2240 PG_FUNCTION_INFO_V1(c_overpaid);
2243 c_overpaid(PG_FUNCTION_ARGS)
2245 HeapTupleHeader t = PG_GETARG_HEAPTUPLEHEADER(0);
2246 int32 limit = PG_GETARG_INT32(1);
2250 salary = GetAttributeByName(t, "salary", &isnull);
2252 PG_RETURN_BOOL(false);
2253 /* Alternatively, we might prefer to do PG_RETURN_NULL() for null salary. */
2255 PG_RETURN_BOOL(DatumGetInt32(salary) > limit);
2261 <function>GetAttributeByName</function> is the
2262 <productname>PostgreSQL</productname> system function that
2263 returns attributes out of the specified row. It has
2264 three arguments: the argument of type <type>HeapTupleHeader</type> passed
2266 the function, the name of the desired attribute, and a
2267 return parameter that tells whether the attribute
2268 is null. <function>GetAttributeByName</function> returns a <type>Datum</type>
2269 value that you can convert to the proper data type by using the
2270 appropriate <function>DatumGet<replaceable>XXX</replaceable>()</function>
2271 macro. Note that the return value is meaningless if the null flag is
2272 set; always check the null flag before trying to do anything with the
2277 There is also <function>GetAttributeByNum</function>, which selects
2278 the target attribute by column number instead of name.
2282 The following command declares the function
2283 <function>c_overpaid</function> in SQL:
2286 CREATE FUNCTION c_overpaid(emp, integer) RETURNS boolean
2287 AS '<replaceable>DIRECTORY</replaceable>/funcs', 'c_overpaid'
2291 Notice we have used <literal>STRICT</> so that we did not have to
2292 check whether the input arguments were NULL.
2297 <title>Returning Rows (Composite Types) from C-Language Functions</title>
2300 To return a row or composite-type value from a C-language
2301 function, you can use a special API that provides macros and
2302 functions to hide most of the complexity of building composite
2303 data types. To use this API, the source file must include:
2305 #include "funcapi.h"
2310 There are two ways you can build a composite data value (henceforth
2311 a <quote>tuple</>): you can build it from an array of Datum values,
2312 or from an array of C strings that can be passed to the input
2313 conversion functions of the tuple's column data types. In either
2314 case, you first need to obtain or construct a <structname>TupleDesc</>
2315 descriptor for the tuple structure. When working with Datums, you
2316 pass the <structname>TupleDesc</> to <function>BlessTupleDesc</>,
2317 and then call <function>heap_form_tuple</> for each row. When working
2318 with C strings, you pass the <structname>TupleDesc</> to
2319 <function>TupleDescGetAttInMetadata</>, and then call
2320 <function>BuildTupleFromCStrings</> for each row. In the case of a
2321 function returning a set of tuples, the setup steps can all be done
2322 once during the first call of the function.
2326 Several helper functions are available for setting up the needed
2327 <structname>TupleDesc</>. The recommended way to do this in most
2328 functions returning composite values is to call
2330 TypeFuncClass get_call_result_type(FunctionCallInfo fcinfo,
2332 TupleDesc *resultTupleDesc)
2334 passing the same <literal>fcinfo</> struct passed to the calling function
2335 itself. (This of course requires that you use the version-1
2336 calling conventions.) <varname>resultTypeId</> can be specified
2337 as <literal>NULL</> or as the address of a local variable to receive the
2338 function's result type OID. <varname>resultTupleDesc</> should be the
2339 address of a local <structname>TupleDesc</> variable. Check that the
2340 result is <literal>TYPEFUNC_COMPOSITE</>; if so,
2341 <varname>resultTupleDesc</> has been filled with the needed
2342 <structname>TupleDesc</>. (If it is not, you can report an error along
2343 the lines of <quote>function returning record called in context that
2344 cannot accept type record</quote>.)
2349 <function>get_call_result_type</> can resolve the actual type of a
2350 polymorphic function result; so it is useful in functions that return
2351 scalar polymorphic results, not only functions that return composites.
2352 The <varname>resultTypeId</> output is primarily useful for functions
2353 returning polymorphic scalars.
2359 <function>get_call_result_type</> has a sibling
2360 <function>get_expr_result_type</>, which can be used to resolve the
2361 expected output type for a function call represented by an expression
2362 tree. This can be used when trying to determine the result type from
2363 outside the function itself. There is also
2364 <function>get_func_result_type</>, which can be used when only the
2365 function's OID is available. However these functions are not able
2366 to deal with functions declared to return <structname>record</>, and
2367 <function>get_func_result_type</> cannot resolve polymorphic types,
2368 so you should preferentially use <function>get_call_result_type</>.
2373 Older, now-deprecated functions for obtaining
2374 <structname>TupleDesc</>s are
2376 TupleDesc RelationNameGetTupleDesc(const char *relname)
2378 to get a <structname>TupleDesc</> for the row type of a named relation,
2381 TupleDesc TypeGetTupleDesc(Oid typeoid, List *colaliases)
2383 to get a <structname>TupleDesc</> based on a type OID. This can
2384 be used to get a <structname>TupleDesc</> for a base or
2385 composite type. It will not work for a function that returns
2386 <structname>record</>, however, and it cannot resolve polymorphic
2391 Once you have a <structname>TupleDesc</>, call
2393 TupleDesc BlessTupleDesc(TupleDesc tupdesc)
2395 if you plan to work with Datums, or
2397 AttInMetadata *TupleDescGetAttInMetadata(TupleDesc tupdesc)
2399 if you plan to work with C strings. If you are writing a function
2400 returning set, you can save the results of these functions in the
2401 <structname>FuncCallContext</> structure — use the
2402 <structfield>tuple_desc</> or <structfield>attinmeta</> field
2407 When working with Datums, use
2409 HeapTuple heap_form_tuple(TupleDesc tupdesc, Datum *values, bool *isnull)
2411 to build a <structname>HeapTuple</> given user data in Datum form.
2415 When working with C strings, use
2417 HeapTuple BuildTupleFromCStrings(AttInMetadata *attinmeta, char **values)
2419 to build a <structname>HeapTuple</> given user data
2420 in C string form. <literal>values</literal> is an array of C strings,
2421 one for each attribute of the return row. Each C string should be in
2422 the form expected by the input function of the attribute data
2423 type. In order to return a null value for one of the attributes,
2424 the corresponding pointer in the <parameter>values</> array
2425 should be set to <symbol>NULL</>. This function will need to
2426 be called again for each row you return.
2430 Once you have built a tuple to return from your function, it
2431 must be converted into a <type>Datum</>. Use
2433 HeapTupleGetDatum(HeapTuple tuple)
2435 to convert a <structname>HeapTuple</> into a valid Datum. This
2436 <type>Datum</> can be returned directly if you intend to return
2437 just a single row, or it can be used as the current return value
2438 in a set-returning function.
2442 An example appears in the next section.
2447 <sect2 id="xfunc-c-return-set">
2448 <title>Returning Sets from C-Language Functions</title>
2451 There is also a special API that provides support for returning
2452 sets (multiple rows) from a C-language function. A set-returning
2453 function must follow the version-1 calling conventions. Also,
2454 source files must include <filename>funcapi.h</filename>, as
2459 A set-returning function (<acronym>SRF</>) is called
2460 once for each item it returns. The <acronym>SRF</> must
2461 therefore save enough state to remember what it was doing and
2462 return the next item on each call.
2463 The structure <structname>FuncCallContext</> is provided to help
2464 control this process. Within a function, <literal>fcinfo->flinfo->fn_extra</>
2465 is used to hold a pointer to <structname>FuncCallContext</>
2471 * Number of times we've been called before
2473 * call_cntr is initialized to 0 for you by SRF_FIRSTCALL_INIT(), and
2474 * incremented for you every time SRF_RETURN_NEXT() is called.
2479 * OPTIONAL maximum number of calls
2481 * max_calls is here for convenience only and setting it is optional.
2482 * If not set, you must provide alternative means to know when the
2488 * OPTIONAL pointer to result slot
2490 * This is obsolete and only present for backwards compatibility, viz,
2491 * user-defined SRFs that use the deprecated TupleDescGetSlot().
2493 TupleTableSlot *slot;
2496 * OPTIONAL pointer to miscellaneous user-provided context information
2498 * user_fctx is for use as a pointer to your own data to retain
2499 * arbitrary context information between calls of your function.
2504 * OPTIONAL pointer to struct containing attribute type input metadata
2506 * attinmeta is for use when returning tuples (i.e., composite data types)
2507 * and is not used when returning base data types. It is only needed
2508 * if you intend to use BuildTupleFromCStrings() to create the return
2511 AttInMetadata *attinmeta;
2514 * memory context used for structures that must live for multiple calls
2516 * multi_call_memory_ctx is set by SRF_FIRSTCALL_INIT() for you, and used
2517 * by SRF_RETURN_DONE() for cleanup. It is the most appropriate memory
2518 * context for any memory that is to be reused across multiple calls
2521 MemoryContext multi_call_memory_ctx;
2524 * OPTIONAL pointer to struct containing tuple description
2526 * tuple_desc is for use when returning tuples (i.e. composite data types)
2527 * and is only needed if you are going to build the tuples with
2528 * heap_form_tuple() rather than with BuildTupleFromCStrings(). Note that
2529 * the TupleDesc pointer stored here should usually have been run through
2530 * BlessTupleDesc() first.
2532 TupleDesc tuple_desc;
2539 An <acronym>SRF</> uses several functions and macros that
2540 automatically manipulate the <structname>FuncCallContext</>
2541 structure (and expect to find it via <literal>fn_extra</>). Use
2545 to determine if your function is being called for the first or a
2546 subsequent time. On the first call (only) use
2548 SRF_FIRSTCALL_INIT()
2550 to initialize the <structname>FuncCallContext</>. On every function call,
2551 including the first, use
2555 to properly set up for using the <structname>FuncCallContext</>
2556 and clearing any previously returned data left over from the
2561 If your function has data to return, use
2563 SRF_RETURN_NEXT(funcctx, result)
2565 to return it to the caller. (<literal>result</> must be of type
2566 <type>Datum</>, either a single value or a tuple prepared as
2567 described above.) Finally, when your function is finished
2570 SRF_RETURN_DONE(funcctx)
2572 to clean up and end the <acronym>SRF</>.
2576 The memory context that is current when the <acronym>SRF</> is called is
2577 a transient context that will be cleared between calls. This means
2578 that you do not need to call <function>pfree</> on everything
2579 you allocated using <function>palloc</>; it will go away anyway. However, if you want to allocate
2580 any data structures to live across calls, you need to put them somewhere
2581 else. The memory context referenced by
2582 <structfield>multi_call_memory_ctx</> is a suitable location for any
2583 data that needs to survive until the <acronym>SRF</> is finished running. In most
2584 cases, this means that you should switch into
2585 <structfield>multi_call_memory_ctx</> while doing the first-call setup.
2589 A complete pseudo-code example looks like the following:
2592 my_set_returning_function(PG_FUNCTION_ARGS)
2594 FuncCallContext *funcctx;
2596 MemoryContext oldcontext;
2597 <replaceable>further declarations as needed</replaceable>
2599 if (SRF_IS_FIRSTCALL())
2601 funcctx = SRF_FIRSTCALL_INIT();
2602 oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
2603 /* One-time setup code appears here: */
2604 <replaceable>user code</replaceable>
2605 <replaceable>if returning composite</replaceable>
2606 <replaceable>build TupleDesc, and perhaps AttInMetadata</replaceable>
2607 <replaceable>endif returning composite</replaceable>
2608 <replaceable>user code</replaceable>
2609 MemoryContextSwitchTo(oldcontext);
2612 /* Each-time setup code appears here: */
2613 <replaceable>user code</replaceable>
2614 funcctx = SRF_PERCALL_SETUP();
2615 <replaceable>user code</replaceable>
2617 /* this is just one way we might test whether we are done: */
2618 if (funcctx->call_cntr < funcctx->max_calls)
2620 /* Here we want to return another item: */
2621 <replaceable>user code</replaceable>
2622 <replaceable>obtain result Datum</replaceable>
2623 SRF_RETURN_NEXT(funcctx, result);
2627 /* Here we are done returning items and just need to clean up: */
2628 <replaceable>user code</replaceable>
2629 SRF_RETURN_DONE(funcctx);
2636 A complete example of a simple <acronym>SRF</> returning a composite type
2639 PG_FUNCTION_INFO_V1(retcomposite);
2642 retcomposite(PG_FUNCTION_ARGS)
2644 FuncCallContext *funcctx;
2648 AttInMetadata *attinmeta;
2650 /* stuff done only on the first call of the function */
2651 if (SRF_IS_FIRSTCALL())
2653 MemoryContext oldcontext;
2655 /* create a function context for cross-call persistence */
2656 funcctx = SRF_FIRSTCALL_INIT();
2658 /* switch to memory context appropriate for multiple function calls */
2659 oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
2661 /* total number of tuples to be returned */
2662 funcctx->max_calls = PG_GETARG_UINT32(0);
2664 /* Build a tuple descriptor for our result type */
2665 if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
2667 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
2668 errmsg("function returning record called in context "
2669 "that cannot accept type record")));
2672 * generate attribute metadata needed later to produce tuples from raw
2675 attinmeta = TupleDescGetAttInMetadata(tupdesc);
2676 funcctx->attinmeta = attinmeta;
2678 MemoryContextSwitchTo(oldcontext);
2681 /* stuff done on every call of the function */
2682 funcctx = SRF_PERCALL_SETUP();
2684 call_cntr = funcctx->call_cntr;
2685 max_calls = funcctx->max_calls;
2686 attinmeta = funcctx->attinmeta;
2688 if (call_cntr < max_calls) /* do when there is more left to send */
2695 * Prepare a values array for building the returned tuple.
2696 * This should be an array of C strings which will
2697 * be processed later by the type input functions.
2699 values = (char **) palloc(3 * sizeof(char *));
2700 values[0] = (char *) palloc(16 * sizeof(char));
2701 values[1] = (char *) palloc(16 * sizeof(char));
2702 values[2] = (char *) palloc(16 * sizeof(char));
2704 snprintf(values[0], 16, "%d", 1 * PG_GETARG_INT32(1));
2705 snprintf(values[1], 16, "%d", 2 * PG_GETARG_INT32(1));
2706 snprintf(values[2], 16, "%d", 3 * PG_GETARG_INT32(1));
2709 tuple = BuildTupleFromCStrings(attinmeta, values);
2711 /* make the tuple into a datum */
2712 result = HeapTupleGetDatum(tuple);
2714 /* clean up (this is not really necessary) */
2720 SRF_RETURN_NEXT(funcctx, result);
2722 else /* do when there is no more left */
2724 SRF_RETURN_DONE(funcctx);
2729 One way to declare this function in SQL is:
2731 CREATE TYPE __retcomposite AS (f1 integer, f2 integer, f3 integer);
2733 CREATE OR REPLACE FUNCTION retcomposite(integer, integer)
2734 RETURNS SETOF __retcomposite
2735 AS '<replaceable>filename</>', 'retcomposite'
2736 LANGUAGE C IMMUTABLE STRICT;
2738 A different way is to use OUT parameters:
2740 CREATE OR REPLACE FUNCTION retcomposite(IN integer, IN integer,
2741 OUT f1 integer, OUT f2 integer, OUT f3 integer)
2742 RETURNS SETOF record
2743 AS '<replaceable>filename</>', 'retcomposite'
2744 LANGUAGE C IMMUTABLE STRICT;
2746 Notice that in this method the output type of the function is formally
2747 an anonymous <structname>record</> type.
2751 The directory <filename>contrib/tablefunc</> in the source
2752 distribution contains more examples of set-returning functions.
2757 <title>Polymorphic Arguments and Return Types</title>
2760 C-language functions may be declared to accept and
2761 return the polymorphic types
2762 <type>anyelement</type> and <type>anyarray</type>.
2763 See <xref linkend="extend-types-polymorphic"> for a more detailed explanation
2764 of polymorphic functions. When function arguments or return types
2765 are defined as polymorphic types, the function author cannot know
2766 in advance what data type it will be called with, or
2767 need to return. There are two routines provided in <filename>fmgr.h</>
2768 to allow a version-1 C function to discover the actual data types
2769 of its arguments and the type it is expected to return. The routines are
2770 called <literal>get_fn_expr_rettype(FmgrInfo *flinfo)</> and
2771 <literal>get_fn_expr_argtype(FmgrInfo *flinfo, int argnum)</>.
2772 They return the result or argument type OID, or <symbol>InvalidOid</symbol> if the
2773 information is not available.
2774 The structure <literal>flinfo</> is normally accessed as
2775 <literal>fcinfo->flinfo</>. The parameter <literal>argnum</>
2776 is zero based. <function>get_call_result_type</> can also be used
2777 as an alternative to <function>get_fn_expr_rettype</>.
2781 For example, suppose we want to write a function to accept a single
2782 element of any type, and return a one-dimensional array of that type:
2785 PG_FUNCTION_INFO_V1(make_array);
2787 make_array(PG_FUNCTION_ARGS)
2790 Oid element_type = get_fn_expr_argtype(fcinfo->flinfo, 0);
2799 if (!OidIsValid(element_type))
2800 elog(ERROR, "could not determine data type of input");
2802 /* get the provided element */
2803 element = PG_GETARG_DATUM(0);
2805 /* we have one dimension */
2807 /* and one element */
2809 /* and lower bound is 1 */
2812 /* get required info about the element type */
2813 get_typlenbyvalalign(element_type, &typlen, &typbyval, &typalign);
2815 /* now build the array */
2816 result = construct_md_array(&element, ndims, dims, lbs,
2817 element_type, typlen, typbyval, typalign);
2819 PG_RETURN_ARRAYTYPE_P(result);
2825 The following command declares the function
2826 <function>make_array</function> in SQL:
2829 CREATE FUNCTION make_array(anyelement) RETURNS anyarray
2830 AS '<replaceable>DIRECTORY</replaceable>/funcs', 'make_array'
2834 Note the use of <literal>STRICT</literal>; this is essential
2835 since the code is not bothering to test for a null input.
2840 <!-- Keep this comment at the end of the file
2845 sgml-minimize-attributes:nil
2846 sgml-always-quote-attributes:t
2849 sgml-parent-document:nil
2850 sgml-default-dtd-file:"./reference.ced"
2851 sgml-exposed-tags:nil
2852 sgml-local-catalogs:("/usr/lib/sgml/catalog")
2853 sgml-local-ecat-files:nil
projects / postgresql / blob