Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commite00ee88

Browse files
committed
Allow plpgsql functions to omit RETURN command when the function returns
output parameters or VOID or a set. There seems no particular reason toinsist on a RETURN in these cases, since the function return value isdetermined by other elements anyway. Per recent discussion.
1 parent5c7c017 commite00ee88

File tree

6 files changed

+144
-88
lines changed

6 files changed

+144
-88
lines changed

‎doc/src/sgml/plpgsql.sgml

Lines changed: 48 additions & 68 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.64 2005/04/05 18:05:45 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.65 2005/04/07 14:53:04 tgl Exp $
33
-->
44

55
<chapter id="plpgsql">
@@ -123,14 +123,14 @@ $$ LANGUAGE plpgsql;
123123

124124
<para>
125125
Because <application>PL/pgSQL</application> saves execution plans
126-
in this way, SQL commands that appear directly in a
127-
<application>PL/pgSQL</application> function must refer to the
128-
same tables and columns on every execution; that is, you cannot use
129-
a parameter as the name of a table or column in an SQL command. To get
130-
around this restriction, you can construct dynamic commands using
131-
the <application>PL/pgSQL</application> <command>EXECUTE</command>
132-
statement &mdash; at the price of constructing a new execution plan on
133-
every execution.
126+
in this way, SQL commands that appear directly in a
127+
<application>PL/pgSQL</application> function must refer to the
128+
same tables and columns on every execution; that is, you cannot use
129+
a parameter as the name of a table or column in an SQL command. To get
130+
around this restriction, you can construct dynamic commands using
131+
the <application>PL/pgSQL</application> <command>EXECUTE</command>
132+
statement &mdash; at the price of constructing a new execution plan on
133+
every execution.
134134
</para>
135135

136136
<note>
@@ -673,7 +673,6 @@ $$ LANGUAGE plpgsql;
673673
CREATE FUNCTION sales_tax(subtotal real, OUT tax real) AS $$
674674
BEGIN
675675
tax := subtotal * 0.06;
676-
RETURN;
677676
END;
678677
$$ LANGUAGE plpgsql;
679678
</programlisting>
@@ -691,7 +690,6 @@ CREATE FUNCTION sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$
691690
BEGIN
692691
sum := x + y;
693692
prod := x * y;
694-
RETURN;
695693
END;
696694
$$ LANGUAGE plpgsql;
697695
</programlisting>
@@ -742,7 +740,6 @@ CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement,
742740
AS $$
743741
BEGIN
744742
sum := v1 + v2 + v3;
745-
RETURN;
746743
END;
747744
$$ LANGUAGE plpgsql;
748745
</programlisting>
@@ -1498,17 +1495,20 @@ RETURN <replaceable>expression</replaceable>;
14981495
</para>
14991496

15001497
<para>
1501-
The return value of afunctioncannot be left undefined. If
1502-
control reaches the end of the top-level block of the function
1503-
without hitting a <command>RETURN</command> statement, a run-time
1504-
error will occur.
1498+
If you declared thefunctionto return <type>void</type>, a
1499+
<command>RETURN</command> statement can be used to exit the function
1500+
early; but do not write an expression following
1501+
<command>RETURN</command>.
15051502
</para>
15061503

15071504
<para>
1508-
If you have declared the function to
1509-
return <type>void</type>, a <command>RETURN</command> statement
1510-
must still be provided; but in this case the expression following
1511-
<command>RETURN</command> is optional and will be ignored if present.
1505+
The return value of a function cannot be left undefined. If
1506+
control reaches the end of the top-level block of the function
1507+
without hitting a <command>RETURN</command> statement, a run-time
1508+
error will occur. This restriction does not apply to functions
1509+
with output parameters and functions returning <type>void</type>,
1510+
however. In those cases a <command>RETURN</command> statement is
1511+
automatically executed if the top-level block finishes.
15121512
</para>
15131513
</sect3>
15141514

@@ -1538,7 +1538,8 @@ RETURN NEXT <replaceable>expression</replaceable>;
15381538
the <application>PL/pgSQL</> function. As successive
15391539
<command>RETURN NEXT</command> commands are executed, the result
15401540
set is built up. A final <command>RETURN</command>, which should
1541-
have no argument, causes control to exit the function.
1541+
have no argument, causes control to exit the function (or you can
1542+
just let control reach the end of the function).
15421543
</para>
15431544

15441545
<para>
@@ -2424,7 +2425,6 @@ BEGIN
24242425
RETURN NEXT $1;
24252426
OPEN $2 FOR SELECT * FROM table_2;
24262427
RETURN NEXT $2;
2427-
RETURN;
24282428
END;
24292429
$$ LANGUAGE plpgsql;
24302430

@@ -2990,7 +2990,8 @@ AFTER INSERT OR UPDATE OR DELETE ON sales_fact
29902990

29912991
<listitem>
29922992
<para>
2993-
In <productname>PostgreSQL</> you need to use dollar quoting or escape
2993+
In <productname>PostgreSQL</> the function body has to be written as
2994+
a string literal. Therefore you need to use dollar quoting or escape
29942995
single quotes in the function body. See <xref
29952996
linkend="plpgsql-quote-tips">.
29962997
</para>
@@ -3027,8 +3028,8 @@ AFTER INSERT OR UPDATE OR DELETE ON sales_fact
30273028
<para>
30283029
Here is an <productname>Oracle</productname> <application>PL/SQL</> function:
30293030
<programlisting>
3030-
CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_nameINvarchar,
3031-
v_versionINvarchar)
3031+
CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar,
3032+
v_version varchar)
30323033
RETURN varchar IS
30333034
BEGIN
30343035
IF v_version IS NULL THEN
@@ -3042,21 +3043,10 @@ show errors;
30423043
</para>
30433044

30443045
<para>
3045-
Let's go through this function and see the differences to <application>PL/pgSQL</>:
3046+
Let's go through this function and see the differences compared to
3047+
<application>PL/pgSQL</>:
30463048

30473049
<itemizedlist>
3048-
<listitem>
3049-
<para>
3050-
<productname>Oracle</productname> can have
3051-
<literal>IN</literal>, <literal>OUT</literal>, and
3052-
<literal>INOUT</literal> parameters passed to functions.
3053-
<literal>INOUT</literal>, for example, means that the
3054-
parameter will receive a value and return
3055-
another. <productname>PostgreSQL</> only has <literal>IN</literal>
3056-
parameters, and hence there is no specification of the parameter kind.
3057-
</para>
3058-
</listitem>
3059-
30603050
<listitem>
30613051
<para>
30623052
The <literal>RETURN</literal> key word in the function
@@ -3187,7 +3177,6 @@ BEGIN
31873177
|| ' LANGUAGE plpgsql;' ;
31883178

31893179
EXECUTE func_cmd;
3190-
RETURN;
31913180
END;
31923181
$func$ LANGUAGE plpgsql;
31933182
</programlisting>
@@ -3209,8 +3198,8 @@ $func$ LANGUAGE plpgsql;
32093198
<para>
32103199
<xref linkend="plpgsql-porting-ex3"> shows how to port a function
32113200
with <literal>OUT</> parameters and string manipulation.
3212-
<productname>PostgreSQL</> does not havean
3213-
<function>instr</function> function, but you canwork around it
3201+
<productname>PostgreSQL</> does not havea built-in
3202+
<function>instr</function> function, but you cancreate one
32143203
using a combination of other
32153204
functions.<indexterm><primary>instr</></indexterm> In <xref
32163205
linkend="plpgsql-porting-appendix"> there is a
@@ -3227,9 +3216,6 @@ $func$ LANGUAGE plpgsql;
32273216
<para>
32283217
The following <productname>Oracle</productname> PL/SQL procedure is used
32293218
to parse a URL and return several elements (host, path, and query).
3230-
In <productname>PostgreSQL</>, functions can return only one value.
3231-
One way to work around this is to make the return value a composite
3232-
type (row type).
32333219
</para>
32343220

32353221
<para>
@@ -3278,45 +3264,41 @@ show errors;
32783264
<para>
32793265
Here is a possible translation into <application>PL/pgSQL</>:
32803266
<programlisting>
3281-
CREATE TYPE cs_parse_url_result AS (
3282-
v_host VARCHAR,
3283-
v_path VARCHAR,
3284-
v_query VARCHAR
3285-
);
3286-
3287-
CREATE OR REPLACE FUNCTION cs_parse_url(v_url VARCHAR)
3288-
RETURNS cs_parse_url_result AS $$
3267+
CREATE OR REPLACE FUNCTION cs_parse_url(
3268+
v_url IN VARCHAR,
3269+
v_host OUT VARCHAR, -- This will be passed back
3270+
v_path OUT VARCHAR, -- This one too
3271+
v_query OUT VARCHAR) -- And this one
3272+
AS $$
32893273
DECLARE
3290-
res cs_parse_url_result;
32913274
a_pos1 INTEGER;
32923275
a_pos2 INTEGER;
32933276
BEGIN
3294-
res.v_host := NULL;
3295-
res.v_path := NULL;
3296-
res.v_query := NULL;
3277+
v_host := NULL;
3278+
v_path := NULL;
3279+
v_query := NULL;
32973280
a_pos1 := instr(v_url, '//');
32983281

32993282
IF a_pos1 = 0 THEN
3300-
RETURN res;
3283+
RETURN;
33013284
END IF;
33023285
a_pos2 := instr(v_url, '/', a_pos1 + 2);
33033286
IF a_pos2 = 0 THEN
3304-
res.v_host := substr(v_url, a_pos1 + 2);
3305-
res.v_path := '/';
3306-
RETURN res;
3287+
v_host := substr(v_url, a_pos1 + 2);
3288+
v_path := '/';
3289+
RETURN;
33073290
END IF;
33083291

3309-
res.v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2);
3292+
v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2);
33103293
a_pos1 := instr(v_url, '?', a_pos2 + 1);
33113294

33123295
IF a_pos1 = 0 THEN
3313-
res.v_path := substr(v_url, a_pos2);
3314-
RETURN res;
3296+
v_path := substr(v_url, a_pos2);
3297+
RETURN;
33153298
END IF;
33163299

3317-
res.v_path := substr(v_url, a_pos2, a_pos1 - a_pos2);
3318-
res.v_query := substr(v_url, a_pos1 + 1);
3319-
RETURN res;
3300+
v_path := substr(v_url, a_pos2, a_pos1 - a_pos2);
3301+
v_query := substr(v_url, a_pos1 + 1);
33203302
END;
33213303
$$ LANGUAGE plpgsql;
33223304
</programlisting>
@@ -3427,8 +3409,6 @@ BEGIN
34273409
WHEN unique_violation THEN <co id="co.plpgsql-porting-exception">
34283410
-- don't worry if it already exists
34293411
END;
3430-
3431-
RETURN;
34323412
END;
34333413
$$ LANGUAGE plpgsql;
34343414
</programlisting>

‎src/pl/plpgsql/src/gram.y

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -4,7 +4,7 @@
44
* procedural language
55
*
66
* IDENTIFICATION
7-
* $PostgreSQL: pgsql/src/pl/plpgsql/src/gram.y,v 1.68 2005/04/05 18:05:46 tgl Exp $
7+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/gram.y,v 1.69 2005/04/07 14:53:04 tgl Exp $
88
*
99
* This software is copyrighted by Jan Wieck - Hamburg.
1010
*
@@ -1071,7 +1071,7 @@ stmt_return: K_RETURN lno
10711071
elseif (plpgsql_curr_compile->fn_rettype == VOIDOID)
10721072
{
10731073
if (yylex() !=';')
1074-
yyerror("function returning void cannot specify RETURN expression");
1074+
yyerror("RETURN cannot have a parameter in function returning void");
10751075
}
10761076
elseif (plpgsql_curr_compile->fn_retistuple)
10771077
{

‎src/pl/plpgsql/src/pl_comp.c

Lines changed: 40 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@
33
* procedural language
44
*
55
* IDENTIFICATION
6-
* $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_comp.c,v 1.86 2005/04/05 06:22:16 tgl Exp $
6+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_comp.c,v 1.87 2005/04/07 14:53:04 tgl Exp $
77
*
88
* This software is copyrighted by Jan Wieck - Hamburg.
99
*
@@ -271,8 +271,8 @@ do_compile(FunctionCallInfo fcinfo,
271271
intparse_rc;
272272
Oidrettypeid;
273273
intnumargs;
274-
intnum_in_args;
275-
intnum_out_args;
274+
intnum_in_args=0;
275+
intnum_out_args=0;
276276
Oid*argtypes;
277277
char**argnames;
278278
char*argmodes;
@@ -374,7 +374,6 @@ do_compile(FunctionCallInfo fcinfo,
374374
/*
375375
* Create the variables for the procedure's parameters.
376376
*/
377-
num_in_args=num_out_args=0;
378377
for (i=0;i<numargs;i++)
379378
{
380379
charbuf[32];
@@ -641,12 +640,48 @@ do_compile(FunctionCallInfo fcinfo,
641640
parse_rc=plpgsql_yyparse();
642641
if (parse_rc!=0)
643642
elog(ERROR,"plpgsql parser returned %d",parse_rc);
643+
function->action=plpgsql_yylval.program;
644644

645645
plpgsql_scanner_finish();
646646
pfree(proc_source);
647647

648648
/*
649-
* If that was successful, complete the function's info.
649+
* If it has OUT parameters or returns VOID or returns a set, we allow
650+
* control to fall off the end without an explicit RETURN statement.
651+
* The easiest way to implement this is to add a RETURN statement to the
652+
* end of the statement list during parsing. However, if the outer block
653+
* has an EXCEPTION clause, we need to make a new outer block, since the
654+
* added RETURN shouldn't act like it is inside the EXCEPTION clause.
655+
*/
656+
if (num_out_args>0||function->fn_rettype==VOIDOID||
657+
function->fn_retset)
658+
{
659+
if (function->action->exceptions!=NIL)
660+
{
661+
PLpgSQL_stmt_block*new;
662+
663+
new=palloc0(sizeof(PLpgSQL_stmt_block));
664+
new->cmd_type=PLPGSQL_STMT_BLOCK;
665+
new->body=list_make1(function->action);
666+
667+
function->action=new;
668+
}
669+
if (function->action->body==NIL||
670+
((PLpgSQL_stmt*)llast(function->action->body))->cmd_type!=PLPGSQL_STMT_RETURN)
671+
{
672+
PLpgSQL_stmt_return*new;
673+
674+
new=palloc0(sizeof(PLpgSQL_stmt_return));
675+
new->cmd_type=PLPGSQL_STMT_RETURN;
676+
new->expr=NULL;
677+
new->retvarno=function->out_param_varno;
678+
679+
function->action->body=lappend(function->action->body,new);
680+
}
681+
}
682+
683+
/*
684+
* Complete the function's info
650685
*/
651686
function->fn_nargs=procStruct->pronargs;
652687
for (i=0;i<function->fn_nargs;i++)
@@ -655,7 +690,6 @@ do_compile(FunctionCallInfo fcinfo,
655690
function->datums=palloc(sizeof(PLpgSQL_datum*)*plpgsql_nDatums);
656691
for (i=0;i<plpgsql_nDatums;i++)
657692
function->datums[i]=plpgsql_Datums[i];
658-
function->action=plpgsql_yylval.program;
659693

660694
/* Debug dump for completed functions */
661695
if (plpgsql_DumpExecTree)

‎src/pl/plpgsql/src/pl_exec.c

Lines changed: 9 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@
33
* procedural language
44
*
55
* IDENTIFICATION
6-
* $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_exec.c,v 1.134 2005/04/05 06:22:16 tgl Exp $
6+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_exec.c,v 1.135 2005/04/07 14:53:04 tgl Exp $
77
*
88
* This software is copyrighted by Jan Wieck - Hamburg.
99
*
@@ -1691,12 +1691,18 @@ exec_stmt_return(PLpgSQL_execstate *estate, PLpgSQL_stmt_return *stmt)
16911691
&(estate->retisnull),
16921692
&(estate->rettype));
16931693
}
1694+
1695+
returnPLPGSQL_RC_RETURN;
16941696
}
16951697

1698+
/*
1699+
* Special hack for function returning VOID: instead of NULL, return a
1700+
* non-null VOID value. This is of dubious importance but is kept for
1701+
* backwards compatibility. Note that the only other way to get here
1702+
* is to have written "RETURN NULL" in a function returning tuple.
1703+
*/
16961704
if (estate->fn_rettype==VOIDOID)
16971705
{
1698-
/* Special hack for function returning VOID */
1699-
Assert(stmt->retvarno<0&&stmt->expr==NULL);
17001706
estate->retval= (Datum)0;
17011707
estate->retisnull= false;
17021708
estate->rettype=VOIDOID;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp