diff options
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/sgml/ref/create_function.sgml | 76 | ||||
| -rw-r--r-- | doc/src/sgml/xfunc.sgml | 108 |
2 files changed, 171 insertions, 13 deletions
diff --git a/doc/src/sgml/ref/create_function.sgml b/doc/src/sgml/ref/create_function.sgml index 0991e96a54a..768a42846b6 100644 --- a/doc/src/sgml/ref/create_function.sgml +++ b/doc/src/sgml/ref/create_function.sgml @@ -1,5 +1,5 @@ <!-- -$PostgreSQL: pgsql/doc/src/sgml/ref/create_function.sgml,v 1.64 2005/01/04 00:39:53 tgl Exp $ +$PostgreSQL: pgsql/doc/src/sgml/ref/create_function.sgml,v 1.65 2005/03/31 22:45:59 tgl Exp $ --> <refentry id="SQL-CREATEFUNCTION"> @@ -19,8 +19,9 @@ $PostgreSQL: pgsql/doc/src/sgml/ref/create_function.sgml,v 1.64 2005/01/04 00:39 <refsynopsisdiv> <synopsis> -CREATE [ OR REPLACE ] FUNCTION <replaceable class="parameter">name</replaceable> ( [ [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">argtype</replaceable> [, ...] ] ) - RETURNS <replaceable class="parameter">rettype</replaceable> +CREATE [ OR REPLACE ] FUNCTION + <replaceable class="parameter">name</replaceable> ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">argtype</replaceable> [, ...] ] ) + [ RETURNS <replaceable class="parameter">rettype</replaceable> ] { LANGUAGE <replaceable class="parameter">langname</replaceable> | IMMUTABLE | STABLE | VOLATILE | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT @@ -57,7 +58,9 @@ CREATE [ OR REPLACE ] FUNCTION <replaceable class="parameter">name</replaceable> tried, you would actually be creating a new, distinct function). Also, <command>CREATE OR REPLACE FUNCTION</command> will not let you change the return type of an existing function. To do that, - you must drop and recreate the function. + you must drop and recreate the function. (When using <literal>OUT</> + parameters, that means you can't change the names or types of any + <literal>OUT</> parameters except by dropping the function.) </para> <para> @@ -89,13 +92,27 @@ CREATE [ OR REPLACE ] FUNCTION <replaceable class="parameter">name</replaceable> </varlistentry> <varlistentry> + <term><replaceable class="parameter">argmode</replaceable></term> + + <listitem> + <para> + The mode of an argument: either <literal>IN</>, <literal>OUT</>, + or <literal>INOUT</>. If omitted, the default is <literal>IN</>. + </para> + </listitem> + </varlistentry> + + <varlistentry> <term><replaceable class="parameter">argname</replaceable></term> <listitem> <para> The name of an argument. Some languages (currently only PL/pgSQL) let you use the name in the function body. For other languages the - argument name is just extra documentation. + name of an input argument is just extra documentation. But the name + of an output argument is significant, since it defines the column + name in the result row type. (If you omit the name for an output + argument, the system will choose a default column name.) </para> </listitem> </varlistentry> @@ -138,6 +155,13 @@ CREATE [ OR REPLACE ] FUNCTION <replaceable class="parameter">name</replaceable> to specify <quote>pseudotypes</> such as <type>cstring</>. </para> <para> + When there are <literal>OUT</> or <literal>INOUT</> parameters, + the <literal>RETURNS</> clause may be omitted. If present, it + must agree with the result type implied by the output parameters: + <literal>RECORD</> if there are multiple output parameters, or + the same type as the single output parameter. + </para> + <para> The <literal>SETOF</literal> modifier indicates that the function will return a set of items, rather than a single item. @@ -362,6 +386,16 @@ CREATE [ OR REPLACE ] FUNCTION <replaceable class="parameter">name</replaceable> </para> <para> + Two functions are considered the same if they have the same names and + <emphasis>input</> argument types, ignoring any <literal>OUT</> + parameters. Thus for example these declarations conflict: +<programlisting> +CREATE FUNCTION foo(int) ... +CREATE FUNCTION foo(int, out text) ... +</programlisting> + </para> + + <para> When repeated <command>CREATE FUNCTION</command> calls refer to the same object file, the file is only loaded once. To unload and reload the file (perhaps during development), use the <xref @@ -393,7 +427,7 @@ CREATE [ OR REPLACE ] FUNCTION <replaceable class="parameter">name</replaceable> <title>Examples</title> <para> - Here is a trivial example to help you get started. For more + Here are some trivial examples to help you get started. For more information and examples, see <xref linkend="xfunc">. <programlisting> CREATE FUNCTION add(integer, integer) RETURNS integer @@ -407,7 +441,6 @@ CREATE FUNCTION add(integer, integer) RETURNS integer <para> Increment an integer, making use of an argument name, in <application>PL/pgSQL</application>: - <programlisting> CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS $$ BEGIN @@ -416,6 +449,28 @@ CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS $$ $$ LANGUAGE plpgsql; </programlisting> </para> + + <para> + Return a record containing multiple output parameters: +<programlisting> +CREATE FUNCTION dup(in int, out f1 int, out f2 text) + AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$ + LANGUAGE SQL; + +SELECT * FROM dup(42); +</programlisting> + You can do the same thing more verbosely with an explicitly named + composite type: +<programlisting> +CREATE TYPE dup_result AS (f1 int, f2 text); + +CREATE FUNCTION dup(int) RETURNS dup_result + AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$ + LANGUAGE SQL; + +SELECT * FROM dup(42); +</programlisting> + </para> </refsect1> @@ -428,6 +483,13 @@ $$ LANGUAGE plpgsql; not fully compatible. The attributes are not portable, neither are the different available languages. </para> + + <para> + For compatibility with some other database systems, + <replaceable class="parameter">argmode</replaceable> can be written + either before or after <replaceable class="parameter">argname</replaceable>. + But only the first way is standard-compliant. + </para> </refsect1> diff --git a/doc/src/sgml/xfunc.sgml b/doc/src/sgml/xfunc.sgml index 83af1f93f70..079773d0d46 100644 --- a/doc/src/sgml/xfunc.sgml +++ b/doc/src/sgml/xfunc.sgml @@ -1,5 +1,5 @@ <!-- -$PostgreSQL: pgsql/doc/src/sgml/xfunc.sgml,v 1.101 2005/03/16 21:38:04 tgl Exp $ +$PostgreSQL: pgsql/doc/src/sgml/xfunc.sgml,v 1.102 2005/03/31 22:46:02 tgl Exp $ --> <sect1 id="xfunc"> @@ -172,7 +172,7 @@ INSERT INTO $1 VALUES (42); </programlisting> </para> - <sect2> + <sect2 id="xfunc-sql-base-functions"> <title><acronym>SQL</acronym> Functions on Base Types</title> <para> @@ -484,7 +484,7 @@ SELECT emp.name, emp.double_salary FROM emp; </tip> <para> - Another way to use a function returning a row result is to pass the + Another way to use a function returning a composite type is to pass the result to another function that accepts the correct row type as input: <screen> @@ -501,8 +501,89 @@ SELECT getname(new_emp()); </para> <para> - Another way to use a function that returns a composite type is to - call it as a table function, as described below. + Still another way to use a function that returns a composite type is to + call it as a table function, as described in <xref + linkend="xfunc-sql-table-functions">. + </para> + </sect2> + + <sect2 id="xfunc-output-parameters"> + <title>Functions with Output Parameters</title> + + <indexterm> + <primary>function</primary> + <secondary>output parameter</secondary> + </indexterm> + + <para> + An alternative way of describing a function's results is to define it + with <firstterm>output parameters</>, as in this example: + +<screen> +CREATE FUNCTION add_em (IN x int, IN y int, OUT sum int) +AS 'SELECT $1 + $2' +LANGUAGE SQL; + +SELECT add_em(3,7); + add_em +-------- + 10 +(1 row) +</screen> + + This is not essentially different from the version of <literal>add_em</> + shown in <xref linkend="xfunc-sql-base-functions">. The real value of + output parameters is that they provide a convenient way of defining + functions that return several columns. For example, + +<screen> +CREATE FUNCTION sum_n_product (x int, y int, OUT sum int, OUT product int) +AS 'SELECT $1 + $2, $1 * $2' +LANGUAGE SQL; + + SELECT * FROM sum_n_product(11,42); + sum | product +-----+--------- + 53 | 462 +(1 row) +</screen> + + What has essentially happened here is that we have created an anonymous + composite type for the result of the function. The above example has + the same end result as + +<screen> +CREATE TYPE sum_prod AS (sum int, product int); + +CREATE FUNCTION sum_n_product (int, int) RETURNS sum_prod +AS 'SELECT $1 + $2, $1 * $2' +LANGUAGE SQL; +</screen> + + but not having to bother with the separate composite type definition + is often handy. + </para> + + <para> + Notice that output parameters are not included in the calling argument + list when invoking such a function from SQL. This is because + <productname>PostgreSQL</productname> considers only the input + parameters to define the function's calling signature. That means + also that only the input parameters matter when referencing the function + for purposes such as dropping it. We could drop the above function + with either of + +<screen> +DROP FUNCTION sum_n_product (x int, y int, OUT sum int, OUT product int); +DROP FUNCTION sum_n_product (int, int); +</screen> + </para> + + <para> + Parameters can be marked as <literal>IN</> (the default), + <literal>OUT</>, or <literal>INOUT</>. An <literal>INOUT</> + parameter serves as both an input parameter (part of the calling + argument list) and an output parameter (part of the result record type). </para> </sect2> @@ -694,6 +775,21 @@ ERROR: cannot determine result data type DETAIL: A function returning "anyarray" or "anyelement" must have at least one argument of either type. </screen> </para> + + <para> + Polymorphism can be used with functions that have output arguments. + For example: +<screen> +CREATE FUNCTION dup (f1 anyelement, OUT f2 anyelement, OUT f3 anyarray) +AS 'select $1, array[$1,$1]' LANGUAGE sql; + +SELECT * FROM dup(22); + f2 | f3 +----+--------- + 22 | {22,22} +(1 row) +</screen> + </para> </sect2> </sect1> @@ -962,7 +1058,7 @@ CREATE FUNCTION square_root(double precision) RETURNS double precision <sect1 id="xfunc-c"> <title>C-Language Functions</title> - <indexterm zone="xfunc-sql"> + <indexterm zone="xfunc-c"> <primary>function</primary> <secondary>user-defined</secondary> <tertiary>in C</tertiary> |