Debian Codegraph @

doc/src/sgml/xfunc.sgml

raw
1 <!-- doc/src/sgml/xfunc.sgml -->
2
3 <sect1 id="xfunc">
4 <title>User-Defined Functions</title>
5
6 <indexterm zone="xfunc">
7 <primary>function</primary>
8 <secondary>user-defined</secondary>
9 </indexterm>
10
11 <para>
12 <productname>PostgreSQL</productname> provides four kinds of
13 functions:
14
15 <itemizedlist>
16 <listitem>
17 <para>
18 query language functions (functions written in
19 <acronym>SQL</acronym>) (<xref linkend="xfunc-sql"/>)
20 </para>
21 </listitem>
22 <listitem>
23 <para>
24 procedural language functions (functions written in, for
25 example, <application>PL/pgSQL</application> or <application>PL/Tcl</application>)
26 (<xref linkend="xfunc-pl"/>)
27 </para>
28 </listitem>
29 <listitem>
30 <para>
31 internal functions (<xref linkend="xfunc-internal"/>)
32 </para>
33 </listitem>
34 <listitem>
35 <para>
36 C-language functions (<xref linkend="xfunc-c"/>)
37 </para>
38 </listitem>
39 </itemizedlist>
40 </para>
41
42 <para>
43 Every kind
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.
49 </para>
50
51 <para>
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.
55 </para>
56
57 <para>
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.
62 </para>
63
64 <para>
65 Throughout this chapter, it can be useful to look at the reference
66 page of the <link linkend="sql-createfunction"><command>CREATE
67 FUNCTION</command></link> command to
68 understand the examples better. Some examples from this chapter
69 can be found in <filename>funcs.sql</filename> and
70 <filename>funcs.c</filename> in the <filename>src/tutorial</filename>
71 directory in the <productname>PostgreSQL</productname> source
72 distribution.
73 </para>
74 </sect1>
75
76 <sect1 id="xproc">
77 <title>User-Defined Procedures</title>
78
79 <indexterm zone="xproc">
80 <primary>procedure</primary>
81 <secondary>user-defined</secondary>
82 </indexterm>
83
84 <para>
85 A procedure is a database object similar to a function.
86 The key differences are:
87
88 <itemizedlist>
89 <listitem>
90 <para>
91 Procedures are defined with
92 the <link linkend="sql-createprocedure"><command>CREATE
93 PROCEDURE</command></link> command, not <command>CREATE
94 FUNCTION</command>.
95 </para>
96 </listitem>
97 <listitem>
98 <para>
99 Procedures do not return a function value; hence <command>CREATE
100 PROCEDURE</command> lacks a <literal>RETURNS</literal> clause.
101 However, procedures can instead return data to their callers via
102 output parameters.
103 </para>
104 </listitem>
105 <listitem>
106 <para>
107 While a function is called as part of a query or DML command, a
108 procedure is called in isolation using
109 the <link linkend="sql-call"><command>CALL</command></link> command.
110 </para>
111 </listitem>
112 <listitem>
113 <para>
114 A procedure can commit or roll back transactions during its
115 execution (then automatically beginning a new transaction), so long
116 as the invoking <command>CALL</command> command is not part of an
117 explicit transaction block. A function cannot do that.
118 </para>
119 </listitem>
120 <listitem>
121 <para>
122 Certain function attributes, such as strictness, don't apply to
123 procedures. Those attributes control how the function is
124 used in a query, which isn't relevant to procedures.
125 </para>
126 </listitem>
127 </itemizedlist>
128 </para>
129
130 <para>
131 The explanations in the following sections about how to define
132 user-defined functions apply to procedures as well, except for the
133 points made above.
134 </para>
135
136 <para>
137 Collectively, functions and procedures are also known
138 as <firstterm>routines</firstterm><indexterm><primary>routine</primary></indexterm>.
139 There are commands such as <link linkend="sql-alterroutine"><command>ALTER ROUTINE</command></link>
140 and <link linkend="sql-droproutine"><command>DROP ROUTINE</command></link> that can operate on functions and
141 procedures without having to know which kind it is. Note, however, that
142 there is no <literal>CREATE ROUTINE</literal> command.
143 </para>
144 </sect1>
145
146 <sect1 id="xfunc-sql">
147 <title>Query Language (<acronym>SQL</acronym>) Functions</title>
148
149 <indexterm zone="xfunc-sql">
150 <primary>function</primary>
151 <secondary>user-defined</secondary>
152 <tertiary>in SQL</tertiary>
153 </indexterm>
154
155 <para>
156 SQL functions execute an arbitrary list of SQL statements, returning
157 the result of the last query in the list.
158 In the simple (non-set)
159 case, the first row of the last query's result will be returned.
160 (Bear in mind that <quote>the first row</quote> of a multirow
161 result is not well-defined unless you use <literal>ORDER BY</literal>.)
162 If the last query happens
163 to return no rows at all, the null value will be returned.
164 </para>
165
166 <para>
167 Alternatively, an SQL function can be declared to return a set (that is,
168 multiple rows) by specifying the function's return type as <literal>SETOF
169 <replaceable>sometype</replaceable></literal>, or equivalently by declaring it as
170 <literal>RETURNS TABLE(<replaceable>columns</replaceable>)</literal>. In this case
171 all rows of the last query's result are returned. Further details appear
172 below.
173 </para>
174
175 <para>
176 The body of an SQL function must be a list of SQL
177 statements separated by semicolons. A semicolon after the last
178 statement is optional. Unless the function is declared to return
179 <type>void</type>, the last statement must be a <command>SELECT</command>,
180 or an <command>INSERT</command>, <command>UPDATE</command>,
181 <command>DELETE</command>, or <command>MERGE</command>
182 that has a <literal>RETURNING</literal> clause.
183 </para>
184
185 <para>
186 Any collection of commands in the <acronym>SQL</acronym>
187 language can be packaged together and defined as a function.
188 Besides <command>SELECT</command> queries, the commands can include data
189 modification queries (<command>INSERT</command>,
190 <command>UPDATE</command>, <command>DELETE</command>, and
191 <command>MERGE</command>), as well as
192 other SQL commands. (You cannot use transaction control commands, e.g.,
193 <command>COMMIT</command>, <command>SAVEPOINT</command>, and some utility
194 commands, e.g., <literal>VACUUM</literal>, in <acronym>SQL</acronym> functions.)
195 However, the final command
196 must be a <command>SELECT</command> or have a <literal>RETURNING</literal>
197 clause that returns whatever is
198 specified as the function's return type. Alternatively, if you
199 want to define an SQL function that performs actions but has no
200 useful value to return, you can define it as returning <type>void</type>.
201 For example, this function removes rows with negative salaries from
202 the <literal>emp</literal> table:
203
204 <screen>
205 CREATE FUNCTION clean_emp() RETURNS void AS '
206 DELETE FROM emp
207 WHERE salary &lt; 0;
208 ' LANGUAGE SQL;
209
210 SELECT clean_emp();
211
212 clean_emp
213 -----------
214
215 (1 row)
216 </screen>
217 </para>
218
219 <para>
220 You can also write this as a procedure, thus avoiding the issue of the
221 return type. For example:
222 <screen>
223 CREATE PROCEDURE clean_emp() AS '
224 DELETE FROM emp
225 WHERE salary &lt; 0;
226 ' LANGUAGE SQL;
227
228 CALL clean_emp();
229 </screen>
230 In simple cases like this, the difference between a function returning
231 <type>void</type> and a procedure is mostly stylistic. However,
232 procedures offer additional functionality such as transaction control
233 that is not available in functions. Also, procedures are SQL standard
234 whereas returning <type>void</type> is a PostgreSQL extension.
235 </para>
236
237 <para>
238 The syntax of the <command>CREATE FUNCTION</command> command requires
239 the function body to be written as a string constant. It is usually
240 most convenient to use dollar quoting (see <xref
241 linkend="sql-syntax-dollar-quoting"/>) for the string constant.
242 If you choose to use regular single-quoted string constant syntax,
243 you must double single quote marks (<literal>'</literal>) and backslashes
244 (<literal>\</literal>) (assuming escape string syntax) in the body of
245 the function (see <xref linkend="sql-syntax-strings"/>).
246 </para>
247
248 <sect2 id="xfunc-sql-function-arguments">
249 <title>Arguments for <acronym>SQL</acronym> Functions</title>
250
251 <indexterm>
252 <primary>function</primary>
253 <secondary>named argument</secondary>
254 </indexterm>
255
256 <para>
257 Arguments of an SQL function can be referenced in the function
258 body using either names or numbers. Examples of both methods appear
259 below.
260 </para>
261
262 <para>
263 To use a name, declare the function argument as having a name, and
264 then just write that name in the function body. If the argument name
265 is the same as any column name in the current SQL command within the
266 function, the column name will take precedence. To override this,
267 qualify the argument name with the name of the function itself, that is
268 <literal><replaceable>function_name</replaceable>.<replaceable>argument_name</replaceable></literal>.
269 (If this would conflict with a qualified column name, again the column
270 name wins. You can avoid the ambiguity by choosing a different alias for
271 the table within the SQL command.)
272 </para>
273
274 <para>
275 In the older numeric approach, arguments are referenced using the syntax
276 <literal>$<replaceable>n</replaceable></literal>: <literal>$1</literal> refers to the first input
277 argument, <literal>$2</literal> to the second, and so on. This will work
278 whether or not the particular argument was declared with a name.
279 </para>
280
281 <para>
282 If an argument is of a composite type, then the dot notation,
283 e.g., <literal><replaceable>argname</replaceable>.<replaceable>fieldname</replaceable></literal> or
284 <literal>$1.<replaceable>fieldname</replaceable></literal>, can be used to access attributes of the
285 argument. Again, you might need to qualify the argument's name with the
286 function name to make the form with an argument name unambiguous.
287 </para>
288
289 <para>
290 SQL function arguments can only be used as data values,
291 not as identifiers. Thus for example this is reasonable:
292 <programlisting>
293 INSERT INTO mytable VALUES ($1);
294 </programlisting>
295 but this will not work:
296 <programlisting>
297 INSERT INTO $1 VALUES (42);
298 </programlisting>
299 </para>
300
301 <note>
302 <para>
303 The ability to use names to reference SQL function arguments was added
304 in <productname>PostgreSQL</productname> 9.2. Functions to be used in
305 older servers must use the <literal>$<replaceable>n</replaceable></literal> notation.
306 </para>
307 </note>
308 </sect2>
309
310 <sect2 id="xfunc-sql-base-functions">
311 <title><acronym>SQL</acronym> Functions on Base Types</title>
312
313 <para>
314 The simplest possible <acronym>SQL</acronym> function has no arguments and
315 simply returns a base type, such as <type>integer</type>:
316
317 <screen>
318 CREATE FUNCTION one() RETURNS integer AS $$
319 SELECT 1 AS result;
320 $$ LANGUAGE SQL;
321
322 -- Alternative syntax for string literal:
323 CREATE FUNCTION one() RETURNS integer AS '
324 SELECT 1 AS result;
325 ' LANGUAGE SQL;
326
327 SELECT one();
328
329 one
330 -----
331 1
332 </screen>
333 </para>
334
335 <para>
336 Notice that we defined a column alias within the function body for the result of the function
337 (with the name <literal>result</literal>), but this column alias is not visible
338 outside the function. Hence, the result is labeled <literal>one</literal>
339 instead of <literal>result</literal>.
340 </para>
341
342 <para>
343 It is almost as easy to define <acronym>SQL</acronym> functions
344 that take base types as arguments:
345
346 <screen>
347 CREATE FUNCTION add_em(x integer, y integer) RETURNS integer AS $$
348 SELECT x + y;
349 $$ LANGUAGE SQL;
350
351 SELECT add_em(1, 2) AS answer;
352
353 answer
354 --------
355 3
356 </screen>
357 </para>
358
359 <para>
360 Alternatively, we could dispense with names for the arguments and
361 use numbers:
362
363 <screen>
364 CREATE FUNCTION add_em(integer, integer) RETURNS integer AS $$
365 SELECT $1 + $2;
366 $$ LANGUAGE SQL;
367
368 SELECT add_em(1, 2) AS answer;
369
370 answer
371 --------
372 3
373 </screen>
374 </para>
375
376 <para>
377 Here is a more useful function, which might be used to debit a
378 bank account:
379
380 <programlisting>
381 CREATE FUNCTION tf1 (accountno integer, debit numeric) RETURNS numeric AS $$
382 UPDATE bank
383 SET balance = balance - debit
384 WHERE accountno = tf1.accountno;
385 SELECT 1;
386 $$ LANGUAGE SQL;
387 </programlisting>
388
389 A user could execute this function to debit account 17 by $100.00 as
390 follows:
391
392 <programlisting>
393 SELECT tf1(17, 100.0);
394 </programlisting>
395 </para>
396
397 <para>
398 In this example, we chose the name <literal>accountno</literal> for the first
399 argument, but this is the same as the name of a column in the
400 <literal>bank</literal> table. Within the <command>UPDATE</command> command,
401 <literal>accountno</literal> refers to the column <literal>bank.accountno</literal>,
402 so <literal>tf1.accountno</literal> must be used to refer to the argument.
403 We could of course avoid this by using a different name for the argument.
404 </para>
405
406 <para>
407 In practice one would probably like a more useful result from the
408 function than a constant 1, so a more likely definition
409 is:
410
411 <programlisting>
412 CREATE FUNCTION tf1 (accountno integer, debit numeric) RETURNS numeric AS $$
413 UPDATE bank
414 SET balance = balance - debit
415 WHERE accountno = tf1.accountno;
416 SELECT balance FROM bank WHERE accountno = tf1.accountno;
417 $$ LANGUAGE SQL;
418 </programlisting>
419
420 which adjusts the balance and returns the new balance.
421 The same thing could be done in one command using <literal>RETURNING</literal>:
422
423 <programlisting>
424 CREATE FUNCTION tf1 (accountno integer, debit numeric) RETURNS numeric AS $$
425 UPDATE bank
426 SET balance = balance - debit
427 WHERE accountno = tf1.accountno
428 RETURNING balance;
429 $$ LANGUAGE SQL;
430 </programlisting>
431 </para>
432
433 <para>
434 If the final <literal>SELECT</literal> or <literal>RETURNING</literal>
435 clause in an <acronym>SQL</acronym> function does not return exactly
436 the function's declared result
437 type, <productname>PostgreSQL</productname> will automatically cast
438 the value to the required type, if that is possible with an implicit
439 or assignment cast. Otherwise, you must write an explicit cast.
440 For example, suppose we wanted the
441 previous <function>add_em</function> function to return
442 type <type>float8</type> instead. It's sufficient to write
443
444 <programlisting>
445 CREATE FUNCTION add_em(integer, integer) RETURNS float8 AS $$
446 SELECT $1 + $2;
447 $$ LANGUAGE SQL;
448 </programlisting>
449
450 since the <type>integer</type> sum can be implicitly cast
451 to <type>float8</type>.
452 (See <xref linkend="typeconv"/> or <xref linkend="sql-createcast"/>
453 for more about casts.)
454 </para>
455 </sect2>
456
457 <sect2 id="xfunc-sql-composite-functions">
458 <title><acronym>SQL</acronym> Functions on Composite Types</title>
459
460 <para>
461 When writing functions with arguments of composite types, we must not
462 only specify which argument we want but also the desired attribute
463 (field) of that argument. For example, suppose that
464 <type>emp</type> is a table containing employee data, and therefore
465 also the name of the composite type of each row of the table. Here
466 is a function <function>double_salary</function> that computes what someone's
467 salary would be if it were doubled:
468
469 <screen>
470 CREATE TABLE emp (
471 name text,
472 salary numeric,
473 age integer,
474 cubicle point
475 );
476
477 INSERT INTO emp VALUES ('Bill', 4200, 45, '(2,1)');
478
479 CREATE FUNCTION double_salary(emp) RETURNS numeric AS $$
480 SELECT $1.salary * 2 AS salary;
481 $$ LANGUAGE SQL;
482
483 SELECT name, double_salary(emp.*) AS dream
484 FROM emp
485 WHERE emp.cubicle ~= point '(2,1)';
486
487 name | dream
488 ------+-------
489 Bill | 8400
490 </screen>
491 </para>
492
493 <para>
494 Notice the use of the syntax <literal>$1.salary</literal>
495 to select one field of the argument row value. Also notice
496 how the calling <command>SELECT</command> command
497 uses <replaceable>table_name</replaceable><literal>.*</literal> to select
498 the entire current row of a table as a composite value. The table
499 row can alternatively be referenced using just the table name,
500 like this:
501 <screen>
502 SELECT name, double_salary(emp) AS dream
503 FROM emp
504 WHERE emp.cubicle ~= point '(2,1)';
505 </screen>
506 but this usage is deprecated since it's easy to get confused.
507 (See <xref linkend="rowtypes-usage"/> for details about these
508 two notations for the composite value of a table row.)
509 </para>
510
511 <para>
512 Sometimes it is handy to construct a composite argument value
513 on-the-fly. This can be done with the <literal>ROW</literal> construct.
514 For example, we could adjust the data being passed to the function:
515 <screen>
516 SELECT name, double_salary(ROW(name, salary*1.1, age, cubicle)) AS dream
517 FROM emp;
518 </screen>
519 </para>
520
521 <para>
522 It is also possible to build a function that returns a composite type.
523 This is an example of a function
524 that returns a single <type>emp</type> row:
525
526 <programlisting>
527 CREATE FUNCTION new_emp() RETURNS emp AS $$
528 SELECT text 'None' AS name,
529 1000.0 AS salary,
530 25 AS age,
531 point '(2,2)' AS cubicle;
532 $$ LANGUAGE SQL;
533 </programlisting>
534
535 In this example we have specified each of the attributes
536 with a constant value, but any computation
537 could have been substituted for these constants.
538 </para>
539
540 <para>
541 Note two important things about defining the function:
542
543 <itemizedlist>
544 <listitem>
545 <para>
546 The select list order in the query must be exactly the same as
547 that in which the columns appear in the composite type.
548 (Naming the columns, as we did above,
549 is irrelevant to the system.)
550 </para>
551 </listitem>
552 <listitem>
553 <para>
554 We must ensure each expression's type can be cast to that of
555 the corresponding column of the composite type.
556 Otherwise we'll get errors like this:
557 <screen>
558 <computeroutput>
559 ERROR: return type mismatch in function declared to return emp
560 DETAIL: Final statement returns text instead of point at column 4.
561 </computeroutput>
562 </screen>
563 As with the base-type case, the system will not insert explicit
564 casts automatically, only implicit or assignment casts.
565 </para>
566 </listitem>
567 </itemizedlist>
568 </para>
569
570 <para>
571 A different way to define the same function is:
572
573 <programlisting>
574 CREATE FUNCTION new_emp() RETURNS emp AS $$
575 SELECT ROW('None', 1000.0, 25, '(2,2)')::emp;
576 $$ LANGUAGE SQL;
577 </programlisting>
578
579 Here we wrote a <command>SELECT</command> that returns just a single
580 column of the correct composite type. This isn't really better
581 in this situation, but it is a handy alternative in some cases
582 &mdash; for example, if we need to compute the result by calling
583 another function that returns the desired composite value.
584 Another example is that if we are trying to write a function that
585 returns a domain over composite, rather than a plain composite type,
586 it is always necessary to write it as returning a single column,
587 since there is no way to cause a coercion of the whole row result.
588 </para>
589
590 <para>
591 We could call this function directly either by using it in
592 a value expression:
593
594 <screen>
595 SELECT new_emp();
596
597 new_emp
598 --------------------------
599 (None,1000.0,25,"(2,2)")
600 </screen>
601
602 or by calling it as a table function:
603
604 <screen>
605 SELECT * FROM new_emp();
606
607 name | salary | age | cubicle
608 ------+--------+-----+---------
609 None | 1000.0 | 25 | (2,2)
610 </screen>
611
612 The second way is described more fully in <xref
613 linkend="xfunc-sql-table-functions"/>.
614 </para>
615
616 <para>
617 When you use a function that returns a composite type,
618 you might want only one field (attribute) from its result.
619 You can do that with syntax like this:
620
621 <screen>
622 SELECT (new_emp()).name;
623
624 name
625 ------
626 None
627 </screen>
628
629 The extra parentheses are needed to keep the parser from getting
630 confused. If you try to do it without them, you get something like this:
631
632 <screen>
633 SELECT new_emp().name;
634 ERROR: syntax error at or near "."
635 LINE 1: SELECT new_emp().name;
636 ^
637 </screen>
638 </para>
639
640 <para>
641 Another option is to use functional notation for extracting an attribute:
642
643 <screen>
644 SELECT name(new_emp());
645
646 name
647 ------
648 None
649 </screen>
650
651 As explained in <xref linkend="rowtypes-usage"/>, the field notation and
652 functional notation are equivalent.
653 </para>
654
655 <para>
656 Another way to use a function returning a composite type is to pass the
657 result to another function that accepts the correct row type as input:
658
659 <screen>
660 CREATE FUNCTION getname(emp) RETURNS text AS $$
661 SELECT $1.name;
662 $$ LANGUAGE SQL;
663
664 SELECT getname(new_emp());
665 getname
666 ---------
667 None
668 (1 row)
669 </screen>
670 </para>
671 </sect2>
672
673 <sect2 id="xfunc-output-parameters">
674 <title><acronym>SQL</acronym> Functions with Output Parameters</title>
675
676 <indexterm>
677 <primary>function</primary>
678 <secondary>output parameter</secondary>
679 </indexterm>
680
681 <para>
682 An alternative way of describing a function's results is to define it
683 with <firstterm>output parameters</firstterm>, as in this example:
684
685 <screen>
686 CREATE FUNCTION add_em (IN x int, IN y int, OUT sum int)
687 AS 'SELECT x + y'
688 LANGUAGE SQL;
689
690 SELECT add_em(3,7);
691 add_em
692 --------
693 10
694 (1 row)
695 </screen>
696
697 This is not essentially different from the version of <literal>add_em</literal>
698 shown in <xref linkend="xfunc-sql-base-functions"/>. The real value of
699 output parameters is that they provide a convenient way of defining
700 functions that return several columns. For example,
701
702 <screen>
703 CREATE FUNCTION sum_n_product (x int, y int, OUT sum int, OUT product int)
704 AS 'SELECT x + y, x * y'
705 LANGUAGE SQL;
706
707 SELECT * FROM sum_n_product(11,42);
708 sum | product
709 -----+---------
710 53 | 462
711 (1 row)
712 </screen>
713
714 What has essentially happened here is that we have created an anonymous
715 composite type for the result of the function. The above example has
716 the same end result as
717
718 <screen>
719 CREATE TYPE sum_prod AS (sum int, product int);
720
721 CREATE FUNCTION sum_n_product (int, int) RETURNS sum_prod
722 AS 'SELECT $1 + $2, $1 * $2'
723 LANGUAGE SQL;
724 </screen>
725
726 but not having to bother with the separate composite type definition
727 is often handy. Notice that the names attached to the output parameters
728 are not just decoration, but determine the column names of the anonymous
729 composite type. (If you omit a name for an output parameter, the
730 system will choose a name on its own.)
731 </para>
732
733 <para>
734 Notice that output parameters are not included in the calling argument
735 list when invoking such a function from SQL. This is because
736 <productname>PostgreSQL</productname> considers only the input
737 parameters to define the function's calling signature. That means
738 also that only the input parameters matter when referencing the function
739 for purposes such as dropping it. We could drop the above function
740 with either of
741
742 <screen>
743 DROP FUNCTION sum_n_product (x int, y int, OUT sum int, OUT product int);
744 DROP FUNCTION sum_n_product (int, int);
745 </screen>
746 </para>
747
748 <para>
749 Parameters can be marked as <literal>IN</literal> (the default),
750 <literal>OUT</literal>, <literal>INOUT</literal>, or <literal>VARIADIC</literal>.
751 An <literal>INOUT</literal>
752 parameter serves as both an input parameter (part of the calling
753 argument list) and an output parameter (part of the result record type).
754 <literal>VARIADIC</literal> parameters are input parameters, but are treated
755 specially as described below.
756 </para>
757 </sect2>
758
759 <sect2 id="xfunc-output-parameters-proc">
760 <title><acronym>SQL</acronym> Procedures with Output Parameters</title>
761
762 <indexterm>
763 <primary>procedures</primary>
764 <secondary>output parameter</secondary>
765 </indexterm>
766
767 <para>
768 Output parameters are also supported in procedures, but they work a bit
769 differently from functions. In <command>CALL</command> commands,
770 output parameters must be included in the argument list.
771 For example, the bank account debiting routine from earlier could be
772 written like this:
773 <programlisting>
774 CREATE PROCEDURE tp1 (accountno integer, debit numeric, OUT new_balance numeric) AS $$
775 UPDATE bank
776 SET balance = balance - debit
777 WHERE accountno = tp1.accountno
778 RETURNING balance;
779 $$ LANGUAGE SQL;
780 </programlisting>
781 To call this procedure, an argument matching the <literal>OUT</literal>
782 parameter must be included. It's customary to write
783 <literal>NULL</literal>:
784 <programlisting>
785 CALL tp1(17, 100.0, NULL);
786 </programlisting>
787 If you write something else, it must be an expression that is implicitly
788 coercible to the declared type of the parameter, just as for input
789 parameters. Note however that such an expression will not be evaluated.
790 </para>
791
792 <para>
793 When calling a procedure from <application>PL/pgSQL</application>,
794 instead of writing <literal>NULL</literal> you must write a variable
795 that will receive the procedure's output. See <xref
796 linkend="plpgsql-statements-calling-procedure"/> for details.
797 </para>
798 </sect2>
799
800 <sect2 id="xfunc-sql-variadic-functions">
801 <title><acronym>SQL</acronym> Functions with Variable Numbers of Arguments</title>
802
803 <indexterm>
804 <primary>function</primary>
805 <secondary>variadic</secondary>
806 </indexterm>
807
808 <indexterm>
809 <primary>variadic function</primary>
810 </indexterm>
811
812 <para>
813 <acronym>SQL</acronym> functions can be declared to accept
814 variable numbers of arguments, so long as all the <quote>optional</quote>
815 arguments are of the same data type. The optional arguments will be
816 passed to the function as an array. The function is declared by
817 marking the last parameter as <literal>VARIADIC</literal>; this parameter
818 must be declared as being of an array type. For example:
819
820 <screen>
821 CREATE FUNCTION mleast(VARIADIC arr numeric[]) RETURNS numeric AS $$
822 SELECT min($1[i]) FROM generate_subscripts($1, 1) g(i);
823 $$ LANGUAGE SQL;
824
825 SELECT mleast(10, -1, 5, 4.4);
826 mleast
827 --------
828 -1
829 (1 row)
830 </screen>
831
832 Effectively, all the actual arguments at or beyond the
833 <literal>VARIADIC</literal> position are gathered up into a one-dimensional
834 array, as if you had written
835
836 <screen>
837 SELECT mleast(ARRAY[10, -1, 5, 4.4]); -- doesn't work
838 </screen>
839
840 You can't actually write that, though &mdash; or at least, it will
841 not match this function definition. A parameter marked
842 <literal>VARIADIC</literal> matches one or more occurrences of its element
843 type, not of its own type.
844 </para>
845
846 <para>
847 Sometimes it is useful to be able to pass an already-constructed array
848 to a variadic function; this is particularly handy when one variadic
849 function wants to pass on its array parameter to another one. Also,
850 this is the only secure way to call a variadic function found in a schema
851 that permits untrusted users to create objects; see
852 <xref linkend="typeconv-func"/>. You can do this by
853 specifying <literal>VARIADIC</literal> in the call:
854
855 <screen>
856 SELECT mleast(VARIADIC ARRAY[10, -1, 5, 4.4]);
857 </screen>
858
859 This prevents expansion of the function's variadic parameter into its
860 element type, thereby allowing the array argument value to match
861 normally. <literal>VARIADIC</literal> can only be attached to the last
862 actual argument of a function call.
863 </para>
864
865 <para>
866 Specifying <literal>VARIADIC</literal> in the call is also the only way to
867 pass an empty array to a variadic function, for example:
868
869 <screen>
870 SELECT mleast(VARIADIC ARRAY[]::numeric[]);
871 </screen>
872
873 Simply writing <literal>SELECT mleast()</literal> does not work because a
874 variadic parameter must match at least one actual argument.
875 (You could define a second function also named <literal>mleast</literal>,
876 with no parameters, if you wanted to allow such calls.)
877 </para>
878
879 <para>
880 The array element parameters generated from a variadic parameter are
881 treated as not having any names of their own. This means it is not
882 possible to call a variadic function using named arguments (<xref
883 linkend="sql-syntax-calling-funcs"/>), except when you specify
884 <literal>VARIADIC</literal>. For example, this will work:
885
886 <screen>
887 SELECT mleast(VARIADIC arr =&gt; ARRAY[10, -1, 5, 4.4]);
888 </screen>
889
890 but not these:
891
892 <screen>
893 SELECT mleast(arr =&gt; 10);
894 SELECT mleast(arr =&gt; ARRAY[10, -1, 5, 4.4]);
895 </screen>
896 </para>
897 </sect2>
898
899 <sect2 id="xfunc-sql-parameter-defaults">
900 <title><acronym>SQL</acronym> Functions with Default Values for Arguments</title>
901
902 <indexterm>
903 <primary>function</primary>
904 <secondary>default values for arguments</secondary>
905 </indexterm>
906
907 <para>
908 Functions can be declared with default values for some or all input
909 arguments. The default values are inserted whenever the function is
910 called with insufficiently many actual arguments. Since arguments
911 can only be omitted from the end of the actual argument list, all
912 parameters after a parameter with a default value have to have
913 default values as well. (Although the use of named argument notation
914 could allow this restriction to be relaxed, it's still enforced so that
915 positional argument notation works sensibly.) Whether or not you use it,
916 this capability creates a need for precautions when calling functions in
917 databases where some users mistrust other users; see
918 <xref linkend="typeconv-func"/>.
919 </para>
920
921 <para>
922 For example:
923 <screen>
924 CREATE FUNCTION foo(a int, b int DEFAULT 2, c int DEFAULT 3)
925 RETURNS int
926 LANGUAGE SQL
927 AS $$
928 SELECT $1 + $2 + $3;
929 $$;
930
931 SELECT foo(10, 20, 30);
932 foo
933 -----
934 60
935 (1 row)
936
937 SELECT foo(10, 20);
938 foo
939 -----
940 33
941 (1 row)
942
943 SELECT foo(10);
944 foo
945 -----
946 15
947 (1 row)
948
949 SELECT foo(); -- fails since there is no default for the first argument
950 ERROR: function foo() does not exist
951 </screen>
952 The <literal>=</literal> sign can also be used in place of the
953 key word <literal>DEFAULT</literal>.
954 </para>
955 </sect2>
956
957 <sect2 id="xfunc-sql-table-functions">
958 <title><acronym>SQL</acronym> Functions as Table Sources</title>
959
960 <para>
961 All SQL functions can be used in the <literal>FROM</literal> clause of a query,
962 but it is particularly useful for functions returning composite types.
963 If the function is defined to return a base type, the table function
964 produces a one-column table. If the function is defined to return
965 a composite type, the table function produces a column for each attribute
966 of the composite type.
967 </para>
968
969 <para>
970 Here is an example:
971
972 <screen>
973 CREATE TABLE foo (fooid int, foosubid int, fooname text);
974 INSERT INTO foo VALUES (1, 1, 'Joe');
975 INSERT INTO foo VALUES (1, 2, 'Ed');
976 INSERT INTO foo VALUES (2, 1, 'Mary');
977
978 CREATE FUNCTION getfoo(int) RETURNS foo AS $$
979 SELECT * FROM foo WHERE fooid = $1;
980 $$ LANGUAGE SQL;
981
982 SELECT *, upper(fooname) FROM getfoo(1) AS t1;
983
984 fooid | foosubid | fooname | upper
985 -------+----------+---------+-------
986 1 | 1 | Joe | JOE
987 (1 row)
988 </screen>
989
990 As the example shows, we can work with the columns of the function's
991 result just the same as if they were columns of a regular table.
992 </para>
993
994 <para>
995 Note that we only got one row out of the function. This is because
996 we did not use <literal>SETOF</literal>. That is described in the next section.
997 </para>
998 </sect2>
999
1000 <sect2 id="xfunc-sql-functions-returning-set">
1001 <title><acronym>SQL</acronym> Functions Returning Sets</title>
1002
1003 <indexterm>
1004 <primary>function</primary>
1005 <secondary>with SETOF</secondary>
1006 </indexterm>
1007
1008 <para>
1009 When an SQL function is declared as returning <literal>SETOF
1010 <replaceable>sometype</replaceable></literal>, the function's final
1011 query is executed to completion, and each row it
1012 outputs is returned as an element of the result set.
1013 </para>
1014
1015 <para>
1016 This feature is normally used when calling the function in the <literal>FROM</literal>
1017 clause. In this case each row returned by the function becomes
1018 a row of the table seen by the query. For example, assume that
1019 table <literal>foo</literal> has the same contents as above, and we say:
1020
1021 <programlisting>
1022 CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
1023 SELECT * FROM foo WHERE fooid = $1;
1024 $$ LANGUAGE SQL;
1025
1026 SELECT * FROM getfoo(1) AS t1;
1027 </programlisting>
1028
1029 Then we would get:
1030 <screen>
1031 fooid | foosubid | fooname
1032 -------+----------+---------
1033 1 | 1 | Joe
1034 1 | 2 | Ed
1035 (2 rows)
1036 </screen>
1037 </para>
1038
1039 <para>
1040 It is also possible to return multiple rows with the columns defined by
1041 output parameters, like this:
1042
1043 <programlisting>
1044 CREATE TABLE tab (y int, z int);
1045 INSERT INTO tab VALUES (1, 2), (3, 4), (5, 6), (7, 8);
1046
1047 CREATE FUNCTION sum_n_product_with_tab (x int, OUT sum int, OUT product int)
1048 RETURNS SETOF record
1049 AS $$
1050 SELECT $1 + tab.y, $1 * tab.y FROM tab;
1051 $$ LANGUAGE SQL;
1052
1053 SELECT * FROM sum_n_product_with_tab(10);
1054 sum | product
1055 -----+---------
1056 11 | 10
1057 13 | 30
1058 15 | 50
1059 17 | 70
1060 (4 rows)
1061 </programlisting>
1062
1063 The key point here is that you must write <literal>RETURNS SETOF record</literal>
1064 to indicate that the function returns multiple rows instead of just one.
1065 If there is only one output parameter, write that parameter's type
1066 instead of <type>record</type>.
1067 </para>
1068
1069 <para>
1070 It is frequently useful to construct a query's result by invoking a
1071 set-returning function multiple times, with the parameters for each
1072 invocation coming from successive rows of a table or subquery. The
1073 preferred way to do this is to use the <literal>LATERAL</literal> key word,
1074 which is described in <xref linkend="queries-lateral"/>.
1075 Here is an example using a set-returning function to enumerate
1076 elements of a tree structure:
1077
1078 <screen>
1079 SELECT * FROM nodes;
1080 name | parent
1081 -----------+--------
1082 Top |
1083 Child1 | Top
1084 Child2 | Top
1085 Child3 | Top
1086 SubChild1 | Child1
1087 SubChild2 | Child1
1088 (6 rows)
1089
1090 CREATE FUNCTION listchildren(text) RETURNS SETOF text AS $$
1091 SELECT name FROM nodes WHERE parent = $1
1092 $$ LANGUAGE SQL STABLE;
1093
1094 SELECT * FROM listchildren('Top');
1095 listchildren
1096 --------------
1097 Child1
1098 Child2
1099 Child3
1100 (3 rows)
1101
1102 SELECT name, child FROM nodes, LATERAL listchildren(name) AS child;
1103 name | child
1104 --------+-----------
1105 Top | Child1
1106 Top | Child2
1107 Top | Child3
1108 Child1 | SubChild1
1109 Child1 | SubChild2
1110 (5 rows)
1111 </screen>
1112
1113 This example does not do anything that we couldn't have done with a
1114 simple join, but in more complex calculations the option to put
1115 some of the work into a function can be quite convenient.
1116 </para>
1117
1118 <para>
1119 Functions returning sets can also be called in the select list
1120 of a query. For each row that the query
1121 generates by itself, the set-returning function is invoked, and an output
1122 row is generated for each element of the function's result set.
1123 The previous example could also be done with queries like
1124 these:
1125
1126 <screen>
1127 SELECT listchildren('Top');
1128 listchildren
1129 --------------
1130 Child1
1131 Child2
1132 Child3
1133 (3 rows)
1134
1135 SELECT name, listchildren(name) FROM nodes;
1136 name | listchildren
1137 --------+--------------
1138 Top | Child1
1139 Top | Child2
1140 Top | Child3
1141 Child1 | SubChild1
1142 Child1 | SubChild2
1143 (5 rows)
1144 </screen>
1145
1146 In the last <command>SELECT</command>,
1147 notice that no output row appears for <literal>Child2</literal>, <literal>Child3</literal>, etc.
1148 This happens because <function>listchildren</function> returns an empty set
1149 for those arguments, so no result rows are generated. This is the same
1150 behavior as we got from an inner join to the function result when using
1151 the <literal>LATERAL</literal> syntax.
1152 </para>
1153
1154 <para>
1155 <productname>PostgreSQL</productname>'s behavior for a set-returning function in a
1156 query's select list is almost exactly the same as if the set-returning
1157 function had been written in a <literal>LATERAL FROM</literal>-clause item
1158 instead. For example,
1159 <programlisting>
1160 SELECT x, generate_series(1,5) AS g FROM tab;
1161 </programlisting>
1162 is almost equivalent to
1163 <programlisting>
1164 SELECT x, g FROM tab, LATERAL generate_series(1,5) AS g;
1165 </programlisting>
1166 It would be exactly the same, except that in this specific example,
1167 the planner could choose to put <structname>g</structname> on the outside of the
1168 nested-loop join, since <structname>g</structname> has no actual lateral dependency
1169 on <structname>tab</structname>. That would result in a different output row
1170 order. Set-returning functions in the select list are always evaluated
1171 as though they are on the inside of a nested-loop join with the rest of
1172 the <literal>FROM</literal> clause, so that the function(s) are run to
1173 completion before the next row from the <literal>FROM</literal> clause is
1174 considered.
1175 </para>
1176
1177 <para>
1178 If there is more than one set-returning function in the query's select
1179 list, the behavior is similar to what you get from putting the functions
1180 into a single <literal>LATERAL ROWS FROM( ... )</literal> <literal>FROM</literal>-clause
1181 item. For each row from the underlying query, there is an output row
1182 using the first result from each function, then an output row using the
1183 second result, and so on. If some of the set-returning functions
1184 produce fewer outputs than others, null values are substituted for the
1185 missing data, so that the total number of rows emitted for one
1186 underlying row is the same as for the set-returning function that
1187 produced the most outputs. Thus the set-returning functions
1188 run <quote>in lockstep</quote> until they are all exhausted, and then
1189 execution continues with the next underlying row.
1190 </para>
1191
1192 <para>
1193 Set-returning functions can be nested in a select list, although that is
1194 not allowed in <literal>FROM</literal>-clause items. In such cases, each level
1195 of nesting is treated separately, as though it were
1196 a separate <literal>LATERAL ROWS FROM( ... )</literal> item. For example, in
1197 <programlisting>
1198 SELECT srf1(srf2(x), srf3(y)), srf4(srf5(z)) FROM tab;
1199 </programlisting>
1200 the set-returning functions <function>srf2</function>, <function>srf3</function>,
1201 and <function>srf5</function> would be run in lockstep for each row
1202 of <structname>tab</structname>, and then <function>srf1</function> and <function>srf4</function>
1203 would be applied in lockstep to each row produced by the lower
1204 functions.
1205 </para>
1206
1207 <para>
1208 Set-returning functions cannot be used within conditional-evaluation
1209 constructs, such as <literal>CASE</literal> or <literal>COALESCE</literal>. For
1210 example, consider
1211 <programlisting>
1212 SELECT x, CASE WHEN x &gt; 0 THEN generate_series(1, 5) ELSE 0 END FROM tab;
1213 </programlisting>
1214 It might seem that this should produce five repetitions of input rows
1215 that have <literal>x &gt; 0</literal>, and a single repetition of those that do
1216 not; but actually, because <function>generate_series(1, 5)</function> would be
1217 run in an implicit <literal>LATERAL FROM</literal> item before
1218 the <literal>CASE</literal> expression is ever evaluated, it would produce five
1219 repetitions of every input row. To reduce confusion, such cases produce
1220 a parse-time error instead.
1221 </para>
1222
1223 <note>
1224 <para>
1225 If a function's last command is <command>INSERT</command>,
1226 <command>UPDATE</command>, <command>DELETE</command>, or
1227 <command>MERGE</command> with <literal>RETURNING</literal>, that command will
1228 always be executed to completion, even if the function is not declared
1229 with <literal>SETOF</literal> or the calling query does not fetch all the
1230 result rows. Any extra rows produced by the <literal>RETURNING</literal>
1231 clause are silently dropped, but the commanded table modifications
1232 still happen (and are all completed before returning from the function).
1233 </para>
1234 </note>
1235
1236 <note>
1237 <para>
1238 Before <productname>PostgreSQL</productname> 10, putting more than one
1239 set-returning function in the same select list did not behave very
1240 sensibly unless they always produced equal numbers of rows. Otherwise,
1241 what you got was a number of output rows equal to the least common
1242 multiple of the numbers of rows produced by the set-returning
1243 functions. Also, nested set-returning functions did not work as
1244 described above; instead, a set-returning function could have at most
1245 one set-returning argument, and each nest of set-returning functions
1246 was run independently. Also, conditional execution (set-returning
1247 functions inside <literal>CASE</literal> etc.) was previously allowed,
1248 complicating things even more.
1249 Use of the <literal>LATERAL</literal> syntax is recommended when writing
1250 queries that need to work in older <productname>PostgreSQL</productname> versions,
1251 because that will give consistent results across different versions.
1252 If you have a query that is relying on conditional execution of a
1253 set-returning function, you may be able to fix it by moving the
1254 conditional test into a custom set-returning function. For example,
1255 <programlisting>
1256 SELECT x, CASE WHEN y &gt; 0 THEN generate_series(1, z) ELSE 5 END FROM tab;
1257 </programlisting>
1258 could become
1259 <programlisting>
1260 CREATE FUNCTION case_generate_series(cond bool, start int, fin int, els int)
1261 RETURNS SETOF int AS $$
1262 BEGIN
1263 IF cond THEN
1264 RETURN QUERY SELECT generate_series(start, fin);
1265 ELSE
1266 RETURN QUERY SELECT els;
1267 END IF;
1268 END$$ LANGUAGE plpgsql;
1269
1270 SELECT x, case_generate_series(y &gt; 0, 1, z, 5) FROM tab;
1271 </programlisting>
1272 This formulation will work the same in all versions
1273 of <productname>PostgreSQL</productname>.
1274 </para>
1275 </note>
1276 </sect2>
1277
1278 <sect2 id="xfunc-sql-functions-returning-table">
1279 <title><acronym>SQL</acronym> Functions Returning <literal>TABLE</literal></title>
1280
1281 <indexterm>
1282 <primary>function</primary>
1283 <secondary>RETURNS TABLE</secondary>
1284 </indexterm>
1285
1286 <para>
1287 There is another way to declare a function as returning a set,
1288 which is to use the syntax
1289 <literal>RETURNS TABLE(<replaceable>columns</replaceable>)</literal>.
1290 This is equivalent to using one or more <literal>OUT</literal> parameters plus
1291 marking the function as returning <literal>SETOF record</literal> (or
1292 <literal>SETOF</literal> a single output parameter's type, as appropriate).
1293 This notation is specified in recent versions of the SQL standard, and
1294 thus may be more portable than using <literal>SETOF</literal>.
1295 </para>
1296
1297 <para>
1298 For example, the preceding sum-and-product example could also be
1299 done this way:
1300
1301 <programlisting>
1302 CREATE FUNCTION sum_n_product_with_tab (x int)
1303 RETURNS TABLE(sum int, product int) AS $$
1304 SELECT $1 + tab.y, $1 * tab.y FROM tab;
1305 $$ LANGUAGE SQL;
1306 </programlisting>
1307
1308 It is not allowed to use explicit <literal>OUT</literal> or <literal>INOUT</literal>
1309 parameters with the <literal>RETURNS TABLE</literal> notation &mdash; you must
1310 put all the output columns in the <literal>TABLE</literal> list.
1311 </para>
1312 </sect2>
1313
1314 <sect2 id="xfunc-sql-polymorphic-functions">
1315 <title>Polymorphic <acronym>SQL</acronym> Functions</title>
1316
1317 <para>
1318 <acronym>SQL</acronym> functions can be declared to accept and
1319 return the polymorphic types described in <xref
1320 linkend="extend-types-polymorphic"/>. Here is a polymorphic
1321 function <function>make_array</function> that builds up an array
1322 from two arbitrary data type elements:
1323 <screen>
1324 CREATE FUNCTION make_array(anyelement, anyelement) RETURNS anyarray AS $$
1325 SELECT ARRAY[$1, $2];
1326 $$ LANGUAGE SQL;
1327
1328 SELECT make_array(1, 2) AS intarray, make_array('a'::text, 'b') AS textarray;
1329 intarray | textarray
1330 ----------+-----------
1331 {1,2} | {a,b}
1332 (1 row)
1333 </screen>
1334 </para>
1335
1336 <para>
1337 Notice the use of the typecast <literal>'a'::text</literal>
1338 to specify that the argument is of type <type>text</type>. This is
1339 required if the argument is just a string literal, since otherwise
1340 it would be treated as type
1341 <type>unknown</type>, and array of <type>unknown</type> is not a valid
1342 type.
1343 Without the typecast, you will get errors like this:
1344 <screen>
1345 ERROR: could not determine polymorphic type because input has type unknown
1346 </screen>
1347 </para>
1348
1349 <para>
1350 With <function>make_array</function> declared as above, you must
1351 provide two arguments that are of exactly the same data type; the
1352 system will not attempt to resolve any type differences. Thus for
1353 example this does not work:
1354 <screen>
1355 SELECT make_array(1, 2.5) AS numericarray;
1356 ERROR: function make_array(integer, numeric) does not exist
1357 </screen>
1358 An alternative approach is to use the <quote>common</quote> family of
1359 polymorphic types, which allows the system to try to identify a
1360 suitable common type:
1361 <screen>
1362 CREATE FUNCTION make_array2(anycompatible, anycompatible)
1363 RETURNS anycompatiblearray AS $$
1364 SELECT ARRAY[$1, $2];
1365 $$ LANGUAGE SQL;
1366
1367 SELECT make_array2(1, 2.5) AS numericarray;
1368 numericarray
1369 --------------
1370 {1,2.5}
1371 (1 row)
1372 </screen>
1373 Because the rules for common type resolution default to choosing
1374 type <type>text</type> when all inputs are of unknown types, this
1375 also works:
1376 <screen>
1377 SELECT make_array2('a', 'b') AS textarray;
1378 textarray
1379 -----------
1380 {a,b}
1381 (1 row)
1382 </screen>
1383 </para>
1384
1385 <para>
1386 It is permitted to have polymorphic arguments with a fixed
1387 return type, but the converse is not. For example:
1388 <screen>
1389 CREATE FUNCTION is_greater(anyelement, anyelement) RETURNS boolean AS $$
1390 SELECT $1 &gt; $2;
1391 $$ LANGUAGE SQL;
1392
1393 SELECT is_greater(1, 2);
1394 is_greater
1395 ------------
1396 f
1397 (1 row)
1398
1399 CREATE FUNCTION invalid_func() RETURNS anyelement AS $$
1400 SELECT 1;
1401 $$ LANGUAGE SQL;
1402 ERROR: cannot determine result data type
1403 DETAIL: A result of type anyelement requires at least one input of type anyelement, anyarray, anynonarray, anyenum, or anyrange.
1404 </screen>
1405 </para>
1406
1407 <para>
1408 Polymorphism can be used with functions that have output arguments.
1409 For example:
1410 <screen>
1411 CREATE FUNCTION dup (f1 anyelement, OUT f2 anyelement, OUT f3 anyarray)
1412 AS 'select $1, array[$1,$1]' LANGUAGE SQL;
1413
1414 SELECT * FROM dup(22);
1415 f2 | f3
1416 ----+---------
1417 22 | {22,22}
1418 (1 row)
1419 </screen>
1420 </para>
1421
1422 <para>
1423 Polymorphism can also be used with variadic functions.
1424 For example:
1425 <screen>
1426 CREATE FUNCTION anyleast (VARIADIC anyarray) RETURNS anyelement AS $$
1427 SELECT min($1[i]) FROM generate_subscripts($1, 1) g(i);
1428 $$ LANGUAGE SQL;
1429
1430 SELECT anyleast(10, -1, 5, 4);
1431 anyleast
1432 ----------
1433 -1
1434 (1 row)
1435
1436 SELECT anyleast('abc'::text, 'def');
1437 anyleast
1438 ----------
1439 abc
1440 (1 row)
1441
1442 CREATE FUNCTION concat_values(text, VARIADIC anyarray) RETURNS text AS $$
1443 SELECT array_to_string($2, $1);
1444 $$ LANGUAGE SQL;
1445
1446 SELECT concat_values('|', 1, 4, 2);
1447 concat_values
1448 ---------------
1449 1|4|2
1450 (1 row)
1451 </screen>
1452 </para>
1453 </sect2>
1454
1455 <sect2 id="xfunc-sql-collations">
1456 <title><acronym>SQL</acronym> Functions with Collations</title>
1457
1458 <indexterm>
1459 <primary>collation</primary>
1460 <secondary>in SQL functions</secondary>
1461 </indexterm>
1462
1463 <para>
1464 When an SQL function has one or more parameters of collatable data types,
1465 a collation is identified for each function call depending on the
1466 collations assigned to the actual arguments, as described in <xref
1467 linkend="collation"/>. If a collation is successfully identified
1468 (i.e., there are no conflicts of implicit collations among the arguments)
1469 then all the collatable parameters are treated as having that collation
1470 implicitly. This will affect the behavior of collation-sensitive
1471 operations within the function. For example, using the
1472 <function>anyleast</function> function described above, the result of
1473 <programlisting>
1474 SELECT anyleast('abc'::text, 'ABC');
1475 </programlisting>
1476 will depend on the database's default collation. In <literal>C</literal> locale
1477 the result will be <literal>ABC</literal>, but in many other locales it will
1478 be <literal>abc</literal>. The collation to use can be forced by adding
1479 a <literal>COLLATE</literal> clause to any of the arguments, for example
1480 <programlisting>
1481 SELECT anyleast('abc'::text, 'ABC' COLLATE "C");
1482 </programlisting>
1483 Alternatively, if you wish a function to operate with a particular
1484 collation regardless of what it is called with, insert
1485 <literal>COLLATE</literal> clauses as needed in the function definition.
1486 This version of <function>anyleast</function> would always use <literal>en_US</literal>
1487 locale to compare strings:
1488 <programlisting>
1489 CREATE FUNCTION anyleast (VARIADIC anyarray) RETURNS anyelement AS $$
1490 SELECT min($1[i] COLLATE "en_US") FROM generate_subscripts($1, 1) g(i);
1491 $$ LANGUAGE SQL;
1492 </programlisting>
1493 But note that this will throw an error if applied to a non-collatable
1494 data type.
1495 </para>
1496
1497 <para>
1498 If no common collation can be identified among the actual arguments,
1499 then an SQL function treats its parameters as having their data types'
1500 default collation (which is usually the database's default collation,
1501 but could be different for parameters of domain types).
1502 </para>
1503
1504 <para>
1505 The behavior of collatable parameters can be thought of as a limited
1506 form of polymorphism, applicable only to textual data types.
1507 </para>
1508 </sect2>
1509 </sect1>
1510
1511 <sect1 id="xfunc-overload">
1512 <title>Function Overloading</title>
1513
1514 <indexterm zone="xfunc-overload">
1515 <primary>overloading</primary>
1516 <secondary>functions</secondary>
1517 </indexterm>
1518
1519 <para>
1520 More than one function can be defined with the same SQL name, so long
1521 as the arguments they take are different. In other words,
1522 function names can be <firstterm>overloaded</firstterm>. Whether or not
1523 you use it, this capability entails security precautions when calling
1524 functions in databases where some users mistrust other users; see
1525 <xref linkend="typeconv-func"/>. When a query is executed, the server
1526 will determine which function to call from the data types and the number
1527 of the provided arguments. Overloading can also be used to simulate
1528 functions with a variable number of arguments, up to a finite maximum
1529 number.
1530 </para>
1531
1532 <para>
1533 When creating a family of overloaded functions, one should be
1534 careful not to create ambiguities. For instance, given the
1535 functions:
1536 <programlisting>
1537 CREATE FUNCTION test(int, real) RETURNS ...
1538 CREATE FUNCTION test(smallint, double precision) RETURNS ...
1539 </programlisting>
1540 it is not immediately clear which function would be called with
1541 some trivial input like <literal>test(1, 1.5)</literal>. The
1542 currently implemented resolution rules are described in
1543 <xref linkend="typeconv"/>, but it is unwise to design a system that subtly
1544 relies on this behavior.
1545 </para>
1546
1547 <para>
1548 A function that takes a single argument of a composite type should
1549 generally not have the same name as any attribute (field) of that type.
1550 Recall that <literal><replaceable>attribute</replaceable>(<replaceable>table</replaceable>)</literal>
1551 is considered equivalent
1552 to <literal><replaceable>table</replaceable>.<replaceable>attribute</replaceable></literal>.
1553 In the case that there is an
1554 ambiguity between a function on a composite type and an attribute of
1555 the composite type, the attribute will always be used. It is possible
1556 to override that choice by schema-qualifying the function name
1557 (that is, <literal><replaceable>schema</replaceable>.<replaceable>func</replaceable>(<replaceable>table</replaceable>)
1558 </literal>) but it's better to
1559 avoid the problem by not choosing conflicting names.
1560 </para>
1561
1562 <para>
1563 Another possible conflict is between variadic and non-variadic functions.
1564 For instance, it is possible to create both <literal>foo(numeric)</literal> and
1565 <literal>foo(VARIADIC numeric[])</literal>. In this case it is unclear which one
1566 should be matched to a call providing a single numeric argument, such as
1567 <literal>foo(10.1)</literal>. The rule is that the function appearing
1568 earlier in the search path is used, or if the two functions are in the
1569 same schema, the non-variadic one is preferred.
1570 </para>
1571
1572 <para>
1573 When overloading C-language functions, there is an additional
1574 constraint: The C name of each function in the family of
1575 overloaded functions must be different from the C names of all
1576 other functions, either internal or dynamically loaded. If this
1577 rule is violated, the behavior is not portable. You might get a
1578 run-time linker error, or one of the functions will get called
1579 (usually the internal one). The alternative form of the
1580 <literal>AS</literal> clause for the SQL <command>CREATE
1581 FUNCTION</command> command decouples the SQL function name from
1582 the function name in the C source code. For instance:
1583 <programlisting>
1584 CREATE FUNCTION test(int) RETURNS int
1585 AS '<replaceable>filename</replaceable>', 'test_1arg'
1586 LANGUAGE C;
1587 CREATE FUNCTION test(int, int) RETURNS int
1588 AS '<replaceable>filename</replaceable>', 'test_2arg'
1589 LANGUAGE C;
1590 </programlisting>
1591 The names of the C functions here reflect one of many possible conventions.
1592 </para>
1593 </sect1>
1594
1595 <sect1 id="xfunc-volatility">
1596 <title>Function Volatility Categories</title>
1597
1598 <indexterm zone="xfunc-volatility">
1599 <primary>volatility</primary>
1600 <secondary>functions</secondary>
1601 </indexterm>
1602 <indexterm zone="xfunc-volatility">
1603 <primary>VOLATILE</primary>
1604 </indexterm>
1605 <indexterm zone="xfunc-volatility">
1606 <primary>STABLE</primary>
1607 </indexterm>
1608 <indexterm zone="xfunc-volatility">
1609 <primary>IMMUTABLE</primary>
1610 </indexterm>
1611
1612 <para>
1613 Every function has a <firstterm>volatility</firstterm> classification, with
1614 the possibilities being <literal>VOLATILE</literal>, <literal>STABLE</literal>, or
1615 <literal>IMMUTABLE</literal>. <literal>VOLATILE</literal> is the default if the
1616 <link linkend="sql-createfunction"><command>CREATE FUNCTION</command></link>
1617 command does not specify a category. The volatility category is a
1618 promise to the optimizer about the behavior of the function:
1619
1620 <itemizedlist>
1621 <listitem>
1622 <para>
1623 A <literal>VOLATILE</literal> function can do anything, including modifying
1624 the database. It can return different results on successive calls with
1625 the same arguments. The optimizer makes no assumptions about the
1626 behavior of such functions. A query using a volatile function will
1627 re-evaluate the function at every row where its value is needed.
1628 </para>
1629 </listitem>
1630 <listitem>
1631 <para>
1632 A <literal>STABLE</literal> function cannot modify the database and is
1633 guaranteed to return the same results given the same arguments
1634 for all rows within a single statement. This category allows the
1635 optimizer to optimize multiple calls of the function to a single
1636 call. In particular, it is safe to use an expression containing
1637 such a function in an index scan condition. (Since an index scan
1638 will evaluate the comparison value only once, not once at each
1639 row, it is not valid to use a <literal>VOLATILE</literal> function in an
1640 index scan condition.)
1641 </para>
1642 </listitem>
1643 <listitem>
1644 <para>
1645 An <literal>IMMUTABLE</literal> function cannot modify the database and is
1646 guaranteed to return the same results given the same arguments forever.
1647 This category allows the optimizer to pre-evaluate the function when
1648 a query calls it with constant arguments. For example, a query like
1649 <literal>SELECT ... WHERE x = 2 + 2</literal> can be simplified on sight to
1650 <literal>SELECT ... WHERE x = 4</literal>, because the function underlying
1651 the integer addition operator is marked <literal>IMMUTABLE</literal>.
1652 </para>
1653 </listitem>
1654 </itemizedlist>
1655 </para>
1656
1657 <para>
1658 For best optimization results, you should label your functions with the
1659 strictest volatility category that is valid for them.
1660 </para>
1661
1662 <para>
1663 Any function with side-effects <emphasis>must</emphasis> be labeled
1664 <literal>VOLATILE</literal>, so that calls to it cannot be optimized away.
1665 Even a function with no side-effects needs to be labeled
1666 <literal>VOLATILE</literal> if its value can change within a single query;
1667 some examples are <literal>random()</literal>, <literal>currval()</literal>,
1668 <literal>timeofday()</literal>.
1669 </para>
1670
1671 <para>
1672 Another important example is that the <function>current_timestamp</function>
1673 family of functions qualify as <literal>STABLE</literal>, since their values do
1674 not change within a transaction.
1675 </para>
1676
1677 <para>
1678 There is relatively little difference between <literal>STABLE</literal> and
1679 <literal>IMMUTABLE</literal> categories when considering simple interactive
1680 queries that are planned and immediately executed: it doesn't matter
1681 a lot whether a function is executed once during planning or once during
1682 query execution startup. But there is a big difference if the plan is
1683 saved and reused later. Labeling a function <literal>IMMUTABLE</literal> when
1684 it really isn't might allow it to be prematurely folded to a constant during
1685 planning, resulting in a stale value being re-used during subsequent uses
1686 of the plan. This is a hazard when using prepared statements or when
1687 using function languages that cache plans (such as
1688 <application>PL/pgSQL</application>).
1689 </para>
1690
1691 <para>
1692 For functions written in SQL or in any of the standard procedural
1693 languages, there is a second important property determined by the
1694 volatility category, namely the visibility of any data changes that have
1695 been made by the SQL command that is calling the function. A
1696 <literal>VOLATILE</literal> function will see such changes, a <literal>STABLE</literal>
1697 or <literal>IMMUTABLE</literal> function will not. This behavior is implemented
1698 using the snapshotting behavior of MVCC (see <xref linkend="mvcc"/>):
1699 <literal>STABLE</literal> and <literal>IMMUTABLE</literal> functions use a snapshot
1700 established as of the start of the calling query, whereas
1701 <literal>VOLATILE</literal> functions obtain a fresh snapshot at the start of
1702 each query they execute.
1703 </para>
1704
1705 <note>
1706 <para>
1707 Functions written in C can manage snapshots however they want, but it's
1708 usually a good idea to make C functions work this way too.
1709 </para>
1710 </note>
1711
1712 <para>
1713 Because of this snapshotting behavior,
1714 a function containing only <command>SELECT</command> commands can safely be
1715 marked <literal>STABLE</literal>, even if it selects from tables that might be
1716 undergoing modifications by concurrent queries.
1717 <productname>PostgreSQL</productname> will execute all commands of a
1718 <literal>STABLE</literal> function using the snapshot established for the
1719 calling query, and so it will see a fixed view of the database throughout
1720 that query.
1721 </para>
1722
1723 <para>
1724 The same snapshotting behavior is used for <command>SELECT</command> commands
1725 within <literal>IMMUTABLE</literal> functions. It is generally unwise to select
1726 from database tables within an <literal>IMMUTABLE</literal> function at all,
1727 since the immutability will be broken if the table contents ever change.
1728 However, <productname>PostgreSQL</productname> does not enforce that you
1729 do not do that.
1730 </para>
1731
1732 <para>
1733 A common error is to label a function <literal>IMMUTABLE</literal> when its
1734 results depend on a configuration parameter. For example, a function
1735 that manipulates timestamps might well have results that depend on the
1736 <xref linkend="guc-timezone"/> setting. For safety, such functions should
1737 be labeled <literal>STABLE</literal> instead.
1738 </para>
1739
1740 <note>
1741 <para>
1742 <productname>PostgreSQL</productname> requires that <literal>STABLE</literal>
1743 and <literal>IMMUTABLE</literal> functions contain no SQL commands other
1744 than <command>SELECT</command> to prevent data modification.
1745 (This is not a completely bulletproof test, since such functions could
1746 still call <literal>VOLATILE</literal> functions that modify the database.
1747 If you do that, you will find that the <literal>STABLE</literal> or
1748 <literal>IMMUTABLE</literal> function does not notice the database changes
1749 applied by the called function, since they are hidden from its snapshot.)
1750 </para>
1751 </note>
1752 </sect1>
1753
1754 <sect1 id="xfunc-pl">
1755 <title>Procedural Language Functions</title>
1756
1757 <para>
1758 <productname>PostgreSQL</productname> allows user-defined functions
1759 to be written in other languages besides SQL and C. These other
1760 languages are generically called <firstterm>procedural
1761 languages</firstterm> (<acronym>PL</acronym>s).
1762 Procedural languages aren't built into the
1763 <productname>PostgreSQL</productname> server; they are offered
1764 by loadable modules.
1765 See <xref linkend="xplang"/> and following chapters for more
1766 information.
1767 </para>
1768 </sect1>
1769
1770 <sect1 id="xfunc-internal">
1771 <title>Internal Functions</title>
1772
1773 <indexterm zone="xfunc-internal"><primary>function</primary><secondary>internal</secondary></indexterm>
1774
1775 <para>
1776 Internal functions are functions written in C that have been statically
1777 linked into the <productname>PostgreSQL</productname> server.
1778 The <quote>body</quote> of the function definition
1779 specifies the C-language name of the function, which need not be the
1780 same as the name being declared for SQL use.
1781 (For reasons of backward compatibility, an empty body
1782 is accepted as meaning that the C-language function name is the
1783 same as the SQL name.)
1784 </para>
1785
1786 <para>
1787 Normally, all internal functions present in the
1788 server are declared during the initialization of the database cluster
1789 (see <xref linkend="creating-cluster"/>),
1790 but a user could use <command>CREATE FUNCTION</command>
1791 to create additional alias names for an internal function.
1792 Internal functions are declared in <command>CREATE FUNCTION</command>
1793 with language name <literal>internal</literal>. For instance, to
1794 create an alias for the <function>sqrt</function> function:
1795 <programlisting>
1796 CREATE FUNCTION square_root(double precision) RETURNS double precision
1797 AS 'dsqrt'
1798 LANGUAGE internal
1799 STRICT;
1800 </programlisting>
1801 (Most internal functions expect to be declared <quote>strict</quote>.)
1802 </para>
1803
1804 <note>
1805 <para>
1806 Not all <quote>predefined</quote> functions are
1807 <quote>internal</quote> in the above sense. Some predefined
1808 functions are written in SQL.
1809 </para>
1810 </note>
1811 </sect1>
1812
1813 <sect1 id="xfunc-c">
1814 <title>C-Language Functions</title>
1815
1816 <indexterm zone="xfunc-c">
1817 <primary>function</primary>
1818 <secondary>user-defined</secondary>
1819 <tertiary>in C</tertiary>
1820 </indexterm>
1821
1822 <para>
1823 User-defined functions can be written in C (or a language that can
1824 be made compatible with C, such as C++). Such functions are
1825 compiled into dynamically loadable objects (also called shared
1826 libraries) and are loaded by the server on demand. The dynamic
1827 loading feature is what distinguishes <quote>C language</quote> functions
1828 from <quote>internal</quote> functions &mdash; the actual coding conventions
1829 are essentially the same for both. (Hence, the standard internal
1830 function library is a rich source of coding examples for user-defined
1831 C functions.)
1832 </para>
1833
1834 <para>
1835 Currently only one calling convention is used for C functions
1836 (<quote>version 1</quote>). Support for that calling convention is
1837 indicated by writing a <literal>PG_FUNCTION_INFO_V1()</literal> macro
1838 call for the function, as illustrated below.
1839 </para>
1840
1841 <sect2 id="xfunc-c-dynload">
1842 <title>Dynamic Loading</title>
1843
1844 <indexterm zone="xfunc-c-dynload">
1845 <primary>dynamic loading</primary>
1846 </indexterm>
1847
1848 <para>
1849 The first time a user-defined function in a particular
1850 loadable object file is called in a session,
1851 the dynamic loader loads that object file into memory so that the
1852 function can be called. The <command>CREATE FUNCTION</command>
1853 for a user-defined C function must therefore specify two pieces of
1854 information for the function: the name of the loadable
1855 object file, and the C name (link symbol) of the specific function to call
1856 within that object file. If the C name is not explicitly specified then
1857 it is assumed to be the same as the SQL function name.
1858 </para>
1859
1860 <para>
1861 The following algorithm is used to locate the shared object file
1862 based on the name given in the <command>CREATE FUNCTION</command>
1863 command:
1864
1865 <orderedlist>
1866 <listitem>
1867 <para>
1868 If the name is an absolute path, the given file is loaded.
1869 </para>
1870 </listitem>
1871
1872 <listitem>
1873 <para>
1874 If the name starts with the string <literal>$libdir</literal>,
1875 that part is replaced by the <productname>PostgreSQL</productname> package
1876 library directory
1877 name, which is determined at build time.<indexterm><primary>$libdir</primary></indexterm>
1878 </para>
1879 </listitem>
1880
1881 <listitem>
1882 <para>
1883 If the name does not contain a directory part, the file is
1884 searched for in the path specified by the configuration variable
1885 <xref linkend="guc-dynamic-library-path"/>.<indexterm><primary>dynamic_library_path</primary></indexterm>
1886 </para>
1887 </listitem>
1888
1889 <listitem>
1890 <para>
1891 Otherwise (the file was not found in the path, or it contains a
1892 non-absolute directory part), the dynamic loader will try to
1893 take the name as given, which will most likely fail. (It is
1894 unreliable to depend on the current working directory.)
1895 </para>
1896 </listitem>
1897 </orderedlist>
1898
1899 If this sequence does not work, the platform-specific shared
1900 library file name extension (often <filename>.so</filename>) is
1901 appended to the given name and this sequence is tried again. If
1902 that fails as well, the load will fail.
1903 </para>
1904
1905 <para>
1906 It is recommended to locate shared libraries either relative to
1907 <literal>$libdir</literal> or through the dynamic library path.
1908 This simplifies version upgrades if the new installation is at a
1909 different location. The actual directory that
1910 <literal>$libdir</literal> stands for can be found out with the
1911 command <literal>pg_config --pkglibdir</literal>.
1912 </para>
1913
1914 <para>
1915 The user ID the <productname>PostgreSQL</productname> server runs
1916 as must be able to traverse the path to the file you intend to
1917 load. Making the file or a higher-level directory not readable
1918 and/or not executable by the <systemitem>postgres</systemitem>
1919 user is a common mistake.
1920 </para>
1921
1922 <para>
1923 In any case, the file name that is given in the
1924 <command>CREATE FUNCTION</command> command is recorded literally
1925 in the system catalogs, so if the file needs to be loaded again
1926 the same procedure is applied.
1927 </para>
1928
1929 <note>
1930 <para>
1931 <productname>PostgreSQL</productname> will not compile a C function
1932 automatically. The object file must be compiled before it is referenced
1933 in a <command>CREATE
1934 FUNCTION</command> command. See <xref linkend="dfunc"/> for additional
1935 information.
1936 </para>
1937 </note>
1938
1939 <indexterm zone="xfunc-c-dynload">
1940 <primary>magic block</primary>
1941 </indexterm>
1942 <indexterm zone="xfunc-c-dynload">
1943 <primary><literal>PG_MODULE_MAGIC</literal></primary>
1944 </indexterm>
1945
1946 <para>
1947 To ensure that a dynamically loaded object file is not loaded into an
1948 incompatible server, <productname>PostgreSQL</productname> checks that the
1949 file contains a <quote>magic block</quote> with the appropriate contents.
1950 This allows the server to detect obvious incompatibilities, such as code
1951 compiled for a different major version of
1952 <productname>PostgreSQL</productname>. To include a magic block,
1953 write this in one (and only one) of the module source files, after having
1954 included the header <filename>fmgr.h</filename>:
1955
1956 <programlisting>
1957 PG_MODULE_MAGIC;
1958 </programlisting>
1959 or
1960 <programlisting>
1961 PG_MODULE_MAGIC_EXT(<replaceable>parameters</replaceable>);
1962 </programlisting>
1963 </para>
1964
1965 <para>
1966 The <literal>PG_MODULE_MAGIC_EXT</literal> variant allows the specification
1967 of additional information about the module; currently, a name and/or a
1968 version string can be added. (More fields might be allowed in future.)
1969 Write something like this:
1970
1971 <programlisting>
1972 PG_MODULE_MAGIC_EXT(
1973 .name = "my_module_name",
1974 .version = "1.2.3"
1975 );
1976 </programlisting>
1977
1978 Subsequently the name and version can be examined via
1979 the <function>pg_get_loaded_modules()</function> function.
1980 The meaning of the version string is not restricted
1981 by <productname>PostgreSQL</productname>, but use of semantic versioning
1982 rules is recommended.
1983 </para>
1984
1985 <para>
1986 After it is used for the first time, a dynamically loaded object
1987 file is retained in memory. Future calls in the same session to
1988 the function(s) in that file will only incur the small overhead of
1989 a symbol table lookup. If you need to force a reload of an object
1990 file, for example after recompiling it, begin a fresh session.
1991 </para>
1992
1993 <indexterm zone="xfunc-c-dynload">
1994 <primary>_PG_init</primary>
1995 </indexterm>
1996 <indexterm zone="xfunc-c-dynload">
1997 <primary>library initialization function</primary>
1998 </indexterm>
1999
2000 <para>
2001 Optionally, a dynamically loaded file can contain an initialization
2002 function. If the file includes a function named
2003 <function>_PG_init</function>, that function will be called immediately after
2004 loading the file. The function receives no parameters and should
2005 return void. There is presently no way to unload a dynamically loaded file.
2006 </para>
2007
2008 </sect2>
2009
2010 <sect2 id="xfunc-c-basetype">
2011 <title>Base Types in C-Language Functions</title>
2012
2013 <indexterm zone="xfunc-c-basetype">
2014 <primary>data type</primary>
2015 <secondary>internal organization</secondary>
2016 </indexterm>
2017
2018 <para>
2019 To know how to write C-language functions, you need to know how
2020 <productname>PostgreSQL</productname> internally represents base
2021 data types and how they can be passed to and from functions.
2022 Internally, <productname>PostgreSQL</productname> regards a base
2023 type as a <quote>blob of memory</quote>. The user-defined
2024 functions that you define over a type in turn define the way that
2025 <productname>PostgreSQL</productname> can operate on it. That
2026 is, <productname>PostgreSQL</productname> will only store and
2027 retrieve the data from disk and use your user-defined functions
2028 to input, process, and output the data.
2029 </para>
2030
2031 <para>
2032 Base types can have one of three internal formats:
2033
2034 <itemizedlist>
2035 <listitem>
2036 <para>
2037 pass by value, fixed-length
2038 </para>
2039 </listitem>
2040 <listitem>
2041 <para>
2042 pass by reference, fixed-length
2043 </para>
2044 </listitem>
2045 <listitem>
2046 <para>
2047 pass by reference, variable-length
2048 </para>
2049 </listitem>
2050 </itemizedlist>
2051 </para>
2052
2053 <para>
2054 By-value types can only be 1, 2, or 4 bytes in length
2055 (also 8 bytes, if <literal>sizeof(Datum)</literal> is 8 on your machine).
2056 You should be careful to define your types such that they will be the
2057 same size (in bytes) on all architectures. For example, the
2058 <literal>long</literal> type is dangerous because it is 4 bytes on some
2059 machines and 8 bytes on others, whereas <type>int</type> type is 4 bytes
2060 on most Unix machines. A reasonable implementation of the
2061 <type>int4</type> type on Unix machines might be:
2062
2063 <programlisting>
2064 /* 4-byte integer, passed by value */
2065 typedef int int4;
2066 </programlisting>
2067
2068 (The actual PostgreSQL C code calls this type <type>int32</type>, because
2069 it is a convention in C that <type>int<replaceable>XX</replaceable></type>
2070 means <replaceable>XX</replaceable> <emphasis>bits</emphasis>. Note
2071 therefore also that the C type <type>int8</type> is 1 byte in size. The
2072 SQL type <type>int8</type> is called <type>int64</type> in C. See also
2073 <xref linkend="xfunc-c-type-table"/>.)
2074 </para>
2075
2076 <para>
2077 On the other hand, fixed-length types of any size can
2078 be passed by-reference. For example, here is a sample
2079 implementation of a <productname>PostgreSQL</productname> type:
2080
2081 <programlisting>
2082 /* 16-byte structure, passed by reference */
2083 typedef struct
2084 {
2085 double x, y;
2086 } Point;
2087 </programlisting>
2088
2089 Only pointers to such types can be used when passing
2090 them in and out of <productname>PostgreSQL</productname> functions.
2091 To return a value of such a type, allocate the right amount of
2092 memory with <literal>palloc</literal>, fill in the allocated memory,
2093 and return a pointer to it. (Also, if you just want to return the
2094 same value as one of your input arguments that's of the same data type,
2095 you can skip the extra <literal>palloc</literal> and just return the
2096 pointer to the input value.)
2097 </para>
2098
2099 <para>
2100 Finally, all variable-length types must also be passed
2101 by reference. All variable-length types must begin
2102 with an opaque length field of exactly 4 bytes, which will be set
2103 by <symbol>SET_VARSIZE</symbol>; never set this field directly! All data to
2104 be stored within that type must be located in the memory
2105 immediately following that length field. The
2106 length field contains the total length of the structure,
2107 that is, it includes the size of the length field
2108 itself.
2109 </para>
2110
2111 <para>
2112 Another important point is to avoid leaving any uninitialized bits
2113 within data type values; for example, take care to zero out any
2114 alignment padding bytes that might be present in structs. Without
2115 this, logically-equivalent constants of your data type might be
2116 seen as unequal by the planner, leading to inefficient (though not
2117 incorrect) plans.
2118 </para>
2119
2120 <warning>
2121 <para>
2122 <emphasis>Never</emphasis> modify the contents of a pass-by-reference input
2123 value. If you do so you are likely to corrupt on-disk data, since
2124 the pointer you are given might point directly into a disk buffer.
2125 The sole exception to this rule is explained in
2126 <xref linkend="xaggr"/>.
2127 </para>
2128 </warning>
2129
2130 <para>
2131 As an example, we can define the type <type>text</type> as
2132 follows:
2133
2134 <programlisting>
2135 typedef struct {
2136 int32 length;
2137 char data[FLEXIBLE_ARRAY_MEMBER];
2138 } text;
2139 </programlisting>
2140
2141 The <literal>[FLEXIBLE_ARRAY_MEMBER]</literal> notation means that the actual
2142 length of the data part is not specified by this declaration.
2143 </para>
2144
2145 <para>
2146 When manipulating
2147 variable-length types, we must be careful to allocate
2148 the correct amount of memory and set the length field correctly.
2149 For example, if we wanted to store 40 bytes in a <structname>text</structname>
2150 structure, we might use a code fragment like this:
2151
2152 <programlisting><![CDATA[
2153 #include "postgres.h"
2154 ...
2155 char buffer[40]; /* our source data */
2156 ...
2157 text *destination = (text *) palloc(VARHDRSZ + 40);
2158 SET_VARSIZE(destination, VARHDRSZ + 40);
2159 memcpy(destination->data, buffer, 40);
2160 ...
2161 ]]>
2162 </programlisting>
2163
2164 <literal>VARHDRSZ</literal> is the same as <literal>sizeof(int32)</literal>, but
2165 it's considered good style to use the macro <literal>VARHDRSZ</literal>
2166 to refer to the size of the overhead for a variable-length type.
2167 Also, the length field <emphasis>must</emphasis> be set using the
2168 <literal>SET_VARSIZE</literal> macro, not by simple assignment.
2169 </para>
2170
2171 <para>
2172 <xref linkend="xfunc-c-type-table"/> shows the C types
2173 corresponding to many of the built-in SQL data types
2174 of <productname>PostgreSQL</productname>.
2175 The <quote>Defined In</quote> column gives the header file that
2176 needs to be included to get the type definition. (The actual
2177 definition might be in a different file that is included by the
2178 listed file. It is recommended that users stick to the defined
2179 interface.) Note that you should always include
2180 <filename>postgres.h</filename> first in any source file of server
2181 code, because it declares a number of things that you will need
2182 anyway, and because including other headers first can cause
2183 portability issues.
2184 </para>
2185
2186 <table tocentry="1" id="xfunc-c-type-table">
2187 <title>Equivalent C Types for Built-in SQL Types</title>
2188 <tgroup cols="3">
2189 <colspec colname="col1" colwidth="1*"/>
2190 <colspec colname="col2" colwidth="1*"/>
2191 <colspec colname="col3" colwidth="2*"/>
2192 <thead>
2193 <row>
2194 <entry>
2195 SQL Type
2196 </entry>
2197 <entry>
2198 C Type
2199 </entry>
2200 <entry>
2201 Defined In
2202 </entry>
2203 </row>
2204 </thead>
2205 <tbody>
2206 <row>
2207 <entry><type>boolean</type></entry>
2208 <entry><type>bool</type></entry>
2209 <entry><filename>postgres.h</filename> (maybe compiler built-in)</entry>
2210 </row>
2211 <row>
2212 <entry><type>box</type></entry>
2213 <entry><type>BOX*</type></entry>
2214 <entry><filename>utils/geo_decls.h</filename></entry>
2215 </row>
2216 <row>
2217 <entry><type>bytea</type></entry>
2218 <entry><type>bytea*</type></entry>
2219 <entry><filename>postgres.h</filename></entry>
2220 </row>
2221 <row>
2222 <entry><type>"char"</type></entry>
2223 <entry><type>char</type></entry>
2224 <entry>(compiler built-in)</entry>
2225 </row>
2226 <row>
2227 <entry><type>character</type></entry>
2228 <entry><type>BpChar*</type></entry>
2229 <entry><filename>postgres.h</filename></entry>
2230 </row>
2231 <row>
2232 <entry><type>cid</type></entry>
2233 <entry><type>CommandId</type></entry>
2234 <entry><filename>postgres.h</filename></entry>
2235 </row>
2236 <row>
2237 <entry><type>date</type></entry>
2238 <entry><type>DateADT</type></entry>
2239 <entry><filename>utils/date.h</filename></entry>
2240 </row>
2241 <row>
2242 <entry><type>float4</type> (<type>real</type>)</entry>
2243 <entry><type>float4</type></entry>
2244 <entry><filename>postgres.h</filename></entry>
2245 </row>
2246 <row>
2247 <entry><type>float8</type> (<type>double precision</type>)</entry>
2248 <entry><type>float8</type></entry>
2249 <entry><filename>postgres.h</filename></entry>
2250 </row>
2251 <row>
2252 <entry><type>int2</type> (<type>smallint</type>)</entry>
2253 <entry><type>int16</type></entry>
2254 <entry><filename>postgres.h</filename></entry>
2255 </row>
2256 <row>
2257 <entry><type>int4</type> (<type>integer</type>)</entry>
2258 <entry><type>int32</type></entry>
2259 <entry><filename>postgres.h</filename></entry>
2260 </row>
2261 <row>
2262 <entry><type>int8</type> (<type>bigint</type>)</entry>
2263 <entry><type>int64</type></entry>
2264 <entry><filename>postgres.h</filename></entry>
2265 </row>
2266 <row>
2267 <entry><type>interval</type></entry>
2268 <entry><type>Interval*</type></entry>
2269 <entry><filename>datatype/timestamp.h</filename></entry>
2270 </row>
2271 <row>
2272 <entry><type>lseg</type></entry>
2273 <entry><type>LSEG*</type></entry>
2274 <entry><filename>utils/geo_decls.h</filename></entry>
2275 </row>
2276 <row>
2277 <entry><type>name</type></entry>
2278 <entry><type>Name</type></entry>
2279 <entry><filename>postgres.h</filename></entry>
2280 </row>
2281 <row>
2282 <entry><type>numeric</type></entry>
2283 <entry><type>Numeric</type></entry>
2284 <entry><filename>utils/numeric.h</filename></entry>
2285 </row>
2286 <row>
2287 <entry><type>oid</type></entry>
2288 <entry><type>Oid</type></entry>
2289 <entry><filename>postgres.h</filename></entry>
2290 </row>
2291 <row>
2292 <entry><type>oidvector</type></entry>
2293 <entry><type>oidvector*</type></entry>
2294 <entry><filename>postgres.h</filename></entry>
2295 </row>
2296 <row>
2297 <entry><type>path</type></entry>
2298 <entry><type>PATH*</type></entry>
2299 <entry><filename>utils/geo_decls.h</filename></entry>
2300 </row>
2301 <row>
2302 <entry><type>point</type></entry>
2303 <entry><type>POINT*</type></entry>
2304 <entry><filename>utils/geo_decls.h</filename></entry>
2305 </row>
2306 <row>
2307 <entry><type>regproc</type></entry>
2308 <entry><type>RegProcedure</type></entry>
2309 <entry><filename>postgres.h</filename></entry>
2310 </row>
2311 <row>
2312 <entry><type>text</type></entry>
2313 <entry><type>text*</type></entry>
2314 <entry><filename>postgres.h</filename></entry>
2315 </row>
2316 <row>
2317 <entry><type>tid</type></entry>
2318 <entry><type>ItemPointer</type></entry>
2319 <entry><filename>storage/itemptr.h</filename></entry>
2320 </row>
2321 <row>
2322 <entry><type>time</type></entry>
2323 <entry><type>TimeADT</type></entry>
2324 <entry><filename>utils/date.h</filename></entry>
2325 </row>
2326 <row>
2327 <entry><type>time with time zone</type></entry>
2328 <entry><type>TimeTzADT</type></entry>
2329 <entry><filename>utils/date.h</filename></entry>
2330 </row>
2331 <row>
2332 <entry><type>timestamp</type></entry>
2333 <entry><type>Timestamp</type></entry>
2334 <entry><filename>datatype/timestamp.h</filename></entry>
2335 </row>
2336 <row>
2337 <entry><type>timestamp with time zone</type></entry>
2338 <entry><type>TimestampTz</type></entry>
2339 <entry><filename>datatype/timestamp.h</filename></entry>
2340 </row>
2341 <row>
2342 <entry><type>varchar</type></entry>
2343 <entry><type>VarChar*</type></entry>
2344 <entry><filename>postgres.h</filename></entry>
2345 </row>
2346 <row>
2347 <entry><type>xid</type></entry>
2348 <entry><type>TransactionId</type></entry>
2349 <entry><filename>postgres.h</filename></entry>
2350 </row>
2351 </tbody>
2352 </tgroup>
2353 </table>
2354
2355 <para>
2356 Now that we've gone over all of the possible structures
2357 for base types, we can show some examples of real functions.
2358 </para>
2359 </sect2>
2360
2361 <sect2 id="xfunc-c-v1-call-conv">
2362 <title>Version 1 Calling Conventions</title>
2363
2364 <para>
2365 The version-1 calling convention relies on macros to suppress most
2366 of the complexity of passing arguments and results. The C declaration
2367 of a version-1 function is always:
2368 <programlisting>
2369 Datum funcname(PG_FUNCTION_ARGS)
2370 </programlisting>
2371 In addition, the macro call:
2372 <programlisting>
2373 PG_FUNCTION_INFO_V1(funcname);
2374 </programlisting>
2375 must appear in the same source file. (Conventionally, it's
2376 written just before the function itself.) This macro call is not
2377 needed for <literal>internal</literal>-language functions, since
2378 <productname>PostgreSQL</productname> assumes that all internal functions
2379 use the version-1 convention. It is, however, required for
2380 dynamically-loaded functions.
2381 </para>
2382
2383 <para>
2384 In a version-1 function, each actual argument is fetched using a
2385 <function>PG_GETARG_<replaceable>xxx</replaceable>()</function>
2386 macro that corresponds to the argument's data type. (In non-strict
2387 functions there needs to be a previous check about argument null-ness
2388 using <function>PG_ARGISNULL()</function>; see below.)
2389 The result is returned using a
2390 <function>PG_RETURN_<replaceable>xxx</replaceable>()</function>
2391 macro for the return type.
2392 <function>PG_GETARG_<replaceable>xxx</replaceable>()</function>
2393 takes as its argument the number of the function argument to
2394 fetch, where the count starts at 0.
2395 <function>PG_RETURN_<replaceable>xxx</replaceable>()</function>
2396 takes as its argument the actual value to return.
2397 </para>
2398
2399 <para>
2400 To call another version-1 function, you can use
2401 <function>DirectFunctionCall<replaceable>n</replaceable>(func,
2402 arg1, ..., argn)</function>. This is particularly useful when you want
2403 to call functions defined in the standard internal library, by using an
2404 interface similar to their SQL signature.
2405 </para>
2406
2407 <para>
2408 These convenience functions and similar ones can be found
2409 in <filename>fmgr.h</filename>.
2410 The <function>DirectFunctionCall<replaceable>n</replaceable></function>
2411 family expect a C function name as their first argument. There are also
2412 <function>OidFunctionCall<replaceable>n</replaceable></function> which
2413 take the OID of the target function, and some other variants. All of
2414 these expect the function's arguments to be supplied
2415 as <type>Datum</type>s, and likewise they return <type>Datum</type>.
2416 Note that neither arguments nor result are allowed to be NULL when
2417 using these convenience functions.
2418 </para>
2419
2420 <para>
2421 For example, to call the <function>starts_with(text, text)</function>
2422 function from C, you can search through the catalog and find out that
2423 its C implementation is the
2424 <function>Datum text_starts_with(PG_FUNCTION_ARGS)</function>
2425 function. Typically you would
2426 use <literal>DirectFunctionCall2(text_starts_with, ...)</literal> to
2427 call such a function. However, <function>starts_with(text,
2428 text)</function> requires collation information, so it will fail
2429 with <quote>could not determine which collation to use for string
2430 comparison</quote> if called that way. Instead you must
2431 use <literal>DirectFunctionCall2Coll(text_starts_with, ...)</literal>
2432 and provide the desired collation, which typically is just passed
2433 through from <function>PG_GET_COLLATION()</function>, as shown in the
2434 example below.
2435 </para>
2436
2437 <para>
2438 <filename>fmgr.h</filename> also supplies macros that facilitate
2439 conversions between C types and <type>Datum</type>. For example to
2440 turn <type>Datum</type> into <type>text*</type>, you can
2441 use <function>DatumGetTextPP(X)</function>. While some types have macros
2442 named like <function>TypeGetDatum(X)</function> for the reverse
2443 conversion, <type>text*</type> does not; it's sufficient to use the
2444 generic macro <function>PointerGetDatum(X)</function> for that.
2445 If your extension defines additional types, it is usually convenient to
2446 define similar macros for your types too.
2447 </para>
2448
2449 <para>
2450 Here are some examples using the version-1 calling convention:
2451 </para>
2452
2453 <programlisting><![CDATA[
2454 #include "postgres.h"
2455 #include <string.h>
2456 #include "fmgr.h"
2457 #include "utils/geo_decls.h"
2458 #include "varatt.h"
2459
2460 PG_MODULE_MAGIC;
2461
2462 /* by value */
2463
2464 PG_FUNCTION_INFO_V1(add_one);
2465
2466 Datum
2467 add_one(PG_FUNCTION_ARGS)
2468 {
2469 int32 arg = PG_GETARG_INT32(0);
2470
2471 PG_RETURN_INT32(arg + 1);
2472 }
2473
2474 /* by reference, fixed length */
2475
2476 PG_FUNCTION_INFO_V1(add_one_float8);
2477
2478 Datum
2479 add_one_float8(PG_FUNCTION_ARGS)
2480 {
2481 /* The macros for FLOAT8 hide its pass-by-reference nature. */
2482 float8 arg = PG_GETARG_FLOAT8(0);
2483
2484 PG_RETURN_FLOAT8(arg + 1.0);
2485 }
2486
2487 PG_FUNCTION_INFO_V1(makepoint);
2488
2489 Datum
2490 makepoint(PG_FUNCTION_ARGS)
2491 {
2492 /* Here, the pass-by-reference nature of Point is not hidden. */
2493 Point *pointx = PG_GETARG_POINT_P(0);
2494 Point *pointy = PG_GETARG_POINT_P(1);
2495 Point *new_point = (Point *) palloc(sizeof(Point));
2496
2497 new_point->x = pointx->x;
2498 new_point->y = pointy->y;
2499
2500 PG_RETURN_POINT_P(new_point);
2501 }
2502
2503 /* by reference, variable length */
2504
2505 PG_FUNCTION_INFO_V1(copytext);
2506
2507 Datum
2508 copytext(PG_FUNCTION_ARGS)
2509 {
2510 text *t = PG_GETARG_TEXT_PP(0);
2511
2512 /*
2513 * VARSIZE_ANY_EXHDR is the size of the struct in bytes, minus the
2514 * VARHDRSZ or VARHDRSZ_SHORT of its header. Construct the copy with a
2515 * full-length header.
2516 */
2517 text *new_t = (text *) palloc(VARSIZE_ANY_EXHDR(t) + VARHDRSZ);
2518 SET_VARSIZE(new_t, VARSIZE_ANY_EXHDR(t) + VARHDRSZ);
2519
2520 /*
2521 * VARDATA is a pointer to the data region of the new struct. The source
2522 * could be a short datum, so retrieve its data through VARDATA_ANY.
2523 */
2524 memcpy(VARDATA(new_t), /* destination */
2525 VARDATA_ANY(t), /* source */
2526 VARSIZE_ANY_EXHDR(t)); /* how many bytes */
2527 PG_RETURN_TEXT_P(new_t);
2528 }
2529
2530 PG_FUNCTION_INFO_V1(concat_text);
2531
2532 Datum
2533 concat_text(PG_FUNCTION_ARGS)
2534 {
2535 text *arg1 = PG_GETARG_TEXT_PP(0);
2536 text *arg2 = PG_GETARG_TEXT_PP(1);
2537 int32 arg1_size = VARSIZE_ANY_EXHDR(arg1);
2538 int32 arg2_size = VARSIZE_ANY_EXHDR(arg2);
2539 int32 new_text_size = arg1_size + arg2_size + VARHDRSZ;
2540 text *new_text = (text *) palloc(new_text_size);
2541
2542 SET_VARSIZE(new_text, new_text_size);
2543 memcpy(VARDATA(new_text), VARDATA_ANY(arg1), arg1_size);
2544 memcpy(VARDATA(new_text) + arg1_size, VARDATA_ANY(arg2), arg2_size);
2545 PG_RETURN_TEXT_P(new_text);
2546 }
2547
2548 /* A wrapper around starts_with(text, text) */
2549
2550 PG_FUNCTION_INFO_V1(t_starts_with);
2551
2552 Datum
2553 t_starts_with(PG_FUNCTION_ARGS)
2554 {
2555 text *t1 = PG_GETARG_TEXT_PP(0);
2556 text *t2 = PG_GETARG_TEXT_PP(1);
2557 Oid collid = PG_GET_COLLATION();
2558 bool result;
2559
2560 result = DatumGetBool(DirectFunctionCall2Coll(text_starts_with,
2561 collid,
2562 PointerGetDatum(t1),
2563 PointerGetDatum(t2)));
2564 PG_RETURN_BOOL(result);
2565 }
2566 ]]>
2567 </programlisting>
2568
2569 <para>
2570 Supposing that the above code has been prepared in file
2571 <filename>funcs.c</filename> and compiled into a shared object,
2572 we could define the functions to <productname>PostgreSQL</productname>
2573 with commands like this:
2574 </para>
2575
2576 <programlisting>
2577 CREATE FUNCTION add_one(integer) RETURNS integer
2578 AS '<replaceable>DIRECTORY</replaceable>/funcs', 'add_one'
2579 LANGUAGE C STRICT;
2580
2581 -- note overloading of SQL function name "add_one"
2582 CREATE FUNCTION add_one(double precision) RETURNS double precision
2583 AS '<replaceable>DIRECTORY</replaceable>/funcs', 'add_one_float8'
2584 LANGUAGE C STRICT;
2585
2586 CREATE FUNCTION makepoint(point, point) RETURNS point
2587 AS '<replaceable>DIRECTORY</replaceable>/funcs', 'makepoint'
2588 LANGUAGE C STRICT;
2589
2590 CREATE FUNCTION copytext(text) RETURNS text
2591 AS '<replaceable>DIRECTORY</replaceable>/funcs', 'copytext'
2592 LANGUAGE C STRICT;
2593
2594 CREATE FUNCTION concat_text(text, text) RETURNS text
2595 AS '<replaceable>DIRECTORY</replaceable>/funcs', 'concat_text'
2596 LANGUAGE C STRICT;
2597
2598 CREATE FUNCTION t_starts_with(text, text) RETURNS boolean
2599 AS '<replaceable>DIRECTORY</replaceable>/funcs', 't_starts_with'
2600 LANGUAGE C STRICT;
2601 </programlisting>
2602
2603 <para>
2604 Here, <replaceable>DIRECTORY</replaceable> stands for the
2605 directory of the shared library file (for instance the
2606 <productname>PostgreSQL</productname> tutorial directory, which
2607 contains the code for the examples used in this section).
2608 (Better style would be to use just <literal>'funcs'</literal> in the
2609 <literal>AS</literal> clause, after having added
2610 <replaceable>DIRECTORY</replaceable> to the search path. In any
2611 case, we can omit the system-specific extension for a shared
2612 library, commonly <literal>.so</literal>.)
2613 </para>
2614
2615 <para>
2616 Notice that we have specified the functions as <quote>strict</quote>,
2617 meaning that
2618 the system should automatically assume a null result if any input
2619 value is null. By doing this, we avoid having to check for null inputs
2620 in the function code. Without this, we'd have to check for null values
2621 explicitly, using <function>PG_ARGISNULL()</function>.
2622 </para>
2623
2624 <para>
2625 The macro <function>PG_ARGISNULL(<replaceable>n</replaceable>)</function>
2626 allows a function to test whether each input is null. (Of course, doing
2627 this is only necessary in functions not declared <quote>strict</quote>.)
2628 As with the
2629 <function>PG_GETARG_<replaceable>xxx</replaceable>()</function> macros,
2630 the input arguments are counted beginning at zero. Note that one
2631 should refrain from executing
2632 <function>PG_GETARG_<replaceable>xxx</replaceable>()</function> until
2633 one has verified that the argument isn't null.
2634 To return a null result, execute <function>PG_RETURN_NULL()</function>;
2635 this works in both strict and nonstrict functions.
2636 </para>
2637
2638 <para>
2639 At first glance, the version-1 coding conventions might appear
2640 to be just pointless obscurantism, compared to using
2641 plain <literal>C</literal> calling conventions. They do however allow
2642 us to deal with <literal>NULL</literal>able arguments/return values,
2643 and <quote>toasted</quote> (compressed or out-of-line) values.
2644 </para>
2645
2646 <para>
2647 Other options provided by the version-1 interface are two
2648 variants of the
2649 <function>PG_GETARG_<replaceable>xxx</replaceable>()</function>
2650 macros. The first of these,
2651 <function>PG_GETARG_<replaceable>xxx</replaceable>_COPY()</function>,
2652 guarantees to return a copy of the specified argument that is
2653 safe for writing into. (The normal macros will sometimes return a
2654 pointer to a value that is physically stored in a table, which
2655 must not be written to. Using the
2656 <function>PG_GETARG_<replaceable>xxx</replaceable>_COPY()</function>
2657 macros guarantees a writable result.)
2658 The second variant consists of the
2659 <function>PG_GETARG_<replaceable>xxx</replaceable>_SLICE()</function>
2660 macros which take three arguments. The first is the number of the
2661 function argument (as above). The second and third are the offset and
2662 length of the segment to be returned. Offsets are counted from
2663 zero, and a negative length requests that the remainder of the
2664 value be returned. These macros provide more efficient access to
2665 parts of large values in the case where they have storage type
2666 <quote>external</quote>. (The storage type of a column can be specified using
2667 <literal>ALTER TABLE <replaceable>tablename</replaceable> ALTER
2668 COLUMN <replaceable>colname</replaceable> SET STORAGE
2669 <replaceable>storagetype</replaceable></literal>. <replaceable>storagetype</replaceable> is one of
2670 <literal>plain</literal>, <literal>external</literal>, <literal>extended</literal>,
2671 or <literal>main</literal>.)
2672 </para>
2673
2674 <para>
2675 Finally, the version-1 function call conventions make it possible
2676 to return set results (<xref linkend="xfunc-c-return-set"/>) and
2677 implement trigger functions (<xref linkend="triggers"/>) and
2678 procedural-language call handlers (<xref
2679 linkend="plhandler"/>). For more details
2680 see <filename>src/backend/utils/fmgr/README</filename> in the
2681 source distribution.
2682 </para>
2683 </sect2>
2684
2685 <sect2 id="xfunc-c-code">
2686 <title>Writing Code</title>
2687
2688 <para>
2689 Before we turn to the more advanced topics, we should discuss
2690 some coding rules for <productname>PostgreSQL</productname>
2691 C-language functions. While it might be possible to load functions
2692 written in languages other than C into
2693 <productname>PostgreSQL</productname>, this is usually difficult
2694 (when it is possible at all) because other languages, such as
2695 C++, FORTRAN, or Pascal often do not follow the same calling
2696 convention as C. That is, other languages do not pass argument
2697 and return values between functions in the same way. For this
2698 reason, we will assume that your C-language functions are
2699 actually written in C.
2700 </para>
2701
2702 <para>
2703 The basic rules for writing and building C functions are as follows:
2704
2705 <itemizedlist>
2706 <listitem>
2707 <para>
2708 Use <literal>pg_config
2709 --includedir-server</literal><indexterm><primary>pg_config</primary><secondary>with user-defined C functions</secondary></indexterm>
2710 to find out where the <productname>PostgreSQL</productname> server header
2711 files are installed on your system (or the system that your
2712 users will be running on).
2713 </para>
2714 </listitem>
2715
2716 <listitem>
2717 <para>
2718 Compiling and linking your code so that it can be dynamically
2719 loaded into <productname>PostgreSQL</productname> always
2720 requires special flags. See <xref linkend="dfunc"/> for a
2721 detailed explanation of how to do it for your particular
2722 operating system.
2723 </para>
2724 </listitem>
2725
2726 <listitem>
2727 <para>
2728 Remember to define a <quote>magic block</quote> for your shared library,
2729 as described in <xref linkend="xfunc-c-dynload"/>.
2730 </para>
2731 </listitem>
2732
2733 <listitem>
2734 <para>
2735 When allocating memory, use the
2736 <productname>PostgreSQL</productname> functions
2737 <function>palloc</function><indexterm><primary>palloc</primary></indexterm> and <function>pfree</function><indexterm><primary>pfree</primary></indexterm>
2738 instead of the corresponding C library functions
2739 <function>malloc</function> and <function>free</function>.
2740 The memory allocated by <function>palloc</function> will be
2741 freed automatically at the end of each transaction, preventing
2742 memory leaks.
2743 </para>
2744 </listitem>
2745
2746 <listitem>
2747 <para>
2748 Always zero the bytes of your structures using <function>memset</function>
2749 (or allocate them with <function>palloc0</function> in the first place).
2750 Even if you assign to each field of your structure, there might be
2751 alignment padding (holes in the structure) that contain
2752 garbage values. Without this, it's difficult to
2753 support hash indexes or hash joins, as you must pick out only
2754 the significant bits of your data structure to compute a hash.
2755 The planner also sometimes relies on comparing constants via
2756 bitwise equality, so you can get undesirable planning results if
2757 logically-equivalent values aren't bitwise equal.
2758 </para>
2759 </listitem>
2760
2761 <listitem>
2762 <para>
2763 Most of the internal <productname>PostgreSQL</productname>
2764 types are declared in <filename>postgres.h</filename>, while
2765 the function manager interfaces
2766 (<symbol>PG_FUNCTION_ARGS</symbol>, etc.) are in
2767 <filename>fmgr.h</filename>, so you will need to include at
2768 least these two files. For portability reasons it's best to
2769 include <filename>postgres.h</filename> <emphasis>first</emphasis>,
2770 before any other system or user header files. Including
2771 <filename>postgres.h</filename> will also include
2772 <filename>elog.h</filename> and <filename>palloc.h</filename>
2773 for you.
2774 </para>
2775 </listitem>
2776
2777 <listitem>
2778 <para>
2779 Symbol names defined within object files must not conflict
2780 with each other or with symbols defined in the
2781 <productname>PostgreSQL</productname> server executable. You
2782 will have to rename your functions or variables if you get
2783 error messages to this effect.
2784 </para>
2785 </listitem>
2786 </itemizedlist>
2787 </para>
2788 </sect2>
2789
2790 &dfunc;
2791
2792 <sect2 id="xfunc-api-abi-stability-guidance">
2793 <title>Server API and ABI Stability Guidance</title>
2794
2795 <para>
2796 This section contains guidance to authors of extensions and other server
2797 plugins about API and ABI stability in the
2798 <productname>PostgreSQL</productname> server.
2799 </para>
2800
2801 <sect3 id="xfunc-guidance-general">
2802 <title>General</title>
2803
2804 <para>
2805 The <productname>PostgreSQL</productname> server contains several
2806 well-demarcated APIs for server plugins, such as the function manager
2807 (<acronym>fmgr</acronym>, described in this chapter),
2808 <acronym>SPI</acronym> (<xref linkend="spi"/>), and various hooks
2809 specifically designed for extensions. These interfaces are carefully
2810 managed for long-term stability and compatibility. However, the entire
2811 set of global functions and variables in the server effectively
2812 constitutes the publicly usable API, and most of it was not designed
2813 with extensibility and long-term stability in mind.
2814 </para>
2815
2816 <para>
2817 Therefore, while taking advantage of these interfaces is valid, the
2818 further one strays from the well-trodden path, the likelier it will be
2819 that one might encounter API or ABI compatibility issues at some point.
2820 Extension authors are encouraged to provide feedback about their
2821 requirements, so that over time, as new use patterns arise, certain
2822 interfaces can be considered more stabilized or new, better-designed
2823 interfaces can be added.
2824 </para>
2825 </sect3>
2826
2827 <sect3 id="xfunc-guidance-api-compatibility">
2828 <title>API Compatibility</title>
2829 <para>
2830 The <acronym>API</acronym>, or application programming interface, is the
2831 interface used at compile time.
2832 </para>
2833
2834 <sect4 id="xfunc-guidance-api-major-versions">
2835 <title>Major Versions</title>
2836 <para>
2837 There is <emphasis>no</emphasis> promise of API compatibility between
2838 <productname>PostgreSQL</productname> major versions. Extension code
2839 therefore might require source code changes to work with multiple major
2840 versions. These can usually be managed with preprocessor conditions
2841 such as <literal>#if PG_VERSION_NUM &gt;= 160000</literal>.
2842 Sophisticated extensions that use interfaces beyond the well-demarcated
2843 ones usually require a few such changes for each major server version.
2844 </para>
2845 </sect4>
2846
2847 <sect4 id="xfunc-guidance-api-mninor-versions">
2848 <title>Minor Versions</title>
2849 <para>
2850 <productname>PostgreSQL</productname> makes an effort to avoid server
2851 API breaks in minor releases. In general, extension code that compiles
2852 and works with a minor release should also compile and work with any
2853 other minor release of the same major version, past or future.
2854 </para>
2855
2856 <para>
2857 When a change <emphasis>is</emphasis> required, it will be carefully
2858 managed, taking the requirements of extensions into account. Such
2859 changes will be communicated in the release notes (<xref
2860 linkend="release"/>).
2861 </para>
2862 </sect4>
2863 </sect3>
2864
2865 <sect3 id="xfunc-guidance-abi-compatibility">
2866 <title>ABI Compatibility</title>
2867 <para>
2868 The <acronym>ABI</acronym>, or application binary interface, is the
2869 interface used at run time.
2870 </para>
2871
2872 <sect4 id="xfunc-guidance-abi-major-versions">
2873 <title>Major Versions</title>
2874 <para>
2875 Servers of different major versions have intentionally incompatible
2876 ABIs. Extensions that use server APIs must therefore be re-compiled for
2877 each major release. The inclusion of <literal>PG_MODULE_MAGIC</literal>
2878 (see <xref linkend="xfunc-c-dynload"/>) ensures that code compiled for
2879 one major version will be rejected by other major versions.
2880 </para>
2881 </sect4>
2882
2883 <sect4 id="xfunc-guidance-abi-mninor-versions">
2884 <title>Minor Versions</title>
2885 <para>
2886 <productname>PostgreSQL</productname> makes an effort to avoid server
2887 ABI breaks in minor releases. In general, an extension compiled against
2888 any minor release should work with any other minor release of the same
2889 major version, past or future.
2890 </para>
2891
2892 <para>
2893 When a change <emphasis>is</emphasis> required,
2894 <productname>PostgreSQL</productname> will choose the least invasive
2895 change possible, for example by squeezing a new field into padding
2896 space or appending it to the end of a struct. These sorts of changes
2897 should not impact extensions unless they use very unusual code
2898 patterns.
2899 </para>
2900
2901 <para>
2902 In rare cases, however, even such non-invasive changes may be
2903 impractical or impossible. In such an event, the change will be
2904 carefully managed, taking the requirements of extensions into account.
2905 Such changes will also be documented in the release notes (<xref
2906 linkend="release"/>).
2907 </para>
2908
2909 <para>
2910 Note, however, that many parts of the server are not designed or
2911 maintained as publicly-consumable APIs (and that, in most cases, the
2912 actual boundary is also not well-defined). If urgent needs arise,
2913 changes in those parts will naturally be made with less consideration
2914 for extension code than changes in well-defined and widely used
2915 interfaces.
2916 </para>
2917
2918 <para>
2919 Also, in the absence of automated detection of such changes, this is
2920 not a guarantee, but historically such breaking changes have been
2921 extremely rare.
2922 </para>
2923
2924 </sect4>
2925 </sect3>
2926 </sect2>
2927
2928 <sect2 id="xfunc-c-composite-type-args">
2929 <title>Composite-Type Arguments</title>
2930
2931 <para>
2932 Composite types do not have a fixed layout like C structures.
2933 Instances of a composite type can contain null fields. In
2934 addition, composite types that are part of an inheritance
2935 hierarchy can have different fields than other members of the
2936 same inheritance hierarchy. Therefore,
2937 <productname>PostgreSQL</productname> provides a function
2938 interface for accessing fields of composite types from C.
2939 </para>
2940
2941 <para>
2942 Suppose we want to write a function to answer the query:
2943
2944 <programlisting>
2945 SELECT name, c_overpaid(emp, 1500) AS overpaid
2946 FROM emp
2947 WHERE name = 'Bill' OR name = 'Sam';
2948 </programlisting>
2949
2950 Using the version-1 calling conventions, we can define
2951 <function>c_overpaid</function> as:
2952
2953 <programlisting><![CDATA[
2954 #include "postgres.h"
2955 #include "executor/executor.h" /* for GetAttributeByName() */
2956
2957 PG_MODULE_MAGIC;
2958
2959 PG_FUNCTION_INFO_V1(c_overpaid);
2960
2961 Datum
2962 c_overpaid(PG_FUNCTION_ARGS)
2963 {
2964 HeapTupleHeader t = PG_GETARG_HEAPTUPLEHEADER(0);
2965 int32 limit = PG_GETARG_INT32(1);
2966 bool isnull;
2967 Datum salary;
2968
2969 salary = GetAttributeByName(t, "salary", &isnull);
2970 if (isnull)
2971 PG_RETURN_BOOL(false);
2972 /* Alternatively, we might prefer to do PG_RETURN_NULL() for null salary. */
2973
2974 PG_RETURN_BOOL(DatumGetInt32(salary) > limit);
2975 }
2976 ]]>
2977 </programlisting>
2978 </para>
2979
2980 <para>
2981 <function>GetAttributeByName</function> is the
2982 <productname>PostgreSQL</productname> system function that
2983 returns attributes out of the specified row. It has
2984 three arguments: the argument of type <type>HeapTupleHeader</type> passed
2985 into
2986 the function, the name of the desired attribute, and a
2987 return parameter that tells whether the attribute
2988 is null. <function>GetAttributeByName</function> returns a <type>Datum</type>
2989 value that you can convert to the proper data type by using the
2990 appropriate <function>DatumGet<replaceable>XXX</replaceable>()</function>
2991 function. Note that the return value is meaningless if the null flag is
2992 set; always check the null flag before trying to do anything with the
2993 result.
2994 </para>
2995
2996 <para>
2997 There is also <function>GetAttributeByNum</function>, which selects
2998 the target attribute by column number instead of name.
2999 </para>
3000
3001 <para>
3002 The following command declares the function
3003 <function>c_overpaid</function> in SQL:
3004
3005 <programlisting>
3006 CREATE FUNCTION c_overpaid(emp, integer) RETURNS boolean
3007 AS '<replaceable>DIRECTORY</replaceable>/funcs', 'c_overpaid'
3008 LANGUAGE C STRICT;
3009 </programlisting>
3010
3011 Notice we have used <literal>STRICT</literal> so that we did not have to
3012 check whether the input arguments were NULL.
3013 </para>
3014 </sect2>
3015
3016 <sect2 id="xfunc-c-returning-rows">
3017 <title>Returning Rows (Composite Types)</title>
3018
3019 <para>
3020 To return a row or composite-type value from a C-language
3021 function, you can use a special API that provides macros and
3022 functions to hide most of the complexity of building composite
3023 data types. To use this API, the source file must include:
3024 <programlisting>
3025 #include "funcapi.h"
3026 </programlisting>
3027 </para>
3028
3029 <para>
3030 There are two ways you can build a composite data value (henceforth
3031 a <quote>tuple</quote>): you can build it from an array of Datum values,
3032 or from an array of C strings that can be passed to the input
3033 conversion functions of the tuple's column data types. In either
3034 case, you first need to obtain or construct a <structname>TupleDesc</structname>
3035 descriptor for the tuple structure. When working with Datums, you
3036 pass the <structname>TupleDesc</structname> to <function>BlessTupleDesc</function>,
3037 and then call <function>heap_form_tuple</function> for each row. When working
3038 with C strings, you pass the <structname>TupleDesc</structname> to
3039 <function>TupleDescGetAttInMetadata</function>, and then call
3040 <function>BuildTupleFromCStrings</function> for each row. In the case of a
3041 function returning a set of tuples, the setup steps can all be done
3042 once during the first call of the function.
3043 </para>
3044
3045 <para>
3046 Several helper functions are available for setting up the needed
3047 <structname>TupleDesc</structname>. The recommended way to do this in most
3048 functions returning composite values is to call:
3049 <programlisting>
3050 TypeFuncClass get_call_result_type(FunctionCallInfo fcinfo,
3051 Oid *resultTypeId,
3052 TupleDesc *resultTupleDesc)
3053 </programlisting>
3054 passing the same <literal>fcinfo</literal> struct passed to the calling function
3055 itself. (This of course requires that you use the version-1
3056 calling conventions.) <varname>resultTypeId</varname> can be specified
3057 as <literal>NULL</literal> or as the address of a local variable to receive the
3058 function's result type OID. <varname>resultTupleDesc</varname> should be the
3059 address of a local <structname>TupleDesc</structname> variable. Check that the
3060 result is <literal>TYPEFUNC_COMPOSITE</literal>; if so,
3061 <varname>resultTupleDesc</varname> has been filled with the needed
3062 <structname>TupleDesc</structname>. (If it is not, you can report an error along
3063 the lines of <quote>function returning record called in context that
3064 cannot accept type record</quote>.)
3065 </para>
3066
3067 <tip>
3068 <para>
3069 <function>get_call_result_type</function> can resolve the actual type of a
3070 polymorphic function result; so it is useful in functions that return
3071 scalar polymorphic results, not only functions that return composites.
3072 The <varname>resultTypeId</varname> output is primarily useful for functions
3073 returning polymorphic scalars.
3074 </para>
3075 </tip>
3076
3077 <note>
3078 <para>
3079 <function>get_call_result_type</function> has a sibling
3080 <function>get_expr_result_type</function>, which can be used to resolve the
3081 expected output type for a function call represented by an expression
3082 tree. This can be used when trying to determine the result type from
3083 outside the function itself. There is also
3084 <function>get_func_result_type</function>, which can be used when only the
3085 function's OID is available. However these functions are not able
3086 to deal with functions declared to return <structname>record</structname>, and
3087 <function>get_func_result_type</function> cannot resolve polymorphic types,
3088 so you should preferentially use <function>get_call_result_type</function>.
3089 </para>
3090 </note>
3091
3092 <para>
3093 Older, now-deprecated functions for obtaining
3094 <structname>TupleDesc</structname>s are:
3095 <programlisting>
3096 TupleDesc RelationNameGetTupleDesc(const char *relname)
3097 </programlisting>
3098 to get a <structname>TupleDesc</structname> for the row type of a named relation,
3099 and:
3100 <programlisting>
3101 TupleDesc TypeGetTupleDesc(Oid typeoid, List *colaliases)
3102 </programlisting>
3103 to get a <structname>TupleDesc</structname> based on a type OID. This can
3104 be used to get a <structname>TupleDesc</structname> for a base or
3105 composite type. It will not work for a function that returns
3106 <structname>record</structname>, however, and it cannot resolve polymorphic
3107 types.
3108 </para>
3109
3110 <para>
3111 Once you have a <structname>TupleDesc</structname>, call:
3112 <programlisting>
3113 TupleDesc BlessTupleDesc(TupleDesc tupdesc)
3114 </programlisting>
3115 if you plan to work with Datums, or:
3116 <programlisting>
3117 AttInMetadata *TupleDescGetAttInMetadata(TupleDesc tupdesc)
3118 </programlisting>
3119 if you plan to work with C strings. If you are writing a function
3120 returning set, you can save the results of these functions in the
3121 <structname>FuncCallContext</structname> structure &mdash; use the
3122 <structfield>tuple_desc</structfield> or <structfield>attinmeta</structfield> field
3123 respectively.
3124 </para>
3125
3126 <para>
3127 When working with Datums, use:
3128 <programlisting>
3129 HeapTuple heap_form_tuple(TupleDesc tupdesc, Datum *values, bool *isnull)
3130 </programlisting>
3131 to build a <structname>HeapTuple</structname> given user data in Datum form.
3132 </para>
3133
3134 <para>
3135 When working with C strings, use:
3136 <programlisting>
3137 HeapTuple BuildTupleFromCStrings(AttInMetadata *attinmeta, char **values)
3138 </programlisting>
3139 to build a <structname>HeapTuple</structname> given user data
3140 in C string form. <parameter>values</parameter> is an array of C strings,
3141 one for each attribute of the return row. Each C string should be in
3142 the form expected by the input function of the attribute data
3143 type. In order to return a null value for one of the attributes,
3144 the corresponding pointer in the <parameter>values</parameter> array
3145 should be set to <symbol>NULL</symbol>. This function will need to
3146 be called again for each row you return.
3147 </para>
3148
3149 <para>
3150 Once you have built a tuple to return from your function, it
3151 must be converted into a <type>Datum</type>. Use:
3152 <programlisting>
3153 HeapTupleGetDatum(HeapTuple tuple)
3154 </programlisting>
3155 to convert a <structname>HeapTuple</structname> into a valid Datum. This
3156 <type>Datum</type> can be returned directly if you intend to return
3157 just a single row, or it can be used as the current return value
3158 in a set-returning function.
3159 </para>
3160
3161 <para>
3162 An example appears in the next section.
3163 </para>
3164
3165 </sect2>
3166
3167 <sect2 id="xfunc-c-return-set">
3168 <title>Returning Sets</title>
3169
3170 <para>
3171 C-language functions have two options for returning sets (multiple
3172 rows). In one method, called <firstterm>ValuePerCall</firstterm>
3173 mode, a set-returning function is called repeatedly (passing the same
3174 arguments each time) and it returns one new row on each call, until
3175 it has no more rows to return and signals that by returning NULL.
3176 The set-returning function (<acronym>SRF</acronym>) must therefore
3177 save enough state across calls to remember what it was doing and
3178 return the correct next item on each call.
3179 In the other method, called <firstterm>Materialize</firstterm> mode,
3180 an SRF fills and returns a tuplestore object containing its
3181 entire result; then only one call occurs for the whole result, and
3182 no inter-call state is needed.
3183 </para>
3184
3185 <para>
3186 When using ValuePerCall mode, it is important to remember that the
3187 query is not guaranteed to be run to completion; that is, due to
3188 options such as <literal>LIMIT</literal>, the executor might stop
3189 making calls to the set-returning function before all rows have been
3190 fetched. This means it is not safe to perform cleanup activities in
3191 the last call, because that might not ever happen. It's recommended
3192 to use Materialize mode for functions that need access to external
3193 resources, such as file descriptors.
3194 </para>
3195
3196 <para>
3197 The remainder of this section documents a set of helper macros that
3198 are commonly used (though not required to be used) for SRFs using
3199 ValuePerCall mode. Additional details about Materialize mode can be
3200 found in <filename>src/backend/utils/fmgr/README</filename>. Also,
3201 the <filename>contrib</filename> modules in
3202 the <productname>PostgreSQL</productname> source distribution contain
3203 many examples of SRFs using both ValuePerCall and Materialize mode.
3204 </para>
3205
3206 <para>
3207 To use the ValuePerCall support macros described here,
3208 include <filename>funcapi.h</filename>. These macros work with a
3209 structure <structname>FuncCallContext</structname> that contains the
3210 state that needs to be saved across calls. Within the calling
3211 SRF, <literal>fcinfo-&gt;flinfo-&gt;fn_extra</literal> is used to
3212 hold a pointer to <structname>FuncCallContext</structname> across
3213 calls. The macros automatically fill that field on first use,
3214 and expect to find the same pointer there on subsequent uses.
3215 <programlisting>
3216 typedef struct FuncCallContext
3217 {
3218 /*
3219 * Number of times we've been called before
3220 *
3221 * call_cntr is initialized to 0 for you by SRF_FIRSTCALL_INIT(), and
3222 * incremented for you every time SRF_RETURN_NEXT() is called.
3223 */
3224 uint64 call_cntr;
3225
3226 /*
3227 * OPTIONAL maximum number of calls
3228 *
3229 * max_calls is here for convenience only and setting it is optional.
3230 * If not set, you must provide alternative means to know when the
3231 * function is done.
3232 */
3233 uint64 max_calls;
3234
3235 /*
3236 * OPTIONAL pointer to miscellaneous user-provided context information
3237 *
3238 * user_fctx is for use as a pointer to your own data to retain
3239 * arbitrary context information between calls of your function.
3240 */
3241 void *user_fctx;
3242
3243 /*
3244 * OPTIONAL pointer to struct containing attribute type input metadata
3245 *
3246 * attinmeta is for use when returning tuples (i.e., composite data types)
3247 * and is not used when returning base data types. It is only needed
3248 * if you intend to use BuildTupleFromCStrings() to create the return
3249 * tuple.
3250 */
3251 AttInMetadata *attinmeta;
3252
3253 /*
3254 * memory context used for structures that must live for multiple calls
3255 *
3256 * multi_call_memory_ctx is set by SRF_FIRSTCALL_INIT() for you, and used
3257 * by SRF_RETURN_DONE() for cleanup. It is the most appropriate memory
3258 * context for any memory that is to be reused across multiple calls
3259 * of the SRF.
3260 */
3261 MemoryContext multi_call_memory_ctx;
3262
3263 /*
3264 * OPTIONAL pointer to struct containing tuple description
3265 *
3266 * tuple_desc is for use when returning tuples (i.e., composite data types)
3267 * and is only needed if you are going to build the tuples with
3268 * heap_form_tuple() rather than with BuildTupleFromCStrings(). Note that
3269 * the TupleDesc pointer stored here should usually have been run through
3270 * BlessTupleDesc() first.
3271 */
3272 TupleDesc tuple_desc;
3273
3274 } FuncCallContext;
3275 </programlisting>
3276 </para>
3277
3278 <para>
3279 The macros to be used by an <acronym>SRF</acronym> using this
3280 infrastructure are:
3281 <programlisting>
3282 SRF_IS_FIRSTCALL()
3283 </programlisting>
3284 Use this to determine if your function is being called for the first or a
3285 subsequent time. On the first call (only), call:
3286 <programlisting>
3287 SRF_FIRSTCALL_INIT()
3288 </programlisting>
3289 to initialize the <structname>FuncCallContext</structname>. On every function call,
3290 including the first, call:
3291 <programlisting>
3292 SRF_PERCALL_SETUP()
3293 </programlisting>
3294 to set up for using the <structname>FuncCallContext</structname>.
3295 </para>
3296
3297 <para>
3298 If your function has data to return in the current call, use:
3299 <programlisting>
3300 SRF_RETURN_NEXT(funcctx, result)
3301 </programlisting>
3302 to return it to the caller. (<literal>result</literal> must be of type
3303 <type>Datum</type>, either a single value or a tuple prepared as
3304 described above.) Finally, when your function is finished
3305 returning data, use:
3306 <programlisting>
3307 SRF_RETURN_DONE(funcctx)
3308 </programlisting>
3309 to clean up and end the <acronym>SRF</acronym>.
3310 </para>
3311
3312 <para>
3313 The memory context that is current when the <acronym>SRF</acronym> is called is
3314 a transient context that will be cleared between calls. This means
3315 that you do not need to call <function>pfree</function> on everything
3316 you allocated using <function>palloc</function>; it will go away anyway. However, if you want to allocate
3317 any data structures to live across calls, you need to put them somewhere
3318 else. The memory context referenced by
3319 <structfield>multi_call_memory_ctx</structfield> is a suitable location for any
3320 data that needs to survive until the <acronym>SRF</acronym> is finished running. In most
3321 cases, this means that you should switch into
3322 <structfield>multi_call_memory_ctx</structfield> while doing the
3323 first-call setup.
3324 Use <literal>funcctx-&gt;user_fctx</literal> to hold a pointer to
3325 any such cross-call data structures.
3326 (Data you allocate
3327 in <structfield>multi_call_memory_ctx</structfield> will go away
3328 automatically when the query ends, so it is not necessary to free
3329 that data manually, either.)
3330 </para>
3331
3332 <warning>
3333 <para>
3334 While the actual arguments to the function remain unchanged between
3335 calls, if you detoast the argument values (which is normally done
3336 transparently by the
3337 <function>PG_GETARG_<replaceable>xxx</replaceable></function> macro)
3338 in the transient context then the detoasted copies will be freed on
3339 each cycle. Accordingly, if you keep references to such values in
3340 your <structfield>user_fctx</structfield>, you must either copy them into the
3341 <structfield>multi_call_memory_ctx</structfield> after detoasting, or ensure
3342 that you detoast the values only in that context.
3343 </para>
3344 </warning>
3345
3346 <para>
3347 A complete pseudo-code example looks like the following:
3348 <programlisting>
3349 Datum
3350 my_set_returning_function(PG_FUNCTION_ARGS)
3351 {
3352 FuncCallContext *funcctx;
3353 Datum result;
3354 <replaceable>further declarations as needed</replaceable>
3355
3356 if (SRF_IS_FIRSTCALL())
3357 {
3358 MemoryContext oldcontext;
3359
3360 funcctx = SRF_FIRSTCALL_INIT();
3361 oldcontext = MemoryContextSwitchTo(funcctx-&gt;multi_call_memory_ctx);
3362 /* One-time setup code appears here: */
3363 <replaceable>user code</replaceable>
3364 <replaceable>if returning composite</replaceable>
3365 <replaceable>build TupleDesc, and perhaps AttInMetadata</replaceable>
3366 <replaceable>endif returning composite</replaceable>
3367 <replaceable>user code</replaceable>
3368 MemoryContextSwitchTo(oldcontext);
3369 }
3370
3371 /* Each-time setup code appears here: */
3372 <replaceable>user code</replaceable>
3373 funcctx = SRF_PERCALL_SETUP();
3374 <replaceable>user code</replaceable>
3375
3376 /* this is just one way we might test whether we are done: */
3377 if (funcctx-&gt;call_cntr &lt; funcctx-&gt;max_calls)
3378 {
3379 /* Here we want to return another item: */
3380 <replaceable>user code</replaceable>
3381 <replaceable>obtain result Datum</replaceable>
3382 SRF_RETURN_NEXT(funcctx, result);
3383 }
3384 else
3385 {
3386 /* Here we are done returning items, so just report that fact. */
3387 /* (Resist the temptation to put cleanup code here.) */
3388 SRF_RETURN_DONE(funcctx);
3389 }
3390 }
3391 </programlisting>
3392 </para>
3393
3394 <para>
3395 A complete example of a simple <acronym>SRF</acronym> returning a composite type
3396 looks like:
3397 <programlisting><![CDATA[
3398 PG_FUNCTION_INFO_V1(retcomposite);
3399
3400 Datum
3401 retcomposite(PG_FUNCTION_ARGS)
3402 {
3403 FuncCallContext *funcctx;
3404 int call_cntr;
3405 int max_calls;
3406 TupleDesc tupdesc;
3407 AttInMetadata *attinmeta;
3408
3409 /* stuff done only on the first call of the function */
3410 if (SRF_IS_FIRSTCALL())
3411 {
3412 MemoryContext oldcontext;
3413
3414 /* create a function context for cross-call persistence */
3415 funcctx = SRF_FIRSTCALL_INIT();
3416
3417 /* switch to memory context appropriate for multiple function calls */
3418 oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
3419
3420 /* total number of tuples to be returned */
3421 funcctx->max_calls = PG_GETARG_INT32(0);
3422
3423 /* Build a tuple descriptor for our result type */
3424 if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
3425 ereport(ERROR,
3426 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
3427 errmsg("function returning record called in context "
3428 "that cannot accept type record")));
3429
3430 /*
3431 * generate attribute metadata needed later to produce tuples from raw
3432 * C strings
3433 */
3434 attinmeta = TupleDescGetAttInMetadata(tupdesc);
3435 funcctx->attinmeta = attinmeta;
3436
3437 MemoryContextSwitchTo(oldcontext);
3438 }
3439
3440 /* stuff done on every call of the function */
3441 funcctx = SRF_PERCALL_SETUP();
3442
3443 call_cntr = funcctx->call_cntr;
3444 max_calls = funcctx->max_calls;
3445 attinmeta = funcctx->attinmeta;
3446
3447 if (call_cntr < max_calls) /* do when there is more left to send */
3448 {
3449 char **values;
3450 HeapTuple tuple;
3451 Datum result;
3452
3453 /*
3454 * Prepare a values array for building the returned tuple.
3455 * This should be an array of C strings which will
3456 * be processed later by the type input functions.
3457 */
3458 values = (char **) palloc(3 * sizeof(char *));
3459 values[0] = (char *) palloc(16 * sizeof(char));
3460 values[1] = (char *) palloc(16 * sizeof(char));
3461 values[2] = (char *) palloc(16 * sizeof(char));
3462
3463 snprintf(values[0], 16, "%d", 1 * PG_GETARG_INT32(1));
3464 snprintf(values[1], 16, "%d", 2 * PG_GETARG_INT32(1));
3465 snprintf(values[2], 16, "%d", 3 * PG_GETARG_INT32(1));
3466
3467 /* build a tuple */
3468 tuple = BuildTupleFromCStrings(attinmeta, values);
3469
3470 /* make the tuple into a datum */
3471 result = HeapTupleGetDatum(tuple);
3472
3473 /* clean up (this is not really necessary) */
3474 pfree(values[0]);
3475 pfree(values[1]);
3476 pfree(values[2]);
3477 pfree(values);
3478
3479 SRF_RETURN_NEXT(funcctx, result);
3480 }
3481 else /* do when there is no more left */
3482 {
3483 SRF_RETURN_DONE(funcctx);
3484 }
3485 }
3486 ]]>
3487 </programlisting>
3488
3489 One way to declare this function in SQL is:
3490 <programlisting>
3491 CREATE TYPE __retcomposite AS (f1 integer, f2 integer, f3 integer);
3492
3493 CREATE OR REPLACE FUNCTION retcomposite(integer, integer)
3494 RETURNS SETOF __retcomposite
3495 AS '<replaceable>filename</replaceable>', 'retcomposite'
3496 LANGUAGE C IMMUTABLE STRICT;
3497 </programlisting>
3498 A different way is to use OUT parameters:
3499 <programlisting>
3500 CREATE OR REPLACE FUNCTION retcomposite(IN integer, IN integer,
3501 OUT f1 integer, OUT f2 integer, OUT f3 integer)
3502 RETURNS SETOF record
3503 AS '<replaceable>filename</replaceable>', 'retcomposite'
3504 LANGUAGE C IMMUTABLE STRICT;
3505 </programlisting>
3506 Notice that in this method the output type of the function is formally
3507 an anonymous <structname>record</structname> type.
3508 </para>
3509 </sect2>
3510
3511 <sect2 id="xfunc-c-polymorphic">
3512 <title>Polymorphic Arguments and Return Types</title>
3513
3514 <para>
3515 C-language functions can be declared to accept and
3516 return the polymorphic types described in <xref
3517 linkend="extend-types-polymorphic"/>.
3518 When a function's arguments or return types
3519 are defined as polymorphic types, the function author cannot know
3520 in advance what data type it will be called with, or
3521 need to return. There are two routines provided in <filename>fmgr.h</filename>
3522 to allow a version-1 C function to discover the actual data types
3523 of its arguments and the type it is expected to return. The routines are
3524 called <literal>get_fn_expr_rettype(FmgrInfo *flinfo)</literal> and
3525 <literal>get_fn_expr_argtype(FmgrInfo *flinfo, int argnum)</literal>.
3526 They return the result or argument type OID, or <symbol>InvalidOid</symbol> if the
3527 information is not available.
3528 The structure <literal>flinfo</literal> is normally accessed as
3529 <literal>fcinfo-&gt;flinfo</literal>. The parameter <literal>argnum</literal>
3530 is zero based. <function>get_call_result_type</function> can also be used
3531 as an alternative to <function>get_fn_expr_rettype</function>.
3532 There is also <function>get_fn_expr_variadic</function>, which can be used to
3533 find out whether variadic arguments have been merged into an array.
3534 This is primarily useful for <literal>VARIADIC "any"</literal> functions,
3535 since such merging will always have occurred for variadic functions
3536 taking ordinary array types.
3537 </para>
3538
3539 <para>
3540 For example, suppose we want to write a function to accept a single
3541 element of any type, and return a one-dimensional array of that type:
3542
3543 <programlisting>
3544 PG_FUNCTION_INFO_V1(make_array);
3545 Datum
3546 make_array(PG_FUNCTION_ARGS)
3547 {
3548 ArrayType *result;
3549 Oid element_type = get_fn_expr_argtype(fcinfo-&gt;flinfo, 0);
3550 Datum element;
3551 bool isnull;
3552 int16 typlen;
3553 bool typbyval;
3554 char typalign;
3555 int ndims;
3556 int dims[MAXDIM];
3557 int lbs[MAXDIM];
3558
3559 if (!OidIsValid(element_type))
3560 elog(ERROR, "could not determine data type of input");
3561
3562 /* get the provided element, being careful in case it's NULL */
3563 isnull = PG_ARGISNULL(0);
3564 if (isnull)
3565 element = (Datum) 0;
3566 else
3567 element = PG_GETARG_DATUM(0);
3568
3569 /* we have one dimension */
3570 ndims = 1;
3571 /* and one element */
3572 dims[0] = 1;
3573 /* and lower bound is 1 */
3574 lbs[0] = 1;
3575
3576 /* get required info about the element type */
3577 get_typlenbyvalalign(element_type, &amp;typlen, &amp;typbyval, &amp;typalign);
3578
3579 /* now build the array */
3580 result = construct_md_array(&amp;element, &amp;isnull, ndims, dims, lbs,
3581 element_type, typlen, typbyval, typalign);
3582
3583 PG_RETURN_ARRAYTYPE_P(result);
3584 }
3585 </programlisting>
3586 </para>
3587
3588 <para>
3589 The following command declares the function
3590 <function>make_array</function> in SQL:
3591
3592 <programlisting>
3593 CREATE FUNCTION make_array(anyelement) RETURNS anyarray
3594 AS '<replaceable>DIRECTORY</replaceable>/funcs', 'make_array'
3595 LANGUAGE C IMMUTABLE;
3596 </programlisting>
3597 </para>
3598
3599 <para>
3600 There is a variant of polymorphism that is only available to C-language
3601 functions: they can be declared to take parameters of type
3602 <literal>"any"</literal>. (Note that this type name must be double-quoted,
3603 since it's also an SQL reserved word.) This works like
3604 <type>anyelement</type> except that it does not constrain different
3605 <literal>"any"</literal> arguments to be the same type, nor do they help
3606 determine the function's result type. A C-language function can also
3607 declare its final parameter to be <literal>VARIADIC "any"</literal>. This will
3608 match one or more actual arguments of any type (not necessarily the same
3609 type). These arguments will <emphasis>not</emphasis> be gathered into an array
3610 as happens with normal variadic functions; they will just be passed to
3611 the function separately. The <function>PG_NARGS()</function> macro and the
3612 methods described above must be used to determine the number of actual
3613 arguments and their types when using this feature. Also, users of such
3614 a function might wish to use the <literal>VARIADIC</literal> keyword in their
3615 function call, with the expectation that the function would treat the
3616 array elements as separate arguments. The function itself must implement
3617 that behavior if wanted, after using <function>get_fn_expr_variadic</function> to
3618 detect that the actual argument was marked with <literal>VARIADIC</literal>.
3619 </para>
3620 </sect2>
3621
3622 <sect2 id="xfunc-shared-addin">
3623 <title>Shared Memory</title>
3624
3625 <sect3 id="xfunc-shared-addin-at-startup">
3626 <title>Requesting Shared Memory at Startup</title>
3627
3628 <para>
3629 Add-ins can reserve shared memory on server startup. To do so, the
3630 add-in's shared library must be preloaded by specifying it in
3631 <xref linkend="guc-shared-preload-libraries"/><indexterm><primary>shared_preload_libraries</primary></indexterm>.
3632 The shared library should also register a
3633 <literal>shmem_request_hook</literal> in its
3634 <function>_PG_init</function> function. This
3635 <literal>shmem_request_hook</literal> can reserve shared memory by
3636 calling:
3637 <programlisting>
3638 void RequestAddinShmemSpace(Size size)
3639 </programlisting>
3640 Each backend should obtain a pointer to the reserved shared memory by
3641 calling:
3642 <programlisting>
3643 void *ShmemInitStruct(const char *name, Size size, bool *foundPtr)
3644 </programlisting>
3645 If this function sets <literal>foundPtr</literal> to
3646 <literal>false</literal>, the caller should proceed to initialize the
3647 contents of the reserved shared memory. If <literal>foundPtr</literal>
3648 is set to <literal>true</literal>, the shared memory was already
3649 initialized by another backend, and the caller need not initialize
3650 further.
3651 </para>
3652
3653 <para>
3654 To avoid race conditions, each backend should use the LWLock
3655 <function>AddinShmemInitLock</function> when initializing its allocation
3656 of shared memory, as shown here:
3657 <programlisting>
3658 static mystruct *ptr = NULL;
3659 bool found;
3660
3661 LWLockAcquire(AddinShmemInitLock, LW_EXCLUSIVE);
3662 ptr = ShmemInitStruct("my struct name", size, &amp;found);
3663 if (!found)
3664 {
3665 ... initialize contents of shared memory ...
3666 ptr->locks = GetNamedLWLockTranche("my tranche name");
3667 }
3668 LWLockRelease(AddinShmemInitLock);
3669 </programlisting>
3670 <literal>shmem_startup_hook</literal> provides a convenient place for the
3671 initialization code, but it is not strictly required that all such code
3672 be placed in this hook. On Windows (and anywhere else where
3673 <literal>EXEC_BACKEND</literal> is defined), each backend executes the
3674 registered <literal>shmem_startup_hook</literal> shortly after it
3675 attaches to shared memory, so add-ins should still acquire
3676 <function>AddinShmemInitLock</function> within this hook, as shown in the
3677 example above. On other platforms, only the postmaster process executes
3678 the <literal>shmem_startup_hook</literal>, and each backend automatically
3679 inherits the pointers to shared memory.
3680 </para>
3681
3682 <para>
3683 An example of a <literal>shmem_request_hook</literal> and
3684 <literal>shmem_startup_hook</literal> can be found in
3685 <filename>contrib/pg_stat_statements/pg_stat_statements.c</filename> in
3686 the <productname>PostgreSQL</productname> source tree.
3687 </para>
3688 </sect3>
3689
3690 <sect3 id="xfunc-shared-addin-after-startup">
3691 <title>Requesting Shared Memory After Startup</title>
3692
3693 <para>
3694 There is another, more flexible method of reserving shared memory that
3695 can be done after server startup and outside a
3696 <literal>shmem_request_hook</literal>. To do so, each backend that will
3697 use the shared memory should obtain a pointer to it by calling:
3698 <programlisting>
3699 void *GetNamedDSMSegment(const char *name, size_t size,
3700 void (*init_callback) (void *ptr),
3701 bool *found)
3702 </programlisting>
3703 If a dynamic shared memory segment with the given name does not yet
3704 exist, this function will allocate it and initialize it with the provided
3705 <function>init_callback</function> callback function. If the segment has
3706 already been allocated and initialized by another backend, this function
3707 simply attaches the existing dynamic shared memory segment to the current
3708 backend.
3709 </para>
3710
3711 <para>
3712 Unlike shared memory reserved at server startup, there is no need to
3713 acquire <function>AddinShmemInitLock</function> or otherwise take action
3714 to avoid race conditions when reserving shared memory with
3715 <function>GetNamedDSMSegment</function>. This function ensures that only
3716 one backend allocates and initializes the segment and that all other
3717 backends receive a pointer to the fully allocated and initialized
3718 segment.
3719 </para>
3720
3721 <para>
3722 A complete usage example of <function>GetNamedDSMSegment</function> can
3723 be found in
3724 <filename>src/test/modules/test_dsm_registry/test_dsm_registry.c</filename>
3725 in the <productname>PostgreSQL</productname> source tree.
3726 </para>
3727 </sect3>
3728 </sect2>
3729
3730 <sect2 id="xfunc-addin-lwlocks">
3731 <title>LWLocks</title>
3732
3733 <sect3 id="xfunc-addin-lwlocks-at-startup">
3734 <title>Requesting LWLocks at Startup</title>
3735
3736 <para>
3737 Add-ins can reserve LWLocks on server startup. As with shared memory
3738 reserved at server startup, the add-in's shared library must be preloaded
3739 by specifying it in
3740 <xref linkend="guc-shared-preload-libraries"/><indexterm><primary>shared_preload_libraries</primary></indexterm>,
3741 and the shared library should register a
3742 <literal>shmem_request_hook</literal> in its
3743 <function>_PG_init</function> function. This
3744 <literal>shmem_request_hook</literal> can reserve LWLocks by calling:
3745 <programlisting>
3746 void RequestNamedLWLockTranche(const char *tranche_name, int num_lwlocks)
3747 </programlisting>
3748 This ensures that an array of <literal>num_lwlocks</literal> LWLocks is
3749 available under the name <literal>tranche_name</literal>. A pointer to
3750 this array can be obtained by calling:
3751 <programlisting>
3752 LWLockPadded *GetNamedLWLockTranche(const char *tranche_name)
3753 </programlisting>
3754 </para>
3755 </sect3>
3756
3757 <sect3 id="xfunc-addin-lwlocks-after-startup">
3758 <title>Requesting LWLocks After Startup</title>
3759
3760 <para>
3761 There is another, more flexible method of obtaining LWLocks that can be
3762 done after server startup and outside a
3763 <literal>shmem_request_hook</literal>. To do so, first allocate a
3764 <literal>tranche_id</literal> by calling:
3765 <programlisting>
3766 int LWLockNewTrancheId(void)
3767 </programlisting>
3768 Next, initialize each LWLock, passing the new
3769 <literal>tranche_id</literal> as an argument:
3770 <programlisting>
3771 void LWLockInitialize(LWLock *lock, int tranche_id)
3772 </programlisting>
3773 Similar to shared memory, each backend should ensure that only one
3774 process allocates a new <literal>tranche_id</literal> and initializes
3775 each new LWLock. One way to do this is to only call these functions in
3776 your shared memory initialization code with the
3777 <function>AddinShmemInitLock</function> held exclusively. If using
3778 <function>GetNamedDSMSegment</function>, calling these functions in the
3779 <function>init_callback</function> callback function is sufficient to
3780 avoid race conditions.
3781 </para>
3782
3783 <para>
3784 Finally, each backend using the <literal>tranche_id</literal> should
3785 associate it with a <literal>tranche_name</literal> by calling:
3786 <programlisting>
3787 void LWLockRegisterTranche(int tranche_id, const char *tranche_name)
3788 </programlisting>
3789 </para>
3790
3791 <para>
3792 A complete usage example of <function>LWLockNewTrancheId</function>,
3793 <function>LWLockInitialize</function>, and
3794 <function>LWLockRegisterTranche</function> can be found in
3795 <filename>contrib/pg_prewarm/autoprewarm.c</filename> in the
3796 <productname>PostgreSQL</productname> source tree.
3797 </para>
3798 </sect3>
3799 </sect2>
3800
3801 <sect2 id="xfunc-addin-wait-events">
3802 <title>Custom Wait Events</title>
3803
3804 <para>
3805 Add-ins can define custom wait events under the wait event type
3806 <literal>Extension</literal> by calling:
3807 <programlisting>
3808 uint32 WaitEventExtensionNew(const char *wait_event_name)
3809 </programlisting>
3810 The wait event is associated to a user-facing custom string.
3811 An example can be found in <filename>src/test/modules/worker_spi</filename>
3812 in the PostgreSQL source tree.
3813 </para>
3814 <para>
3815 Custom wait events can be viewed in
3816 <link linkend="monitoring-pg-stat-activity-view"><structname>pg_stat_activity</structname></link>:
3817 <screen>
3818 =# SELECT wait_event_type, wait_event FROM pg_stat_activity
3819 WHERE backend_type ~ 'worker_spi';
3820 wait_event_type | wait_event
3821 -----------------+---------------
3822 Extension | WorkerSpiMain
3823 (1 row)
3824 </screen>
3825 </para>
3826 </sect2>
3827
3828 <sect2 id="xfunc-addin-injection-points">
3829 <title>Injection Points</title>
3830
3831 <para>
3832 An injection point with a given <literal>name</literal> is declared using
3833 macro:
3834 <programlisting>
3835 INJECTION_POINT(name, arg);
3836 </programlisting>
3837
3838 There are a few injection points already declared at strategic points
3839 within the server code. After adding a new injection point the code needs
3840 to be compiled in order for that injection point to be available in the
3841 binary. Add-ins written in C-language can declare injection points in
3842 their own code using the same macro. The injection point names should use
3843 lower-case characters, with terms separated by
3844 dashes. <literal>arg</literal> is an optional argument value given to the
3845 callback at run-time.
3846 </para>
3847
3848 <para>
3849 Executing an injection point can require allocating a small amount of
3850 memory, which can fail. If you need to have an injection point in a
3851 critical section where dynamic allocations are not allowed, you can use
3852 a two-step approach with the following macros:
3853 <programlisting>
3854 INJECTION_POINT_LOAD(name);
3855 INJECTION_POINT_CACHED(name, arg);
3856 </programlisting>
3857
3858 Before entering the critical section,
3859 call <function>INJECTION_POINT_LOAD</function>. It checks the shared
3860 memory state, and loads the callback into backend-private memory if it is
3861 active. Inside the critical section, use
3862 <function>INJECTION_POINT_CACHED</function> to execute the callback.
3863 </para>
3864
3865 <para>
3866 Add-ins can attach callbacks to an already-declared injection point by
3867 calling:
3868 <programlisting>
3869 extern void InjectionPointAttach(const char *name,
3870 const char *library,
3871 const char *function,
3872 const void *private_data,
3873 int private_data_size);
3874 </programlisting>
3875
3876 <literal>name</literal> is the name of the injection point, which when
3877 reached during execution will execute the <literal>function</literal>
3878 loaded from <literal>library</literal>. <literal>private_data</literal>
3879 is a private area of data of size <literal>private_data_size</literal>
3880 given as argument to the callback when executed.
3881 </para>
3882
3883 <para>
3884 Here is an example of callback for
3885 <literal>InjectionPointCallback</literal>:
3886 <programlisting>
3887 static void
3888 custom_injection_callback(const char *name,
3889 const void *private_data,
3890 void *arg)
3891 {
3892 uint32 wait_event_info = WaitEventInjectionPointNew(name);
3893
3894 pgstat_report_wait_start(wait_event_info);
3895 elog(NOTICE, "%s: executed custom callback", name);
3896 pgstat_report_wait_end();
3897 }
3898 </programlisting>
3899 This callback prints a message to server error log with severity
3900 <literal>NOTICE</literal>, but callbacks may implement more complex
3901 logic.
3902 </para>
3903
3904 <para>
3905 An alternative way to define the action to take when an injection point
3906 is reached is to add the testing code alongside the normal source
3907 code. This can be useful if the action e.g. depends on local variables
3908 that are not accessible to loaded modules. The
3909 <function>IS_INJECTION_POINT_ATTACHED</function> macro can then be used
3910 to check if an injection point is attached, for example:
3911 <programlisting>
3912 #ifdef USE_INJECTION_POINTS
3913 if (IS_INJECTION_POINT_ATTACHED("before-foobar"))
3914 {
3915 /* change a local variable if injection point is attached */
3916 local_var = 123;
3917
3918 /* also execute the callback */
3919 INJECTION_POINT_CACHED("before-foobar", NULL);
3920 }
3921 #endif
3922 </programlisting>
3923 Note that the callback attached to the injection point will not be
3924 executed by the <function>IS_INJECTION_POINT_ATTACHED</function>
3925 macro. If you want to execute the callback, you must also call
3926 <function>INJECTION_POINT_CACHED</function> like in the above example.
3927 </para>
3928
3929 <para>
3930 Optionally, it is possible to detach an injection point by calling:
3931 <programlisting>
3932 extern bool InjectionPointDetach(const char *name);
3933 </programlisting>
3934 On success, <literal>true</literal> is returned, <literal>false</literal>
3935 otherwise.
3936 </para>
3937
3938 <para>
3939 A callback attached to an injection point is available across all the
3940 backends including the backends started after
3941 <literal>InjectionPointAttach</literal> is called. It remains attached
3942 while the server is running or until the injection point is detached
3943 using <literal>InjectionPointDetach</literal>.
3944 </para>
3945
3946 <para>
3947 An example can be found in
3948 <filename>src/test/modules/injection_points</filename> in the PostgreSQL
3949 source tree.
3950 </para>
3951
3952 <para>
3953 Enabling injections points requires
3954 <option>--enable-injection-points</option> with
3955 <command>configure</command> or <option>-Dinjection_points=true</option>
3956 with <application>Meson</application>.
3957 </para>
3958 </sect2>
3959
3960 <sect2 id="xfunc-addin-custom-cumulative-statistics">
3961 <title>Custom Cumulative Statistics</title>
3962
3963 <para>
3964 It is possible for add-ins written in C-language to use custom types
3965 of cumulative statistics registered in the
3966 <link linkend="monitoring-stats-setup">Cumulative Statistics System</link>.
3967 </para>
3968
3969 <para>
3970 First, define a <literal>PgStat_KindInfo</literal> that includes all
3971 the information related to the custom type registered. For example:
3972 <programlisting>
3973 static const PgStat_KindInfo custom_stats = {
3974 .name = "custom_stats",
3975 .fixed_amount = false,
3976 .shared_size = sizeof(PgStatShared_Custom),
3977 .shared_data_off = offsetof(PgStatShared_Custom, stats),
3978 .shared_data_len = sizeof(((PgStatShared_Custom *) 0)->stats),
3979 .pending_size = sizeof(PgStat_StatCustomEntry),
3980 }
3981 </programlisting>
3982
3983 Then, each backend that needs to use this custom type needs to register
3984 it with <literal>pgstat_register_kind</literal> and a unique ID used to
3985 store the entries related to this type of statistics:
3986 <programlisting>
3987 extern PgStat_Kind pgstat_register_kind(PgStat_Kind kind,
3988 const PgStat_KindInfo *kind_info);
3989 </programlisting>
3990 While developing a new extension, use
3991 <literal>PGSTAT_KIND_EXPERIMENTAL</literal> for
3992 <parameter>kind</parameter>. When you are ready to release the extension
3993 to users, reserve a kind ID at the
3994 <ulink url="https://wiki.postgresql.org/wiki/CustomCumulativeStats">
3995 Custom Cumulative Statistics</ulink> page.
3996 </para>
3997
3998 <para>
3999 The details of the API for <literal>PgStat_KindInfo</literal> can
4000 be found in <filename>src/include/utils/pgstat_internal.h</filename>.
4001 </para>
4002
4003 <para>
4004 The type of statistics registered is associated with a name and a unique
4005 ID shared across the server in shared memory. Each backend using a
4006 custom type of statistics maintains a local cache storing the information
4007 of each custom <literal>PgStat_KindInfo</literal>.
4008 </para>
4009
4010 <para>
4011 Place the extension module implementing the custom cumulative statistics
4012 type in <xref linkend="guc-shared-preload-libraries"/> so that it will
4013 be loaded early during <productname>PostgreSQL</productname> startup.
4014 </para>
4015
4016 <para>
4017 An example describing how to register and use custom statistics can be
4018 found in <filename>src/test/modules/injection_points</filename>.
4019 </para>
4020 </sect2>
4021
4022 <sect2 id="extend-cpp">
4023 <title>Using C++ for Extensibility</title>
4024
4025 <indexterm zone="extend-cpp">
4026 <primary>C++</primary>
4027 </indexterm>
4028
4029 <para>
4030 Although the <productname>PostgreSQL</productname> backend is written in
4031 C, it is possible to write extensions in C++ if these guidelines are
4032 followed:
4033
4034 <itemizedlist>
4035 <listitem>
4036 <para>
4037 All functions accessed by the backend must present a C interface
4038 to the backend; these C functions can then call C++ functions.
4039 For example, <literal>extern C</literal> linkage is required for
4040 backend-accessed functions. This is also necessary for any
4041 functions that are passed as pointers between the backend and
4042 C++ code.
4043 </para>
4044 </listitem>
4045 <listitem>
4046 <para>
4047 Free memory using the appropriate deallocation method. For example,
4048 most backend memory is allocated using <function>palloc()</function>, so use
4049 <function>pfree()</function> to free it. Using C++
4050 <function>delete</function> in such cases will fail.
4051 </para>
4052 </listitem>
4053 <listitem>
4054 <para>
4055 Prevent exceptions from propagating into the C code (use a catch-all
4056 block at the top level of all <literal>extern C</literal> functions). This
4057 is necessary even if the C++ code does not explicitly throw any
4058 exceptions, because events like out-of-memory can still throw
4059 exceptions. Any exceptions must be caught and appropriate errors
4060 passed back to the C interface. If possible, compile C++ with
4061 <option>-fno-exceptions</option> to eliminate exceptions entirely; in such
4062 cases, you must check for failures in your C++ code, e.g., check for
4063 NULL returned by <function>new()</function>.
4064 </para>
4065 </listitem>
4066 <listitem>
4067 <para>
4068 If calling backend functions from C++ code, be sure that the
4069 C++ call stack contains only plain old data structures
4070 (<acronym>POD</acronym>). This is necessary because backend errors
4071 generate a distant <function>longjmp()</function> that does not properly
4072 unroll a C++ call stack with non-POD objects.
4073 </para>
4074 </listitem>
4075 </itemizedlist>
4076 </para>
4077
4078 <para>
4079 In summary, it is best to place C++ code behind a wall of
4080 <literal>extern C</literal> functions that interface to the backend,
4081 and avoid exception, memory, and call stack leakage.
4082 </para>
4083 </sect2>
4084
4085 </sect1>
4086
4087 <sect1 id="xfunc-optimization">
4088 <title>Function Optimization Information</title>
4089
4090 <indexterm zone="xfunc-optimization">
4091 <primary>optimization information</primary>
4092 <secondary>for functions</secondary>
4093 </indexterm>
4094
4095 <para>
4096 By default, a function is just a <quote>black box</quote> that the
4097 database system knows very little about the behavior of. However,
4098 that means that queries using the function may be executed much less
4099 efficiently than they could be. It is possible to supply additional
4100 knowledge that helps the planner optimize function calls.
4101 </para>
4102
4103 <para>
4104 Some basic facts can be supplied by declarative annotations provided in
4105 the <link linkend="sql-createfunction"><command>CREATE FUNCTION</command></link> command. Most important of
4106 these is the function's <link linkend="xfunc-volatility">volatility
4107 category</link> (<literal>IMMUTABLE</literal>, <literal>STABLE</literal>,
4108 or <literal>VOLATILE</literal>); one should always be careful to
4109 specify this correctly when defining a function.
4110 The parallel safety property (<literal>PARALLEL
4111 UNSAFE</literal>, <literal>PARALLEL RESTRICTED</literal>, or
4112 <literal>PARALLEL SAFE</literal>) must also be specified if you hope
4113 to use the function in parallelized queries.
4114 It can also be useful to specify the function's estimated execution
4115 cost, and/or the number of rows a set-returning function is estimated
4116 to return. However, the declarative way of specifying those two
4117 facts only allows specifying a constant value, which is often
4118 inadequate.
4119 </para>
4120
4121 <para>
4122 It is also possible to attach a <firstterm>planner support
4123 function</firstterm> to an SQL-callable function (called
4124 its <firstterm>target function</firstterm>), and thereby provide
4125 knowledge about the target function that is too complex to be
4126 represented declaratively. Planner support functions have to be
4127 written in C (although their target functions might not be), so this is
4128 an advanced feature that relatively few people will use.
4129 </para>
4130
4131 <para>
4132 A planner support function must have the SQL signature
4133 <programlisting>
4134 supportfn(internal) returns internal
4135 </programlisting>
4136 It is attached to its target function by specifying
4137 the <literal>SUPPORT</literal> clause when creating the target function.
4138 </para>
4139
4140 <para>
4141 The details of the API for planner support functions can be found in
4142 file <filename>src/include/nodes/supportnodes.h</filename> in the
4143 <productname>PostgreSQL</productname> source code. Here we provide
4144 just an overview of what planner support functions can do.
4145 The set of possible requests to a support function is extensible,
4146 so more things might be possible in future versions.
4147 </para>
4148
4149 <para>
4150 Some function calls can be simplified during planning based on
4151 properties specific to the function. For example,
4152 <literal>int4mul(n, 1)</literal> could be simplified to
4153 just <literal>n</literal>. This type of transformation can be
4154 performed by a planner support function, by having it implement
4155 the <literal>SupportRequestSimplify</literal> request type.
4156 The support function will be called for each instance of its target
4157 function found in a query parse tree. If it finds that the particular
4158 call can be simplified into some other form, it can build and return a
4159 parse tree representing that expression. This will automatically work
4160 for operators based on the function, too &mdash; in the example just
4161 given, <literal>n * 1</literal> would also be simplified to
4162 <literal>n</literal>.
4163 (But note that this is just an example; this particular
4164 optimization is not actually performed by
4165 standard <productname>PostgreSQL</productname>.)
4166 We make no guarantee that <productname>PostgreSQL</productname> will
4167 never call the target function in cases that the support function could
4168 simplify. Ensure rigorous equivalence between the simplified
4169 expression and an actual execution of the target function.
4170 </para>
4171
4172 <para>
4173 For target functions that return <type>boolean</type>, it is often useful to estimate
4174 the fraction of rows that will be selected by a <literal>WHERE</literal> clause using that
4175 function. This can be done by a support function that implements
4176 the <literal>SupportRequestSelectivity</literal> request type.
4177 </para>
4178
4179 <para>
4180 If the target function's run time is highly dependent on its inputs,
4181 it may be useful to provide a non-constant cost estimate for it.
4182 This can be done by a support function that implements
4183 the <literal>SupportRequestCost</literal> request type.
4184 </para>
4185
4186 <para>
4187 For target functions that return sets, it is often useful to provide
4188 a non-constant estimate for the number of rows that will be returned.
4189 This can be done by a support function that implements
4190 the <literal>SupportRequestRows</literal> request type.
4191 </para>
4192
4193 <para>
4194 For target functions that return <type>boolean</type>, it may be possible to
4195 convert a function call appearing in <literal>WHERE</literal> into an indexable operator
4196 clause or clauses. The converted clauses might be exactly equivalent
4197 to the function's condition, or they could be somewhat weaker (that is,
4198 they might accept some values that the function condition does not).
4199 In the latter case the index condition is said to
4200 be <firstterm>lossy</firstterm>; it can still be used to scan an index,
4201 but the function call will have to be executed for each row returned by
4202 the index to see if it really passes the <literal>WHERE</literal> condition or not.
4203 To create such conditions, the support function must implement
4204 the <literal>SupportRequestIndexCondition</literal> request type.
4205 </para>
4206 </sect1>