aboutsummaryrefslogtreecommitdiff
path: root/doc/src
diff options
context:
space:
mode:
Diffstat (limited to 'doc/src')
-rw-r--r--doc/src/sgml/ref/create_function.sgml23
-rw-r--r--doc/src/sgml/sources.sgml4
-rw-r--r--doc/src/sgml/syntax.sgml171
-rw-r--r--doc/src/sgml/xfunc.sgml67
4 files changed, 253 insertions, 12 deletions
diff --git a/doc/src/sgml/ref/create_function.sgml b/doc/src/sgml/ref/create_function.sgml
index 0843a175638..6d7eb84d8f2 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.87 2009/10/02 18:13:04 tgl Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/create_function.sgml,v 1.88 2009/10/08 02:39:14 tgl Exp $
-->
<refentry id="SQL-CREATEFUNCTION">
@@ -65,7 +65,7 @@ CREATE [ OR REPLACE ] 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. (When using <literal>OUT</>
- parameters, that means you cannot change the names or types of any
+ parameters, that means you cannot change the types of any
<literal>OUT</> parameters except by dropping the function.)
</para>
@@ -121,8 +121,11 @@ CREATE [ OR REPLACE ] FUNCTION
<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
- name of an input argument is just extra documentation. But the name
- of an output argument is significant, since it defines the column
+ name of an input argument is just extra documentation, so far as
+ the function itself is concerned; but you can use input argument names
+ when calling a function to improve readability (see <xref
+ linkend="sql-syntax-calling-funcs">). In any case, the name
+ of an output argument is significant, because 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>
@@ -570,6 +573,18 @@ CREATE FUNCTION foo(int, int default 42) ...
to replace it (this includes being a member of the owning role).
</para>
+ <para>
+ When replacing an existing function with <command>CREATE OR REPLACE
+ FUNCTION</>, there are restrictions on changing parameter names.
+ You cannot change the name already assigned to any input parameter
+ (although you can add names to parameters that had none before).
+ If there is more than one output parameter, you cannot change the
+ names of the output parameters, because that would change the
+ column names of the anonymous composite type that describes the
+ function's result. These restrictions are made to ensure that
+ existing calls of the function do not stop working when it is replaced.
+ </para>
+
</refsect1>
<refsect1 id="sql-createfunction-examples">
diff --git a/doc/src/sgml/sources.sgml b/doc/src/sgml/sources.sgml
index 0872eacee76..342f8e4ef73 100644
--- a/doc/src/sgml/sources.sgml
+++ b/doc/src/sgml/sources.sgml
@@ -1,4 +1,4 @@
-<!-- $PostgreSQL: pgsql/doc/src/sgml/sources.sgml,v 2.34 2009/06/04 18:33:06 tgl Exp $ -->
+<!-- $PostgreSQL: pgsql/doc/src/sgml/sources.sgml,v 2.35 2009/10/08 02:39:16 tgl Exp $ -->
<chapter id="source">
<title>PostgreSQL Coding Conventions</title>
@@ -125,7 +125,7 @@ ereport(ERROR,
(errcode(ERRCODE_AMBIGUOUS_FUNCTION),
errmsg("function %s is not unique",
func_signature_string(funcname, nargs,
- actual_arg_types)),
+ NIL, actual_arg_types)),
errhint("Unable to choose a best candidate function. "
"You might need to add explicit typecasts.")));
</programlisting>
diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml
index 73db3235bd6..20f7085a8d3 100644
--- a/doc/src/sgml/syntax.sgml
+++ b/doc/src/sgml/syntax.sgml
@@ -1,4 +1,4 @@
-<!-- $PostgreSQL: pgsql/doc/src/sgml/syntax.sgml,v 1.136 2009/09/22 23:52:53 petere Exp $ -->
+<!-- $PostgreSQL: pgsql/doc/src/sgml/syntax.sgml,v 1.137 2009/10/08 02:39:16 tgl Exp $ -->
<chapter id="sql-syntax">
<title>SQL Syntax</title>
@@ -1505,6 +1505,11 @@ sqrt(2)
The list of built-in functions is in <xref linkend="functions">.
Other functions can be added by the user.
</para>
+
+ <para>
+ The arguments can optionally have names attached.
+ See <xref linkend="sql-syntax-calling-funcs"> for details.
+ </para>
</sect2>
<sect2 id="syntax-aggregates">
@@ -2123,4 +2128,168 @@ SELECT ... WHERE CASE WHEN x &gt; 0 THEN y/x &gt; 1.5 ELSE false END;
</sect2>
</sect1>
+ <sect1 id="sql-syntax-calling-funcs">
+ <title>Calling Functions</title>
+
+ <indexterm zone="sql-syntax-calling-funcs">
+ <primary>notation</primary>
+ <secondary>functions</secondary>
+ </indexterm>
+
+ <para>
+ <productname>PostgreSQL</productname> allows functions that have named
+ parameters to be called using either <firstterm>positional</firstterm> or
+ <firstterm>named</firstterm> notation. Named notation is especially
+ useful for functions that have a large number of parameters, since it
+ makes the associations between parameters and actual arguments more
+ explicit and reliable.
+ In positional notation, a function call is written with
+ its argument values in the same order as they are defined in the function
+ declaration. In named notation, the arguments are matched to the
+ function parameters by name and can be written in any order.
+ </para>
+
+ <para>
+ In either notation, parameters that have default values given in the
+ function declaration need not be written in the call at all. But this
+ is particularly useful in named notation, since any combination of
+ parameters can be omitted; while in positional notation parameters can
+ only be omitted from right to left.
+ </para>
+
+ <para>
+ <productname>PostgreSQL</productname> also supports
+ <firstterm>mixed</firstterm> notation, which combines positional and
+ named notation. In this case, positional parameters are written first
+ and named parameters appear after them.
+ </para>
+
+ <para>
+ The following examples will illustrate the usage of all three
+ notations, using the following function definition:
+<programlisting>
+CREATE FUNCTION concat_lower_or_upper(a text, b text, uppercase boolean DEFAULT false)
+RETURNS text
+AS
+$$
+ SELECT CASE
+ WHEN $3 THEN UPPER($1 || ' ' || $2)
+ ELSE LOWER($1 || ' ' || $2)
+ END;
+$$
+LANGUAGE SQL IMMUTABLE STRICT;
+</programlisting>
+ Function <function>concat_lower_or_upper</function> has two mandatory
+ parameters, <literal>a</literal> and <literal>b</literal>. Additionally
+ there is one optional parameter <literal>uppercase</literal> which defaults
+ to <literal>false</literal>. The <literal>a</literal> and
+ <literal>b</literal> inputs will be concatenated, and forced to either
+ upper or lower case depending on the <literal>uppercase</literal>
+ parameter. The remaining details of this function
+ definition are not important here (see <xref linkend="extend"> for
+ more information).
+ </para>
+
+ <sect2 id="sql-syntax-calling-funcs-positional">
+ <title>Using positional notation</title>
+
+ <indexterm>
+ <primary>function</primary>
+ <secondary>positional notation</secondary>
+ </indexterm>
+
+ <para>
+ Positional notation is the traditional mechanism for passing arguments
+ to functions in <productname>PostgreSQL</productname>. An example is:
+<screen>
+SELECT concat_lower_or_upper('Hello', 'World', true);
+ concat_lower_or_upper
+-----------------------
+ HELLO WORLD
+(1 row)
+</screen>
+ All arguments are specified in order. The result is upper case since
+ <literal>uppercase</literal> is specified as <literal>true</literal>.
+ Another example is:
+<screen>
+SELECT concat_lower_or_upper('Hello', 'World');
+ concat_lower_or_upper
+-----------------------
+ hello world
+(1 row)
+</screen>
+ Here, the <literal>uppercase</literal> parameter is omitted, so it
+ receives its default value of <literal>false</literal>, resulting in
+ lower case output. In positional notation, arguments can be omitted
+ from right to left so long as they have defaults.
+ </para>
+ </sect2>
+
+ <sect2 id="sql-syntax-calling-funcs-named">
+ <title>Using named notation</title>
+
+ <indexterm>
+ <primary>function</primary>
+ <secondary>named notation</secondary>
+ </indexterm>
+
+ <para>
+ In named notation, each argument's name is specified using the
+ <literal>AS</literal> keyword. For example:
+<screen>
+SELECT concat_lower_or_upper('Hello' AS a, 'World' AS b);
+ concat_lower_or_upper
+-----------------------
+ hello world
+(1 row)
+</screen>
+ Again, the argument <literal>uppercase</literal> was omitted
+ so it is set to <literal>false</literal> implicitly. One advantage of
+ using named notation is that the arguments may be specified in any
+ order, for example:
+<screen>
+SELECT concat_lower_or_upper('Hello' AS a, 'World' AS b, true AS uppercase);
+ concat_lower_or_upper
+-----------------------
+ HELLO WORLD
+(1 row)
+
+SELECT concat_lower_or_upper('Hello' AS a, true AS uppercase, 'World' AS b);
+ concat_lower_or_upper
+-----------------------
+ HELLO WORLD
+(1 row)
+</screen>
+ </para>
+ </sect2>
+
+ <sect2 id="sql-syntax-calling-funcs-mixed">
+ <title>Using mixed notation</title>
+
+ <indexterm>
+ <primary>function</primary>
+ <secondary>mixed notation</secondary>
+ </indexterm>
+
+ <para>
+ The mixed notation combines positional and named notation. However, as
+ already mentioned, named arguments cannot precede positional arguments.
+ For example:
+<screen>
+SELECT concat_lower_or_upper('Hello', 'World', true AS uppercase);
+ concat_lower_or_upper
+-----------------------
+ HELLO WORLD
+(1 row)
+</screen>
+ In the above query, the arguments <literal>a</literal> and
+ <literal>b</literal> are specified positionally, while
+ <literal>uppercase</> is specified by name. In this example,
+ that adds little except documentation. With a more complex function
+ having numerous parameters that have default values, named or mixed
+ notation can save a great deal of writing and reduce chances for error.
+ </para>
+ </sect2>
+ </sect1>
+
</chapter>
diff --git a/doc/src/sgml/xfunc.sgml b/doc/src/sgml/xfunc.sgml
index 6d85d2d2629..1c20f15226a 100644
--- a/doc/src/sgml/xfunc.sgml
+++ b/doc/src/sgml/xfunc.sgml
@@ -1,4 +1,4 @@
-<!-- $PostgreSQL: pgsql/doc/src/sgml/xfunc.sgml,v 1.139 2009/09/03 22:11:07 tgl Exp $ -->
+<!-- $PostgreSQL: pgsql/doc/src/sgml/xfunc.sgml,v 1.140 2009/10/08 02:39:16 tgl Exp $ -->
<sect1 id="xfunc">
<title>User-Defined Functions</title>
@@ -517,6 +517,39 @@ SELECT getname(new_emp());
</para>
</sect2>
+ <sect2 id="xfunc-named-parameters">
+ <title><acronym>SQL</> Functions with Parameter Names</title>
+
+ <indexterm>
+ <primary>function</primary>
+ <secondary>named parameter</secondary>
+ </indexterm>
+
+ <para>
+ It is possible to attach names to a function's parameters, for example
+
+<programlisting>
+CREATE FUNCTION tf1 (acct_no integer, debit numeric) RETURNS numeric AS $$
+ UPDATE bank
+ SET balance = balance - $2
+ WHERE accountno = $1
+ RETURNING balance;
+$$ LANGUAGE SQL;
+</programlisting>
+
+ Here the first parameter has been given the name <literal>acct_no</>,
+ and the second parameter the name <literal>debit</>.
+ So far as the SQL function itself is concerned, these names are just
+ decoration; you must still refer to the parameters as <literal>$1</>,
+ <literal>$2</>, etc within the function body. (Some procedural
+ languages let you use the parameter names instead.) However,
+ attaching names to the parameters is useful for documentation purposes.
+ When a function has many parameters, it is also useful to use the names
+ while calling the function, as described in
+ <xref linkend="sql-syntax-calling-funcs">.
+ </para>
+ </sect2>
+
<sect2 id="xfunc-output-parameters">
<title><acronym>SQL</> Functions with Output Parameters</title>
@@ -571,7 +604,10 @@ LANGUAGE SQL;
</screen>
but not having to bother with the separate composite type definition
- is often handy.
+ is often handy. Notice that the names attached to the output parameters
+ are not just decoration, but determine the column names of the anonymous
+ composite type. (If you omit a name for an output parameter, the
+ system will choose a name on its own.)
</para>
<para>
@@ -621,7 +657,7 @@ DROP FUNCTION sum_n_product (int, int);
must be declared as being of an array type. For example:
<screen>
-CREATE FUNCTION mleast(VARIADIC numeric[]) RETURNS numeric AS $$
+CREATE FUNCTION mleast(VARIADIC arr numeric[]) RETURNS numeric AS $$
SELECT min($1[i]) FROM generate_subscripts($1, 1) g(i);
$$ LANGUAGE SQL;
@@ -661,6 +697,25 @@ SELECT mleast(VARIADIC ARRAY[10, -1, 5, 4.4]);
normally. <literal>VARIADIC</> can only be attached to the last
actual argument of a function call.
</para>
+
+ <para>
+ The array element parameters generated from a variadic parameter are
+ treated as not having any names of their own. This means it is not
+ possible to call a variadic function using named arguments (<xref
+ linkend="sql-syntax-calling-funcs">), except when you specify
+ <literal>VARIADIC</>. For example, this will work:
+
+<screen>
+SELECT mleast(VARIADIC ARRAY[10, -1, 5, 4.4] AS arr);
+</screen>
+
+ but not these:
+
+<screen>
+SELECT mleast(10 AS arr);
+SELECT mleast(ARRAY[10, -1, 5, 4.4] AS arr);
+</screen>
+ </para>
</sect2>
<sect2 id="xfunc-sql-parameter-defaults">
@@ -677,7 +732,9 @@ SELECT mleast(VARIADIC ARRAY[10, -1, 5, 4.4]);
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.
+ default values as well. (Although the use of named argument notation
+ could allow this restriction to be relaxed, it's still enforced so that
+ positional argument notation works sensibly.)
</para>
<para>
@@ -712,7 +769,7 @@ SELECT foo(); -- fails since there is no default for the first argument
ERROR: function foo() does not exist
</screen>
The <literal>=</literal> sign can also be used in place of the
- key word <literal>DEFAULT</literal>,
+ key word <literal>DEFAULT</literal>.
</para>
</sect2>
generated by cgit v1.2.3 (git 2.25.1) at 2025-08-10 22:28:20 +0000