1- <!-- $PostgreSQL: pgsql/doc/src/sgml/xfunc.sgml,v 1.132 2008/07/18 03:32:52 tgl Exp $ -->
1+ <!-- $PostgreSQL: pgsql/doc/src/sgml/xfunc.sgml,v 1.133 2008/10/31 19:37:56 tgl Exp $ -->
22
33 <sect1 id="xfunc">
44 <title>User-Defined Functions</title>
106106 The body of an SQL function must be a list of SQL
107107 statements separated by semicolons. A semicolon after the last
108108 statement is optional. Unless the function is declared to return
109- <type>void</>, the last statement must be a <command>SELECT</>.
109+ <type>void</>, the last statement must be a <command>SELECT</>,
110+ or an <command>INSERT</>, <command>UPDATE</>, or <command>DELETE</>
111+ that has a <literal>RETURNING</> clause.
110112 </para>
111113
112114 <para>
119121 <command>BEGIN</>, <command>COMMIT</>, <command>ROLLBACK</>, or
120122 <command>SAVEPOINT</> commands into a <acronym>SQL</acronym> function.)
121123 However, the final command
122- must be a <command>SELECT</command> that returns whatever is
124+ must be a <command>SELECT</command> or have a <literal>RETURNING</>
125+ clause that returns whatever is
123126 specified as the function's return type. Alternatively, if you
124127 want to define a SQL function that performs actions but has no
125128 useful value to return, you can define it as returning <type>void</>.
126- In that case, the function body must not end with a <command>SELECT</command>.
127129 For example, this function removes rows with negative salaries from
128130 the <literal>emp</> table:
129131
@@ -257,6 +259,16 @@ $$ LANGUAGE SQL;
257259</programlisting>
258260
259261 which adjusts the balance and returns the new balance.
262+ The same thing could be done in one command using <literal>RETURNING</>:
263+
264+ <programlisting>
265+ CREATE FUNCTION tf1 (integer, numeric) RETURNS numeric AS $$
266+ UPDATE bank
267+ SET balance = balance - $2
268+ WHERE accountno = $1
269+ RETURNING balance;
270+ $$ LANGUAGE SQL;
271+ </programlisting>
260272 </para>
261273 </sect2>
262274
@@ -422,7 +434,7 @@ SELECT (new_emp()).name;
422434
423435<screen>
424436SELECT new_emp().name;
425- ERROR: syntax error at or near "." at character 17
437+ ERROR: syntax error at or near "."
426438LINE 1: SELECT new_emp().name;
427439 ^
428440</screen>
@@ -705,7 +717,7 @@ SELECT *, upper(fooname) FROM getfoo(1) AS t1;
705717 <para>
706718 When an SQL function is declared as returning <literal>SETOF
707719 <replaceable>sometype</></literal>, the function's final
708- <command>SELECT</> query is executed to completion, and each row it
720+ query is executed to completion, and each row it
709721 outputs is returned as an element of the result set.
710722 </para>
711723
@@ -798,6 +810,18 @@ SELECT name, listchildren(name) FROM nodes;
798810 This happens because <function>listchildren</function> returns an empty set
799811 for those arguments, so no result rows are generated.
800812 </para>
813+
814+ <note>
815+ <para>
816+ If a function's last command is <command>INSERT</>, <command>UPDATE</>,
817+ or <command>DELETE</> with <literal>RETURNING</>, that command will
818+ always be executed to completion, even if the function is not declared
819+ with <literal>SETOF</> or the calling query does not fetch all the
820+ result rows. Any extra rows produced by the <literal>RETURNING</>
821+ clause are silently dropped, but the commanded table modifications
822+ still happen (and are all completed before returning from the function).
823+ </para>
824+ </note>
801825 </sect2>
802826
803827 <sect2 id="xfunc-sql-functions-returning-table">
@@ -1459,16 +1483,13 @@ PG_MODULE_MAGIC;
14591483 <para>
14601484 By-value types can only be 1, 2, or 4 bytes in length
14611485 (also 8 bytes, if <literal>sizeof(Datum)</literal> is 8 on your machine).
1462- You should be careful
1463- to define your types such that they will be the same
1464- size (in bytes) on all architectures. For example, the
1465- <literal>long</literal> type is dangerous because it
1466- is 4 bytes on some machines and 8 bytes on others, whereas
1467- <type>int</type> type is 4 bytes on most
1468- Unix machines. A reasonable implementation of
1469- the <type>int4</type> type on Unix
1470- machines might be:
1471-
1486+ You should be careful to define your types such that they will be the
1487+ same size (in bytes) on all architectures. For example, the
1488+ <literal>long</literal> type is dangerous because it is 4 bytes on some
1489+ machines and 8 bytes on others, whereas <type>int</type> type is 4 bytes
1490+ on most Unix machines. A reasonable implementation of the
1491+ <type>int4</type> type on Unix machines might be:
1492+
14721493<programlisting>
14731494/* 4-byte integer, passed by value */
14741495typedef int int4;
@@ -1479,7 +1500,7 @@ typedef int int4;
14791500 On the other hand, fixed-length types of any size can
14801501 be passed by-reference. For example, here is a sample
14811502 implementation of a <productname>PostgreSQL</productname> type:
1482-
1503+
14831504<programlisting>
14841505/* 16-byte structure, passed by reference */
14851506typedef struct
@@ -1502,7 +1523,7 @@ typedef struct
15021523 Finally, all variable-length types must also be passed
15031524 by reference. All variable-length types must begin
15041525 with a length field of exactly 4 bytes, and all data to
1505- be stored within that type must be located in the memory
1526+ be stored within that type must be located in the memory
15061527 immediately following that length field. The
15071528 length field contains the total length of the structure,
15081529 that is, it includes the size of the length field
@@ -1540,8 +1561,8 @@ typedef struct {
15401561 </para>
15411562
15421563 <para>
1543- When manipulating
1544- variable-length types, we must be careful to allocate
1564+ When manipulating
1565+ variable-length types, we must be careful to allocate
15451566 the correct amount of memory and set the length field correctly.
15461567 For example, if we wanted to store 40 bytes in a <structname>text</>
15471568 structure, we might use a code fragment like this:
@@ -1772,7 +1793,7 @@ memcpy(destination->data, buffer, 40);
17721793#include <string.h>
17731794
17741795/* by value */
1775-
1796+
17761797int
17771798add_one(int arg)
17781799{
@@ -1787,7 +1808,7 @@ add_one_float8(float8 *arg)
17871808 float8 *result = (float8 *) palloc(sizeof(float8));
17881809
17891810 *result = *arg + 1.0;
1790-
1811+
17911812 return result;
17921813}
17931814
@@ -1798,7 +1819,7 @@ makepoint(Point *pointx, Point *pointy)
17981819
17991820 new_point->x = pointx->x;
18001821 new_point->y = pointy->y;
1801-
1822+
18021823 return new_point;
18031824}
18041825
@@ -1841,7 +1862,7 @@ concat_text(text *arg1, text *arg2)
18411862 <filename>funcs.c</filename> and compiled into a shared object,
18421863 we could define the functions to <productname>PostgreSQL</productname>
18431864 with commands like this:
1844-
1865+
18451866<programlisting>
18461867CREATE FUNCTION add_one(integer) RETURNS integer
18471868 AS '<replaceable>DIRECTORY</replaceable>/funcs', 'add_one'
@@ -1855,7 +1876,7 @@ CREATE FUNCTION add_one(double precision) RETURNS double precision
18551876CREATE FUNCTION makepoint(point, point) RETURNS point
18561877 AS '<replaceable>DIRECTORY</replaceable>/funcs', 'makepoint'
18571878 LANGUAGE C STRICT;
1858-
1879+
18591880CREATE FUNCTION copytext(text) RETURNS text
18601881 AS '<replaceable>DIRECTORY</replaceable>/funcs', 'copytext'
18611882 LANGUAGE C STRICT;
@@ -1947,7 +1968,7 @@ PG_FUNCTION_INFO_V1(funcname);
19471968/* by value */
19481969
19491970PG_FUNCTION_INFO_V1(add_one);
1950-
1971+
19511972Datum
19521973add_one(PG_FUNCTION_ARGS)
19531974{
@@ -1981,7 +2002,7 @@ makepoint(PG_FUNCTION_ARGS)
19812002
19822003 new_point->x = pointx->x;
19832004 new_point->y = pointy->y;
1984-
2005+
19852006 PG_RETURN_POINT_P(new_point);
19862007}
19872008
@@ -2447,7 +2468,7 @@ include $(PGXS)
24472468 in the <literal>results/</literal> directory), and copying them to
24482469 <literal>expected/</literal> if they match what you want from the test.
24492470 </para>
2450-
2471+
24512472 </tip>
24522473 </sect2>
24532474
@@ -2476,7 +2497,7 @@ SELECT name, c_overpaid(emp, 1500) AS overpaid
24762497
24772498 Using call conventions version 0, we can define
24782499 <function>c_overpaid</> as:
2479-
2500+
24802501<programlisting>
24812502#include "postgres.h"
24822503#include "executor/executor.h" /* for GetAttributeByName() */
@@ -2522,11 +2543,11 @@ c_overpaid(PG_FUNCTION_ARGS)
25222543 </para>
25232544
25242545 <para>
2525- <function>GetAttributeByName</function> is the
2546+ <function>GetAttributeByName</function> is the
25262547 <productname>PostgreSQL</productname> system function that
25272548 returns attributes out of the specified row. It has
25282549 three arguments: the argument of type <type>HeapTupleHeader</type> passed
2529- into
2550+ into
25302551 the function, the name of the desired attribute, and a
25312552 return parameter that tells whether the attribute
25322553 is null. <function>GetAttributeByName</function> returns a <type>Datum</type>
@@ -2733,7 +2754,7 @@ typedef struct
27332754{
27342755 /*
27352756 * Number of times we've been called before
2736- *
2757+ *
27372758 * call_cntr is initialized to 0 for you by SRF_FIRSTCALL_INIT(), and
27382759 * incremented for you every time SRF_RETURN_NEXT() is called.
27392760 */
@@ -2750,23 +2771,23 @@ typedef struct
27502771
27512772 /*
27522773 * OPTIONAL pointer to result slot
2753- *
2774+ *
27542775 * This is obsolete and only present for backwards compatibility, viz,
27552776 * user-defined SRFs that use the deprecated TupleDescGetSlot().
27562777 */
27572778 TupleTableSlot *slot;
27582779
27592780 /*
27602781 * OPTIONAL pointer to miscellaneous user-provided context information
2761- *
2782+ *
27622783 * user_fctx is for use as a pointer to your own data to retain
27632784 * arbitrary context information between calls of your function.
27642785 */
27652786 void *user_fctx;
27662787
27672788 /*
27682789 * OPTIONAL pointer to struct containing attribute type input metadata
2769- *
2790+ *
27702791 * attinmeta is for use when returning tuples (i.e., composite data types)
27712792 * and is not used when returning base data types. It is only needed
27722793 * if you intend to use BuildTupleFromCStrings() to create the return
@@ -2948,7 +2969,7 @@ retcomposite(PG_FUNCTION_ARGS)
29482969 call_cntr = funcctx->call_cntr;
29492970 max_calls = funcctx->max_calls;
29502971 attinmeta = funcctx->attinmeta;
2951-
2972+
29522973 if (call_cntr < max_calls) /* do when there is more left to send */
29532974 {
29542975 char **values;
@@ -3126,7 +3147,7 @@ CREATE FUNCTION make_array(anyelement) RETURNS anyarray
31263147 <para>
31273148 Add-ins can reserve LWLocks and an allocation of shared memory on server
31283149 startup. The add-in's shared library must be preloaded by specifying
3129- it in
3150+ it in
31303151 <xref linkend="guc-shared-preload-libraries"><indexterm><primary>shared-preload-libraries</></>.
31313152 Shared memory is reserved by calling:
31323153<programlisting>
@@ -3139,11 +3160,11 @@ void RequestAddinShmemSpace(int size)
31393160<programlisting>
31403161void RequestAddinLWLocks(int n)
31413162</programlisting>
3142- from <function>_PG_init</>.
3163+ from <function>_PG_init</>.
31433164 </para>
31443165 <para>
31453166 To avoid possible race-conditions, each backend should use the LWLock
3146- <function>AddinShmemInitLock</> when connecting to and initializing
3167+ <function>AddinShmemInitLock</> when connecting to and initializing
31473168 its allocation of shared memory, as shown here:
31483169<programlisting>
31493170 static mystruct *ptr = NULL;