Skip to content

Commit 455dffb

Browse files
peterepetere
committed
Default values for function arguments
Pavel Stehule, with some tweaks by Peter Eisentraut
1 parent 7b640b0 commit 455dffb

29 files changed

+2848
-2166
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 17 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.460 2008/11/14 00:51:46 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.461 2008/12/04 17:51:26 petere Exp $ -->
22

33
<chapter id="functions">
44
<title>Functions and Operators</title>
@@ -11710,6 +11710,10 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
1171011710
<primary>pg_get_function_arguments</primary>
1171111711
</indexterm>
1171211712

11713+
<indexterm>
11714+
<primary>pg_get_function_identity_arguments</primary>
11715+
</indexterm>
11716+
1171311717
<indexterm>
1171411718
<primary>pg_get_function_result</primary>
1171511719
</indexterm>
@@ -11799,7 +11803,12 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
1179911803
<row>
1180011804
<entry><literal><function>pg_get_function_arguments</function>(<parameter>func_oid</parameter>)</literal></entry>
1180111805
<entry><type>text</type></entry>
11802-
<entry>get argument list for function</entry>
11806+
<entry>get argument list of function's definition (with default values)</entry>
11807+
</row>
11808+
<row>
11809+
<entry><literal><function>pg_get_function_identity_arguments</function>(<parameter>func_oid</parameter>)</literal></entry>
11810+
<entry><type>text</type></entry>
11811+
<entry>get argument list to identify a function (without argument names, default values)</entry>
1180311812
</row>
1180411813
<row>
1180511814
<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);
1192011929
of a function, in the form it would need to appear in within
1192111930
<command>CREATE FUNCTION</>.
1192211931
<function>pg_get_function_result</function> similarly returns the
11923-
appropriate <literal>RETURNS</> clause for the function.
11932+
appropriate <literal>RETURNS</> clause for the function.
11933+
<function>pg_get_function_identity_arguments</function> returns the
11934+
argument list necessary to identify a function, in the form it
11935+
would need to appear in within <command>ALTER FUNCTION</>, for
11936+
instance. This form omits default values and argument names, for
11937+
example.
1192411938
</para>
1192511939

1192611940
<para>

‎doc/src/sgml/ref/create_function.sgml

Lines changed: 24 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/create_function.sgml,v 1.81 2008/11/14 10:22:46 petere Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/create_function.sgml,v 1.82 2008/12/04 17:51:26 petere Exp $
33
-->
44

55
<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
2121
<refsynopsisdiv>
2222
<synopsis>
2323
CREATE [ OR REPLACE ] FUNCTION
24-
<replaceable class="parameter">name</replaceable> ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">argtype</replaceable> [, ...] ] )
24+
<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>] [, ...] ] )
2525
[ RETURNS <replaceable class="parameter">rettype</replaceable>
2626
| RETURNS TABLE ( <replaceable class="parameter">colname</replaceable> <replaceable class="parameter">coltype</replaceable> [, ...] ) ]
2727
{ LANGUAGE <replaceable class="parameter">langname</replaceable>
@@ -154,6 +154,20 @@ CREATE [ OR REPLACE ] FUNCTION
154154
</listitem>
155155
</varlistentry>
156156

157+
<varlistentry>
158+
<term><replaceable class="parameter">defexpr</replaceable></term>
159+
160+
<listitem>
161+
<para>
162+
An expression to be used as default value if the parameter is
163+
not specified. The expression has to be convertable to the
164+
argument type of the parameter. All parameters after a
165+
parameter with default value have to be parameters with default
166+
values as well.
167+
</para>
168+
</listitem>
169+
</varlistentry>
170+
157171
<varlistentry>
158172
<term><replaceable class="parameter">rettype</replaceable></term>
159173

@@ -667,6 +681,14 @@ COMMIT;
667681
either before or after <replaceable class="parameter">argname</replaceable>.
668682
But only the first way is standard-compliant.
669683
</para>
684+
685+
<para>
686+
The SQL standard does not specify parameter defaults. The syntax
687+
with the <literal>DEFAULT</literal> key word is from Oracle, and it
688+
is somewhat in the spirit of the standard: SQL/PSM uses it for
689+
variable default values. The syntax with <literal>=</literal> is
690+
used in T-SQL and Firebird.
691+
</para>
670692
</refsect1>
671693

672694

‎doc/src/sgml/xfunc.sgml

Lines changed: 55 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/xfunc.sgml,v 1.133 2008/10/31 19:37:56 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/xfunc.sgml,v 1.134 2008/12/04 17:51:26 petere Exp $ -->
22

33
<sect1 id="xfunc">
44
<title>User-Defined Functions</title>
@@ -663,6 +663,60 @@ SELECT mleast(VARIADIC ARRAY[10, -1, 5, 4.4]);
663663
</para>
664664
</sect2>
665665

666+
<sect2 id="xfunc-parameter-defaults">
667+
<title><acronym>SQL</> Functions with Parameters Default Values</title>
668+
669+
<indexterm>
670+
<primary>default values</primary>
671+
</indexterm>
672+
673+
<para>
674+
Functions can be declared with parameters with default values or
675+
expressions. The default expressions are used as parameter value
676+
if the parameter is not explicitly specified in a function call.
677+
All parameters after a a parameter with default value have to be
678+
parameters with default values as well.
679+
</para>
680+
681+
<para>
682+
For example:
683+
<screen>
684+
CREATE FUNCTION foo(a int DEFAULT 1, b int DEFAULT 2, c int DEFAULT 3)
685+
RETURNS int
686+
LANGUAGE SQL
687+
AS $$
688+
SELECT $1 + $2 + $3;
689+
$$;
690+
691+
SELECT foo(10, 20, 30);
692+
foo
693+
-----
694+
60
695+
(1 row)
696+
697+
SELECT foo(10, 20);
698+
foo
699+
-----
700+
33
701+
(1 row)
702+
703+
SELECT foo(10);
704+
foo
705+
-----
706+
15
707+
(1 row)
708+
709+
SELECT foo();
710+
foo
711+
-----
712+
6
713+
(1 row)
714+
</screen>
715+
Instead of the key word <literal>DEFAULT</literal>,
716+
the <literal>=</literal> sign can also be used.
717+
</para>
718+
</sect2>
719+
666720
<sect2 id="xfunc-sql-table-functions">
667721
<title><acronym>SQL</acronym> Functions as Table Sources</title>
668722

‎src/backend/catalog/namespace.c

Lines changed: 80 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -13,7 +13,7 @@
1313
* Portions Copyright (c) 1994, Regents of the University of California
1414
*
1515
* IDENTIFICATION
16-
* $PostgreSQL: pgsql/src/backend/catalog/namespace.c,v 1.112 2008/09/09 18:58:08 tgl Exp $
16+
* $PostgreSQL: pgsql/src/backend/catalog/namespace.c,v 1.113 2008/12/04 17:51:26 petere Exp $
1717
*
1818
*-------------------------------------------------------------------------
1919
*/
@@ -571,6 +571,11 @@ TypeIsVisible(Oid typid)
571571
* If expand_variadic is false, variadic arguments are not treated specially,
572572
* and the returned nvargs will always be zero.
573573
*
574+
* If expand_variadic is true, functions with argument default values
575+
* will also be retrieved. If expand_variadic is false, default
576+
* values will not be taken into account and functions that do not
577+
* have exactly nargs arguments in total will not be considered.
578+
*
574579
* We search a single namespace if the function name is qualified, else
575580
* all namespaces in the search path. The return list will never contain
576581
* multiple entries with identical argument lists --- in the multiple-
@@ -621,13 +626,45 @@ FuncnameGetCandidates(List *names, int nargs, bool expand_variadic)
621626
int pathpos = 0;
622627
bool variadic;
623628
Oid va_elem_type;
629+
List *defaults = NIL;
624630
FuncCandidateList newResult;
625631

632+
/*
633+
* Check if function has some parameter defaults if some
634+
* parameters are missing.
635+
*/
636+
if (pronargs > nargs && expand_variadic)
637+
{
638+
bool isnull;
639+
Datum proargdefaults;
640+
char *str;
641+
642+
/* skip when not enough default expressions */
643+
if (nargs + procform->pronargdefaults < pronargs)
644+
continue;
645+
646+
proargdefaults = SysCacheGetAttr(PROCOID, proctup,
647+
Anum_pg_proc_proargdefaults, &isnull);
648+
Assert(!isnull);
649+
str = TextDatumGetCString(proargdefaults);
650+
defaults = (List *) stringToNode(str);
651+
652+
Assert(IsA(defaults, List));
653+
654+
/*
655+
* If we don't have to use all default parameters, we skip
656+
* some cells from the left.
657+
*/
658+
defaults = list_copy_tail(defaults, procform->pronargdefaults - pronargs + nargs);
659+
660+
pfree(str);
661+
}
662+
626663
/*
627664
* Check if function is variadic, and get variadic element type if so.
628665
* If expand_variadic is false, we should just ignore variadic-ness.
629666
*/
630-
if (expand_variadic)
667+
if (pronargs <= nargs && expand_variadic)
631668
{
632669
va_elem_type = procform->provariadic;
633670
variadic = OidIsValid(va_elem_type);
@@ -638,11 +675,16 @@ FuncnameGetCandidates(List *names, int nargs, bool expand_variadic)
638675
variadic = false;
639676
}
640677

678+
Assert(!variadic || !defaults);
679+
641680
/* Ignore if it doesn't match requested argument count */
642681
if (nargs >= 0 &&
643-
(variadic ? (pronargs > nargs) : (pronargs != nargs)))
682+
(variadic ? (pronargs > nargs) : (defaults ? (pronargs < nargs) : (pronargs != nargs))))
644683
continue;
645684

685+
Assert(!variadic || (pronargs <= nargs));
686+
Assert(!defaults || (pronargs > nargs));
687+
646688
if (OidIsValid(namespaceId))
647689
{
648690
/* Consider only procs in specified namespace */
@@ -681,6 +723,7 @@ FuncnameGetCandidates(List *names, int nargs, bool expand_variadic)
681723
newResult->pathpos = pathpos;
682724
newResult->oid = HeapTupleGetOid(proctup);
683725
newResult->nargs = effective_nargs;
726+
newResult->argdefaults = defaults;
684727
memcpy(newResult->args, procform->proargtypes.values,
685728
pronargs * sizeof(Oid));
686729
if (variadic)
@@ -695,6 +738,8 @@ FuncnameGetCandidates(List *names, int nargs, bool expand_variadic)
695738
else
696739
newResult->nvargs = 0;
697740

741+
any_variadic = variadic || defaults;
742+
698743
/*
699744
* Does it have the same arguments as something we already accepted?
700745
* If so, decide which one to keep. We can skip this check for the
@@ -704,6 +749,9 @@ FuncnameGetCandidates(List *names, int nargs, bool expand_variadic)
704749
*/
705750
if (any_variadic || !OidIsValid(namespaceId))
706751
{
752+
if (defaults)
753+
effective_nargs = nargs;
754+
707755
/*
708756
* If we have an ordered list from SearchSysCacheList (the normal
709757
* case), then any conflicting proc must immediately adjoin this
@@ -733,11 +781,21 @@ FuncnameGetCandidates(List *names, int nargs, bool expand_variadic)
733781
prevResult;
734782
prevResult = prevResult->next)
735783
{
736-
if (effective_nargs == prevResult->nargs &&
737-
memcmp(newResult->args,
738-
prevResult->args,
739-
effective_nargs * sizeof(Oid)) == 0)
784+
if (!defaults)
785+
{
786+
if (effective_nargs == prevResult->nargs &&
787+
memcmp(newResult->args,
788+
prevResult->args,
789+
effective_nargs * sizeof(Oid)) == 0)
790+
break;
791+
}
792+
else
793+
{
794+
if (memcmp(newResult->args,
795+
prevResult->args,
796+
effective_nargs * sizeof(Oid)) == 0)
740797
break;
798+
}
741799
}
742800
}
743801
if (prevResult)
@@ -777,13 +835,28 @@ FuncnameGetCandidates(List *names, int nargs, bool expand_variadic)
777835
pfree(newResult);
778836
continue; /* keep previous result */
779837
}
838+
839+
if (defaults)
840+
{
841+
if (prevResult->argdefaults != NIL)
842+
ereport(ERROR,
843+
(errcode(ERRCODE_AMBIGUOUS_FUNCTION),
844+
errmsg("functions with parameter defaults %s and %s are ambiguous",
845+
func_signature_string(names, pronargs, procform->proargtypes.values),
846+
func_signature_string(names, prevResult->nargs, prevResult->args))));
847+
/* else, previous result didn't have defaults */
848+
pfree(newResult);
849+
continue; /* keep previous result */
850+
}
851+
780852
/* non-variadic can replace a previous variadic */
781853
Assert(prevResult->nvargs > 0);
782854
}
783855
/* replace previous result */
784856
prevResult->pathpos = pathpos;
785857
prevResult->oid = newResult->oid;
786858
prevResult->nvargs = newResult->nvargs;
859+
prevResult->argdefaults = newResult->argdefaults;
787860
pfree(newResult);
788861
continue; /* args are same, of course */
789862
}

‎src/backend/catalog/pg_aggregate.c

Lines changed: 4 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/catalog/pg_aggregate.c,v 1.97 2008/11/14 19:47:50 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/catalog/pg_aggregate.c,v 1.98 2008/12/04 17:51:26 petere Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -229,7 +229,8 @@ AggregateCreate(const char *aggName,
229229
PointerGetDatum(NULL), /* parameterNames */
230230
PointerGetDatum(NULL), /* proconfig */
231231
1, /* procost */
232-
0); /* prorows */
232+
0, /* prorows */
233+
NULL); /* parameterDefaults */
233234

234235
/*
235236
* Okay to create the pg_aggregate entry.
@@ -321,7 +322,7 @@ lookup_agg_function(List *fnName,
321322
*/
322323
fdresult = func_get_detail(fnName, NIL, nargs, input_types, false,
323324
&fnOid, rettype, &retset, &nvargs,
324-
&true_oid_array);
325+
&true_oid_array, NULL);
325326

326327
/* only valid case is a normal function not returning a set */
327328
if (fdresult != FUNCDETAIL_NORMAL || !OidIsValid(fnOid))

0 commit comments

Comments
 (0)