<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.183 2008/11/21 19:31:58 mha Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.184 2008/12/18 18:20:33 tgl Exp $ -->

<!--

Documentation of the system catalogs, directed toward PostgreSQL developers

-->

<entry><structfield>pronargs</structfield></entry>

<entry><type>int2</type></entry>

<entry></entry>

<entry>Number of arguments</entry>

<entry>Number of input arguments</entry>

</row>

<row>

<entry><structfield>pronargdefaults</structfield></entry>

<entry><type>int2</type></entry>

<entry></entry>

<entry>Number of arguments that have defaults</entry>

</row>

<row>

</entry>

</row>

<row>

<entry><structfield>proargdefaults</structfield></entry>

<entry><type>text</type></entry>

<entry></entry>

<entry>

Expression trees (in <function>nodeToString()</function> representation)

for default values. This is a list with

<structfield>pronargdefaults</> elements, corresponding to the last

<replaceable>N</> <emphasis>input</> arguments (i.e., the last

<replaceable>N</> <structfield>proargtypes</> positions).

If none of the arguments have defaults, this field will be null

</entry>

</row>

<row>

<entry><structfield>prosrc</structfield></entry>

<entry><type>text</type></entry>

<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.461 2008/12/04 17:51:26 petere Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.462 2008/12/18 18:20:33 tgl Exp $ -->

<chapter id="functions">

<title>Functions and Operators</title>

<row>

<entry><literal><function>pg_get_function_identity_arguments</function>(<parameter>func_oid</parameter>)</literal></entry>

<entry><type>text</type></entry>

<entry>get argument list to identify a function (withoutargument names,default values)</entry>

<entry>get argument list to identify a function (without default values)</entry>

</row>

<row>

<entry><literal><function>pg_get_function_result</function>(<parameter>func_oid</parameter>)</literal></entry>

of a function, in the form it would need to appear in within

<command>CREATE FUNCTION</>.

<function>pg_get_function_result</function> similarly returns the

appropriate <literal>RETURNS</> clause for the function.

appropriate <literal>RETURNS</> clause for the function.

<function>pg_get_function_identity_arguments</function> returns the

argument list necessary to identify a function, in the form it

would need to appear in within <command>ALTER FUNCTION</>, for

instance. This form omits default values and argument names, for

example.

instance. This form omits default values.

</para>

<para>

<!--

$PostgreSQL: pgsql/doc/src/sgml/ref/create_function.sgml,v 1.82 2008/12/04 17:51:26 petere Exp $

$PostgreSQL: pgsql/doc/src/sgml/ref/create_function.sgml,v 1.83 2008/12/18 18:20:33 tgl Exp $

-->

<refentry id="SQL-CREATEFUNCTION">

<refsynopsisdiv>

<synopsis>

CREATE [ OR REPLACE ] FUNCTION

<replaceable class="parameter">name</replaceable> ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">argtype</replaceable> [ { DEFAULT | = } <replaceable class="parameter">defexpr</replaceable>] [, ...] ] )

<replaceable class="parameter">name</replaceable> ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">argtype</replaceable> [ { DEFAULT | = } <replaceable class="parameter">defexpr</replaceable>] [, ...] ] )

[ RETURNS <replaceable class="parameter">rettype</replaceable>

| RETURNS TABLE ( <replaceable class="parameter">colname</replaceable> <replaceable class="parameter">coltype</replaceable> [, ...] ) ]

{ LANGUAGE <replaceable class="parameter">langname</replaceable>

[ WITH ( <replaceable class="parameter">attribute</replaceable> [, ...] ) ]

</synopsis>

</refsynopsisdiv>

<refsect1 id="sql-createfunction-description">

<title>Description</title>

<listitem>

<para>

The data type(s) of the function's arguments (optionally

The data type(s) of the function's arguments (optionally

schema-qualified), if any. The argument types can be base, composite,

or domain types, or can reference the type of a table column.

</para>

<listitem>

<para>

An expression to be used as default value if the parameter is

not specified. The expression has to be convertable to the

argument type of the parameter. All parameters after a

parameter with default value have to be parameters with default

values as well.

not specified. The expression has to be coercible to the

argument type of the parameter.

Only input (including <literal>INOUT</>) parameters can have a default

value. All input parameters following a

parameter with a default value must have default values as well.

</para>

</listitem>

</varlistentry>

<listitem>

<para>

The return data type (optionally schema-qualified). The return type

The return data type (optionally schema-qualified). The return type

can be a base, composite, or domain type,

or can reference the type of a table column.

Depending on the implementation language it might also be allowed

</programlisting>

</para>

<para>

Functions that have different argument type lists will not be considered

to conflict at creation time, but if defaults are provided they might

conflict in use. For example, consider

<programlisting>

CREATE FUNCTION foo(int) ...

CREATE FUNCTION foo(int, int default 42) ...

</programlisting>

A call <literal>foo(10)</> will fail due to the ambiguity about which

function should be called.

</para>

<para>

When repeated <command>CREATE FUNCTION</command> calls refer to

the same object file, the file is only loaded once per session.

</refsect1>

<refsect1 id="sql-createfunction-compat">

<title>Compatibility</title>

<!-- $PostgreSQL: pgsql/doc/src/sgml/typeconv.sgml,v 1.57 2008/07/30 19:35:12 tgl Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/typeconv.sgml,v 1.58 2008/12/18 18:20:33 tgl Exp $ -->

<chapter Id="typeconv">

<title>Type Conversion</title>

function might have effective argument types identical to some non-variadic

function. In that case the function appearing earlier in the search path is

used, or if the two functions are in the same schema, the non-variadic one is

selected.

preferred.

</para>

</step>

<step performance="optional">

<para>

Functions that have default values for parameters are considered to match any

call that omits zero or more of the defaultable parameter positions. If more

than one such function matches a call, the one appearing earliest in the

search path is used. If there are two or more such functions in the same

schema with identical parameter types in the non-defaulted positions (which is

possible if they have different sets of defaultable parameters), the system

will not be able to determine which to prefer, and so an <quote>ambiguous

function call</> error will result if no better match to the call can be

found.

</para>

</step>

</substeps>

<!-- $PostgreSQL: pgsql/doc/src/sgml/xfunc.sgml,v 1.135 2008/12/07 23:46:39 alvherre Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/xfunc.sgml,v 1.136 2008/12/18 18:20:33 tgl Exp $ -->

<sect1 id="xfunc">

<title>User-Defined Functions</title>

</para>

</sect2>

<sect2 id="xfunc-parameter-defaults">

<title><acronym>SQL</> Functions withParametersDefault Values</title>

<sect2 id="xfunc-sql-parameter-defaults">

<title><acronym>SQL</> Functions with Default Values for Arguments</title>

<indexterm>

<primary>default values</primary>

<primary>function</primary>

<secondary>default values for arguments</secondary>

</indexterm>

<para>

Functions can be declared with parameters with default values or

expressions. The default expressions are used as parameter value

if the parameter is not explicitly specified in a function call.

All parameters after a a parameter with default value have to be

parameters with default values as well.

Functions can be declared with default values for some or all input

arguments. The default values are inserted whenever the function is

called with insufficiently many actual arguments. Since arguments

can only be omitted from the end of the actual argument list, all

parameters after a parameter with a default value have to have

default values as well.

</para>

<para>

For example:

<screen>

CREATE FUNCTION foo(a int DEFAULT 1, b int DEFAULT 2, c int DEFAULT 3)

CREATE FUNCTION foo(a int, b int DEFAULT 2, c int DEFAULT 3)

RETURNS int

LANGUAGE SQL

AS $$

15

(1 row)

SELECT foo();

foo

-----

6

(1 row)

SELECT foo(); -- fails since there is no default for the first argument

ERROR: function foo() does not exist

</screen>

Instead of the key word<literal>DEFAULT</literal>,

the<literal>=</literal> sign can also be used.

The<literal>=</literal> sign can also be used in place of the

key word<literal>DEFAULT</literal>,

</para>

</sect2>