Skip to content

Commit fd97cf4

Browse files
tglsfdctglsfdc
committed
plpgsql does OUT parameters, as per my proposal a few weeks ago.
1 parent 2af664e commit fd97cf4

File tree

10 files changed

+958
-359
lines changed

10 files changed

+958
-359
lines changed

‎doc/src/sgml/plpgsql.sgml

+119-21
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.62 2005/03/13 09:36:30 neilc Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.63 2005/04/05 06:22:14 tgl Exp $
33
-->
44

55
<chapter id="plpgsql">
@@ -83,7 +83,7 @@ $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.62 2005/03/13 09:36:30 neilc Ex
8383
that contains many statements for which execution plans might be
8484
required will only prepare and save those plans that are really
8585
used during the lifetime of the database connection. This can
86-
substantially reduce the total amount of time required to parse,
86+
substantially reduce the total amount of time required to parse
8787
and generate execution plans for the statements in a
8888
<application>PL/pgSQL</> function. A disadvantage is that errors
8989
in a specific expression or command may not be detected until that
@@ -215,6 +215,7 @@ $$ LANGUAGE plpgsql;
215215
<type>void</> if it has no useful return value.
216216
</para>
217217

218+
<note>
218219
<para>
219220
<application>PL/pgSQL</> does not currently have full support for
220221
domain types: it treats a domain the same as the underlying scalar
@@ -223,6 +224,20 @@ $$ LANGUAGE plpgsql;
223224
it is a hazard if you declare a <application>PL/pgSQL</> function
224225
as returning a domain type.
225226
</para>
227+
</note>
228+
229+
<para>
230+
<application>PL/pgSQL</> functions can also be declared with output
231+
parameters in place of an explicit specification of the return type.
232+
This does not add any fundamental capability to the language, but
233+
it is often convenient, especially for returning multiple values.
234+
</para>
235+
236+
<para>
237+
Specific examples appear in
238+
<xref linkend="plpgsql-declaration-aliases"> and
239+
<xref linkend="plpgsql-statements-returning">.
240+
</para>
226241
</sect2>
227242
</sect1>
228243

@@ -631,19 +646,62 @@ DECLARE
631646
v_string ALIAS FOR $1;
632647
index ALIAS FOR $2;
633648
BEGIN
634-
-- some computations here
649+
-- some computations using v_string and index here
635650
END;
636651
$$ LANGUAGE plpgsql;
637652

638653

639-
CREATE FUNCTION concat_selected_fields(in_t tablename) RETURNS text AS $$
654+
CREATE FUNCTION concat_selected_fields(in_t sometablename) RETURNS text AS $$
640655
BEGIN
641656
RETURN in_t.f1 || in_t.f3 || in_t.f5 || in_t.f7;
642657
END;
643658
$$ LANGUAGE plpgsql;
644659
</programlisting>
645660
</para>
646661

662+
<para>
663+
When a <application>PL/pgSQL</application> function is declared
664+
with output parameters, the output parameters are given
665+
<literal>$<replaceable>n</replaceable></literal> names and optional
666+
aliases in just the same way as the normal input parameters. An
667+
output parameter is effectively a variable that starts out NULL;
668+
it should be assigned to during the execution of the function.
669+
The final value of the parameter is what is returned. For instance,
670+
the sales-tax example could also be done this way:
671+
672+
<programlisting>
673+
CREATE FUNCTION sales_tax(subtotal real, OUT tax real) AS $$
674+
BEGIN
675+
tax := subtotal * 0.06;
676+
RETURN;
677+
END;
678+
$$ LANGUAGE plpgsql;
679+
</programlisting>
680+
681+
Notice that we omitted <literal>RETURNS real</> &mdash; we could have
682+
included it, but it would be redundant.
683+
</para>
684+
685+
<para>
686+
Output parameters are most useful when returning multiple values.
687+
A trivial example is:
688+
689+
<programlisting>
690+
CREATE FUNCTION sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$
691+
BEGIN
692+
sum := x + y;
693+
prod := x * y;
694+
RETURN;
695+
END;
696+
$$ LANGUAGE plpgsql;
697+
</programlisting>
698+
699+
As discussed in <xref linkend="xfunc-output-parameters">, this
700+
effectively creates an anonymous record type for the function's
701+
results. If a <literal>RETURNS</> clause is given, it must say
702+
<literal>RETURNS record</>.
703+
</para>
704+
647705
<para>
648706
When the return type of a <application>PL/pgSQL</application>
649707
function is declared as a polymorphic type (<type>anyelement</type>
@@ -658,6 +716,7 @@ $$ LANGUAGE plpgsql;
658716
though that is not required. <literal>$0</literal> can also be
659717
given an alias. For example, this function works on any data type
660718
that has a <literal>+</> operator:
719+
661720
<programlisting>
662721
CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement)
663722
RETURNS anyelement AS $$
@@ -668,6 +727,24 @@ BEGIN
668727
RETURN result;
669728
END;
670729
$$ LANGUAGE plpgsql;
730+
</programlisting>
731+
</para>
732+
733+
<para>
734+
The same effect can be had by declaring one or more output parameters as
735+
<type>anyelement</type> or <type>anyarray</type>. In this case the
736+
special <literal>$0</literal> parameter is not used; the output
737+
parameters themselves serve the same purpose. For example:
738+
739+
<programlisting>
740+
CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement,
741+
OUT sum anyelement)
742+
AS $$
743+
BEGIN
744+
sum := v1 + v2 + v3;
745+
RETURN;
746+
END;
747+
$$ LANGUAGE plpgsql;
671748
</programlisting>
672749
</para>
673750
</sect2>
@@ -756,18 +833,21 @@ user_id users.user_id%TYPE;
756833
</para>
757834

758835
<para>
759-
Here is an example of using composite types:
836+
Here is an example of using composite types. <structname>table1</>
837+
and <structname>table2</> are existing tables having at least the
838+
mentioned fields:
839+
760840
<programlisting>
761-
CREATE FUNCTION merge_fields(t_row tablename) RETURNS text AS $$
841+
CREATE FUNCTION merge_fields(t_row table1) RETURNS text AS $$
762842
DECLARE
763-
t2_row table2name%ROWTYPE;
843+
t2_row table2%ROWTYPE;
764844
BEGIN
765-
SELECT * INTO t2_row FROM table2name WHERE ... ;
845+
SELECT * INTO t2_row FROM table2 WHERE ... ;
766846
RETURN t_row.f1 || t2_row.f3 || t_row.f5 || t2_row.f7;
767847
END;
768848
$$ LANGUAGE plpgsql;
769849

770-
SELECT merge_fields(t.*) FROM tablename t WHERE ... ;
850+
SELECT merge_fields(t.*) FROM table1 t WHERE ... ;
771851
</programlisting>
772852
</para>
773853
</sect2>
@@ -1411,6 +1491,12 @@ RETURN <replaceable>expression</replaceable>;
14111491
as the <replaceable>expression</replaceable>.
14121492
</para>
14131493

1494+
<para>
1495+
If you declared the function with output parameters, write just
1496+
<command>RETURN</command> with no expression. The current values
1497+
of the output parameter variables will be returned.
1498+
</para>
1499+
14141500
<para>
14151501
The return value of a function cannot be left undefined. If
14161502
control reaches the end of the top-level block of the function
@@ -1441,8 +1527,30 @@ RETURN NEXT <replaceable>expression</replaceable>;
14411527
commands, and then a final <command>RETURN</command> command
14421528
with no argument is used to indicate that the function has
14431529
finished executing. <command>RETURN NEXT</command> can be used
1444-
with both scalar and composite data types; in the latter case, an
1445-
entire <quote>table</quote> of results will be returned.
1530+
with both scalar and composite data types; with a composite result
1531+
type, an entire <quote>table</quote> of results will be returned.
1532+
</para>
1533+
1534+
<para>
1535+
<command>RETURN NEXT</command> does not actually return from the
1536+
function &mdash; it simply saves away the value of the expression.
1537+
Execution then continues with the next statement in
1538+
the <application>PL/pgSQL</> function. As successive
1539+
<command>RETURN NEXT</command> commands are executed, the result
1540+
set is built up. A final <command>RETURN</command>, which should
1541+
have no argument, causes control to exit the function.
1542+
</para>
1543+
1544+
<para>
1545+
If you declared the function with output parameters, write just
1546+
<command>RETURN NEXT</command> with no expression. The current values
1547+
of the output parameter variable(s) will be saved for eventual return.
1548+
Note that you must declare the function as returning
1549+
<literal>SETOF record</literal> when there are
1550+
multiple output parameters, or
1551+
<literal>SETOF <replaceable>sometype</></literal> when there is
1552+
just one output parameter of type <replaceable>sometype</>, in
1553+
order to create a set-returning function with output parameters.
14461554
</para>
14471555

14481556
<para>
@@ -1457,16 +1565,6 @@ SELECT * FROM some_func();
14571565
<literal>FROM</literal> clause.
14581566
</para>
14591567

1460-
<para>
1461-
<command>RETURN NEXT</command> does not actually return from the
1462-
function; it simply saves away the value of the expression.
1463-
Execution then continues with the next statement in
1464-
the <application>PL/pgSQL</> function. As successive
1465-
<command>RETURN NEXT</command> commands are executed, the result
1466-
set is built up. A final <command>RETURN</command>, which should
1467-
have no argument, causes control to exit the function.
1468-
</para>
1469-
14701568
<note>
14711569
<para>
14721570
The current implementation of <command>RETURN NEXT</command>

‎src/backend/utils/fmgr/funcapi.c

+103-1
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Copyright (c) 2002-2005, PostgreSQL Global Development Group
88
*
99
* IDENTIFICATION
10-
* $PostgreSQL: pgsql/src/backend/utils/fmgr/funcapi.c,v 1.19 2005/03/31 22:46:16 tgl Exp $
10+
* $PostgreSQL: pgsql/src/backend/utils/fmgr/funcapi.c,v 1.20 2005/04/05 06:22:14 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -483,6 +483,108 @@ resolve_polymorphic_tupdesc(TupleDesc tupdesc, oidvector *declared_args,
483483
return true;
484484
}
485485

486+
/*
487+
* Given the declared argument types and modes for a function,
488+
* replace any polymorphic types (ANYELEMENT/ANYARRAY) with correct data
489+
* types deduced from the input arguments. Returns TRUE if able to deduce
490+
* all types, FALSE if not. This is the same logic as
491+
* resolve_polymorphic_tupdesc, but with a different argument representation.
492+
*
493+
* argmodes may be NULL, in which case all arguments are assumed to be IN mode.
494+
*/
495+
bool
496+
resolve_polymorphic_argtypes(int numargs, Oid *argtypes, char *argmodes,
497+
Node *call_expr)
498+
{
499+
bool have_anyelement_result = false;
500+
bool have_anyarray_result = false;
501+
Oid anyelement_type = InvalidOid;
502+
Oid anyarray_type = InvalidOid;
503+
int inargno;
504+
int i;
505+
506+
/* First pass: resolve polymorphic inputs, check for outputs */
507+
inargno = 0;
508+
for (i = 0; i < numargs; i++)
509+
{
510+
char argmode = argmodes ? argmodes[i] : PROARGMODE_IN;
511+
512+
switch (argtypes[i])
513+
{
514+
case ANYELEMENTOID:
515+
if (argmode == PROARGMODE_OUT)
516+
have_anyelement_result = true;
517+
else
518+
{
519+
if (!OidIsValid(anyelement_type))
520+
{
521+
anyelement_type = get_call_expr_argtype(call_expr,
522+
inargno);
523+
if (!OidIsValid(anyelement_type))
524+
return false;
525+
}
526+
argtypes[i] = anyelement_type;
527+
}
528+
break;
529+
case ANYARRAYOID:
530+
if (argmode == PROARGMODE_OUT)
531+
have_anyarray_result = true;
532+
else
533+
{
534+
if (!OidIsValid(anyarray_type))
535+
{
536+
anyarray_type = get_call_expr_argtype(call_expr,
537+
inargno);
538+
if (!OidIsValid(anyarray_type))
539+
return false;
540+
}
541+
argtypes[i] = anyarray_type;
542+
}
543+
break;
544+
default:
545+
break;
546+
}
547+
if (argmode != PROARGMODE_OUT)
548+
inargno++;
549+
}
550+
551+
/* Done? */
552+
if (!have_anyelement_result && !have_anyarray_result)
553+
return true;
554+
555+
/* If no input polymorphics, parser messed up */
556+
if (!OidIsValid(anyelement_type) && !OidIsValid(anyarray_type))
557+
return false;
558+
559+
/* If needed, deduce one polymorphic type from the other */
560+
if (have_anyelement_result && !OidIsValid(anyelement_type))
561+
anyelement_type = resolve_generic_type(ANYELEMENTOID,
562+
anyarray_type,
563+
ANYARRAYOID);
564+
if (have_anyarray_result && !OidIsValid(anyarray_type))
565+
anyarray_type = resolve_generic_type(ANYARRAYOID,
566+
anyelement_type,
567+
ANYELEMENTOID);
568+
569+
/* And finally replace the output column types as needed */
570+
for (i = 0; i < numargs; i++)
571+
{
572+
switch (argtypes[i])
573+
{
574+
case ANYELEMENTOID:
575+
argtypes[i] = anyelement_type;
576+
break;
577+
case ANYARRAYOID:
578+
argtypes[i] = anyarray_type;
579+
break;
580+
default:
581+
break;
582+
}
583+
}
584+
585+
return true;
586+
}
587+
486588
/*
487589
* get_type_func_class
488590
* Given the type OID, obtain its TYPEFUNC classification.

‎src/include/funcapi.h

+5-1
Original file line numberDiff line numberDiff line change
@@ -9,7 +9,7 @@
99
*
1010
* Copyright (c) 2002-2005, PostgreSQL Global Development Group
1111
*
12-
* $PostgreSQL: pgsql/src/include/funcapi.h,v 1.16 2005/03/31 22:46:24 tgl Exp $
12+
* $PostgreSQL: pgsql/src/include/funcapi.h,v 1.17 2005/04/05 06:22:15 tgl Exp $
1313
*
1414
*-------------------------------------------------------------------------
1515
*/
@@ -167,6 +167,10 @@ extern TypeFuncClass get_func_result_type(Oid functionId,
167167
Oid *resultTypeId,
168168
TupleDesc *resultTupleDesc);
169169

170+
extern bool resolve_polymorphic_argtypes(int numargs, Oid *argtypes,
171+
char *argmodes,
172+
Node *call_expr);
173+
170174
extern TupleDesc build_function_result_tupdesc_d(Datum proallargtypes,
171175
Datum proargmodes,
172176
Datum proargnames);

0 commit comments

Comments
 (0)