<!--

$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">

<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

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>

</listitem>

</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>

Depending on the implementation language it may also be allowed

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

names).

</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

<title>Examples</title>

<para>

Hereis a trivialexample to help you get started. For more

Hereare some trivialexamples to help you get started. For more

information and examples, see <xref linkend="xfunc">.

<programlisting>

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

RETURN i + 1;

END;

$$ 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>

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>

<!--

$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">

</programlisting>

</para>

<sect2>

<sect2 id="xfunc-sql-base-functions">

<title><acronym>SQL</acronym> Functions on Base Types</title>

<para>

</tip>

<para>

Another way to use a function returning arow result is to pass the

Another way to use a function returning acomposite type is to pass the

result to another function that accepts the correct row type as input:

<screen>

</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>

$$ LANGUAGE SQL;

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 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>