From 455dffbb73f9875c39f2ab544420454168a8c68c Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter_e@gmx.net>
Date: Thu, 4 Dec 2008 17:51:28 +0000
Subject: Default values for function arguments

Pavel Stehule, with some tweaks by Peter Eisentraut
---
 doc/src/sgml/func.sgml                | 20 +++++++++++--
 doc/src/sgml/ref/create_function.sgml | 26 ++++++++++++++--
 doc/src/sgml/xfunc.sgml               | 56 ++++++++++++++++++++++++++++++++++-
 3 files changed, 96 insertions(+), 6 deletions(-)

(limited to 'doc/src/sgml')

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 67500340b0f..23241c24697 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -1,4 +1,4 @@
-<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.460 2008/11/14 00:51:46 tgl Exp $ -->
+<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.461 2008/12/04 17:51:26 petere Exp $ -->
 
  <chapter id="functions">
   <title>Functions and Operators</title>
@@ -11710,6 +11710,10 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
     <primary>pg_get_function_arguments</primary>
    </indexterm>
 
+   <indexterm>
+    <primary>pg_get_function_identity_arguments</primary>
+   </indexterm>
+
    <indexterm>
     <primary>pg_get_function_result</primary>
    </indexterm>
@@ -11799,7 +11803,12 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
       <row>
        <entry><literal><function>pg_get_function_arguments</function>(<parameter>func_oid</parameter>)</literal></entry>
        <entry><type>text</type></entry>
-       <entry>get argument list for function</entry>
+       <entry>get argument list of function's definition (with default values)</entry>
+      </row>
+      <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 (without argument names, default values)</entry>
       </row>
       <row>
        <entry><literal><function>pg_get_function_result</function>(<parameter>func_oid</parameter>)</literal></entry>
@@ -11920,7 +11929,12 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
    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.
   </para>
 
   <para>
diff --git a/doc/src/sgml/ref/create_function.sgml b/doc/src/sgml/ref/create_function.sgml
index a6af57a22bf..5de4967789d 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.81 2008/11/14 10:22:46 petere Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/create_function.sgml,v 1.82 2008/12/04 17:51:26 petere Exp $
 -->
 
 <refentry id="SQL-CREATEFUNCTION">
@@ -21,7 +21,7 @@ $PostgreSQL: pgsql/doc/src/sgml/ref/create_function.sgml,v 1.81 2008/11/14 10:22
  <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> [, ...] ] )
+    <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>
@@ -154,6 +154,20 @@ CREATE [ OR REPLACE ] FUNCTION
      </listitem>
     </varlistentry>
 
+    <varlistentry>
+     <term><replaceable class="parameter">defexpr</replaceable></term>
+
+     <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.
+      </para>
+     </listitem>
+    </varlistentry>
+
     <varlistentry>
      <term><replaceable class="parameter">rettype</replaceable></term>
 
@@ -667,6 +681,14 @@ COMMIT;
    either before or after <replaceable class="parameter">argname</replaceable>.
    But only the first way is standard-compliant.
   </para>
+
+  <para>
+   The SQL standard does not specify parameter defaults.  The syntax
+   with the <literal>DEFAULT</literal> key word is from Oracle, and it
+   is somewhat in the spirit of the standard: SQL/PSM uses it for
+   variable default values.  The syntax with <literal>=</literal> is
+   used in T-SQL and Firebird.
+  </para>
  </refsect1>
 
 
diff --git a/doc/src/sgml/xfunc.sgml b/doc/src/sgml/xfunc.sgml
index a5fb62e18b9..44c83e8dbc7 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.133 2008/10/31 19:37:56 tgl Exp $ -->
+<!-- $PostgreSQL: pgsql/doc/src/sgml/xfunc.sgml,v 1.134 2008/12/04 17:51:26 petere Exp $ -->
 
  <sect1 id="xfunc">
   <title>User-Defined Functions</title>
@@ -663,6 +663,60 @@ SELECT mleast(VARIADIC ARRAY[10, -1, 5, 4.4]);
     </para>
    </sect2>
 
+   <sect2 id="xfunc-parameter-defaults">
+    <title><acronym>SQL</> Functions with Parameters Default Values</title>
+
+    <indexterm>
+     <primary>default values</primary>
+    </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.
+    </para>
+
+    <para>
+     For example:
+<screen>
+CREATE FUNCTION foo(a int DEFAULT 1, b int DEFAULT 2, c int DEFAULT 3) 
+RETURNS int
+LANGUAGE SQL
+AS $$
+    SELECT $1 + $2 + $3;
+$$;
+
+SELECT foo(10, 20, 30);
+ foo 
+-----
+  60
+(1 row)
+
+SELECT foo(10, 20);
+ foo 
+-----
+  33
+(1 row)
+
+SELECT foo(10);
+ foo 
+-----
+  15
+(1 row)
+
+SELECT foo();
+ foo 
+-----
+   6
+(1 row)
+</screen>
+     Instead of the key word <literal>DEFAULT</literal>,
+     the <literal>=</literal> sign can also be used.
+    </para>
+   </sect2>
+
    <sect2 id="xfunc-sql-table-functions">
     <title><acronym>SQL</acronym> Functions as Table Sources</title>
 
-- 
cgit v1.2.3