From e00ee887612da0dab02f1a56e33d8ae821710e14 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Thu, 7 Apr 2005 14:53:04 +0000 Subject: [PATCH] Allow plpgsql functions to omit RETURN command when the function returns output parameters or VOID or a set. There seems no particular reason to insist on a RETURN in these cases, since the function return value is determined by other elements anyway. Per recent discussion. --- doc/src/sgml/plpgsql.sgml | 116 ++++++++++++++-------------------- src/pl/plpgsql/src/gram.y | 4 +- src/pl/plpgsql/src/pl_comp.c | 46 ++++++++++++-- src/pl/plpgsql/src/pl_exec.c | 12 +++- src/test/regress/expected/plpgsql.out | 29 +++++++-- src/test/regress/sql/plpgsql.sql | 25 ++++++-- 6 files changed, 144 insertions(+), 88 deletions(-) diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index 3ea1ac19b2..9cb0ad2a8b 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -1,5 +1,5 @@ @@ -123,14 +123,14 @@ $$ LANGUAGE plpgsql; Because PL/pgSQL saves execution plans - in this way, SQL commands that appear directly in a - PL/pgSQL function must refer to the - same tables and columns on every execution; that is, you cannot use - a parameter as the name of a table or column in an SQL command. To get - around this restriction, you can construct dynamic commands using - the PL/pgSQL EXECUTE - statement — at the price of constructing a new execution plan on - every execution. + in this way, SQL commands that appear directly in a + PL/pgSQL function must refer to the + same tables and columns on every execution; that is, you cannot use + a parameter as the name of a table or column in an SQL command. To get + around this restriction, you can construct dynamic commands using + the PL/pgSQL EXECUTE + statement — at the price of constructing a new execution plan on + every execution. @@ -673,7 +673,6 @@ $$ LANGUAGE plpgsql; CREATE FUNCTION sales_tax(subtotal real, OUT tax real) AS $$ BEGIN tax := subtotal * 0.06; - RETURN; END; $$ LANGUAGE plpgsql; @@ -691,7 +690,6 @@ CREATE FUNCTION sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$ BEGIN sum := x + y; prod := x * y; - RETURN; END; $$ LANGUAGE plpgsql; @@ -742,7 +740,6 @@ CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement, AS $$ BEGIN sum := v1 + v2 + v3; - RETURN; END; $$ LANGUAGE plpgsql; @@ -1498,17 +1495,20 @@ RETURN expression; - The return value of a function cannot be left undefined. If - control reaches the end of the top-level block of the function - without hitting a RETURN statement, a run-time - error will occur. + If you declared the function to return void, a + RETURN statement can be used to exit the function + early; but do not write an expression following + RETURN. - If you have declared the function to - return void, a RETURN statement - must still be provided; but in this case the expression following - RETURN is optional and will be ignored if present. + The return value of a function cannot be left undefined. If + control reaches the end of the top-level block of the function + without hitting a RETURN statement, a run-time + error will occur. This restriction does not apply to functions + with output parameters and functions returning void, + however. In those cases a RETURN statement is + automatically executed if the top-level block finishes. @@ -1538,7 +1538,8 @@ RETURN NEXT expression; the PL/pgSQL function. As successive RETURN NEXT commands are executed, the result set is built up. A final RETURN, which should - have no argument, causes control to exit the function. + have no argument, causes control to exit the function (or you can + just let control reach the end of the function). @@ -2424,7 +2425,6 @@ BEGIN RETURN NEXT $1; OPEN $2 FOR SELECT * FROM table_2; RETURN NEXT $2; - RETURN; END; $$ LANGUAGE plpgsql; @@ -2990,7 +2990,8 @@ AFTER INSERT OR UPDATE OR DELETE ON sales_fact - In PostgreSQL you need to use dollar quoting or escape + In PostgreSQL the function body has to be written as + a string literal. Therefore you need to use dollar quoting or escape single quotes in the function body. See . @@ -3027,8 +3028,8 @@ AFTER INSERT OR UPDATE OR DELETE ON sales_fact Here is an Oracle PL/SQL function: -CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name IN varchar, - v_version IN varchar) +CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar, + v_version varchar) RETURN varchar IS BEGIN IF v_version IS NULL THEN @@ -3042,23 +3043,12 @@ show errors; - Let's go through this function and see the differences to PL/pgSQL: + Let's go through this function and see the differences compared to + PL/pgSQL: - Oracle can have - IN, OUT, and - INOUT parameters passed to functions. - INOUT, for example, means that the - parameter will receive a value and return - another. PostgreSQL only has IN - parameters, and hence there is no specification of the parameter kind. - - - - - The RETURN key word in the function prototype (not the function body) becomes RETURNS in @@ -3187,7 +3177,6 @@ BEGIN || ' LANGUAGE plpgsql;' ; EXECUTE func_cmd; - RETURN; END; $func$ LANGUAGE plpgsql; @@ -3209,8 +3198,8 @@ $func$ LANGUAGE plpgsql; shows how to port a function with OUT parameters and string manipulation. - PostgreSQL does not have an - instr function, but you can work around it + PostgreSQL does not have a built-in + instr function, but you can create one using a combination of other functions.instr In there is a @@ -3227,9 +3216,6 @@ $func$ LANGUAGE plpgsql; The following Oracle PL/SQL procedure is used to parse a URL and return several elements (host, path, and query). - In PostgreSQL, functions can return only one value. - One way to work around this is to make the return value a composite - type (row type). @@ -3278,45 +3264,41 @@ show errors; Here is a possible translation into PL/pgSQL: -CREATE TYPE cs_parse_url_result AS ( - v_host VARCHAR, - v_path VARCHAR, - v_query VARCHAR -); - -CREATE OR REPLACE FUNCTION cs_parse_url(v_url VARCHAR) -RETURNS cs_parse_url_result AS $$ +CREATE OR REPLACE FUNCTION cs_parse_url( + v_url IN VARCHAR, + v_host OUT VARCHAR, -- This will be passed back + v_path OUT VARCHAR, -- This one too + v_query OUT VARCHAR) -- And this one +AS $$ DECLARE - res cs_parse_url_result; a_pos1 INTEGER; a_pos2 INTEGER; BEGIN - res.v_host := NULL; - res.v_path := NULL; - res.v_query := NULL; + v_host := NULL; + v_path := NULL; + v_query := NULL; a_pos1 := instr(v_url, '//'); IF a_pos1 = 0 THEN - RETURN res; + RETURN; END IF; a_pos2 := instr(v_url, '/', a_pos1 + 2); IF a_pos2 = 0 THEN - res.v_host := substr(v_url, a_pos1 + 2); - res.v_path := '/'; - RETURN res; + v_host := substr(v_url, a_pos1 + 2); + v_path := '/'; + RETURN; END IF; - res.v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2); + v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2); a_pos1 := instr(v_url, '?', a_pos2 + 1); IF a_pos1 = 0 THEN - res.v_path := substr(v_url, a_pos2); - RETURN res; + v_path := substr(v_url, a_pos2); + RETURN; END IF; - res.v_path := substr(v_url, a_pos2, a_pos1 - a_pos2); - res.v_query := substr(v_url, a_pos1 + 1); - RETURN res; + v_path := substr(v_url, a_pos2, a_pos1 - a_pos2); + v_query := substr(v_url, a_pos1 + 1); END; $$ LANGUAGE plpgsql; @@ -3427,8 +3409,6 @@ BEGIN WHEN unique_violation THEN -- don't worry if it already exists END; - - RETURN; END; $$ LANGUAGE plpgsql; diff --git a/src/pl/plpgsql/src/gram.y b/src/pl/plpgsql/src/gram.y index 65b8f5f409..8b5f6b5e28 100644 --- a/src/pl/plpgsql/src/gram.y +++ b/src/pl/plpgsql/src/gram.y @@ -4,7 +4,7 @@ * procedural language * * IDENTIFICATION - * $PostgreSQL: pgsql/src/pl/plpgsql/src/gram.y,v 1.68 2005/04/05 18:05:46 tgl Exp $ + * $PostgreSQL: pgsql/src/pl/plpgsql/src/gram.y,v 1.69 2005/04/07 14:53:04 tgl Exp $ * * This software is copyrighted by Jan Wieck - Hamburg. * @@ -1071,7 +1071,7 @@ stmt_return : K_RETURN lno else if (plpgsql_curr_compile->fn_rettype == VOIDOID) { if (yylex() != ';') - yyerror("function returning void cannot specify RETURN expression"); + yyerror("RETURN cannot have a parameter in function returning void"); } else if (plpgsql_curr_compile->fn_retistuple) { diff --git a/src/pl/plpgsql/src/pl_comp.c b/src/pl/plpgsql/src/pl_comp.c index 23109c949f..9fefffa319 100644 --- a/src/pl/plpgsql/src/pl_comp.c +++ b/src/pl/plpgsql/src/pl_comp.c @@ -3,7 +3,7 @@ * procedural language * * IDENTIFICATION - * $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_comp.c,v 1.86 2005/04/05 06:22:16 tgl Exp $ + * $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_comp.c,v 1.87 2005/04/07 14:53:04 tgl Exp $ * * This software is copyrighted by Jan Wieck - Hamburg. * @@ -271,8 +271,8 @@ do_compile(FunctionCallInfo fcinfo, int parse_rc; Oid rettypeid; int numargs; - int num_in_args; - int num_out_args; + int num_in_args = 0; + int num_out_args = 0; Oid *argtypes; char **argnames; char *argmodes; @@ -374,7 +374,6 @@ do_compile(FunctionCallInfo fcinfo, /* * Create the variables for the procedure's parameters. */ - num_in_args = num_out_args = 0; for (i = 0; i < numargs; i++) { char buf[32]; @@ -641,12 +640,48 @@ do_compile(FunctionCallInfo fcinfo, parse_rc = plpgsql_yyparse(); if (parse_rc != 0) elog(ERROR, "plpgsql parser returned %d", parse_rc); + function->action = plpgsql_yylval.program; plpgsql_scanner_finish(); pfree(proc_source); /* - * If that was successful, complete the function's info. + * If it has OUT parameters or returns VOID or returns a set, we allow + * control to fall off the end without an explicit RETURN statement. + * The easiest way to implement this is to add a RETURN statement to the + * end of the statement list during parsing. However, if the outer block + * has an EXCEPTION clause, we need to make a new outer block, since the + * added RETURN shouldn't act like it is inside the EXCEPTION clause. + */ + if (num_out_args > 0 || function->fn_rettype == VOIDOID || + function->fn_retset) + { + if (function->action->exceptions != NIL) + { + PLpgSQL_stmt_block *new; + + new = palloc0(sizeof(PLpgSQL_stmt_block)); + new->cmd_type = PLPGSQL_STMT_BLOCK; + new->body = list_make1(function->action); + + function->action = new; + } + if (function->action->body == NIL || + ((PLpgSQL_stmt *) llast(function->action->body))->cmd_type != PLPGSQL_STMT_RETURN) + { + PLpgSQL_stmt_return *new; + + new = palloc0(sizeof(PLpgSQL_stmt_return)); + new->cmd_type = PLPGSQL_STMT_RETURN; + new->expr = NULL; + new->retvarno = function->out_param_varno; + + function->action->body = lappend(function->action->body, new); + } + } + + /* + * Complete the function's info */ function->fn_nargs = procStruct->pronargs; for (i = 0; i < function->fn_nargs; i++) @@ -655,7 +690,6 @@ do_compile(FunctionCallInfo fcinfo, function->datums = palloc(sizeof(PLpgSQL_datum *) * plpgsql_nDatums); for (i = 0; i < plpgsql_nDatums; i++) function->datums[i] = plpgsql_Datums[i]; - function->action = plpgsql_yylval.program; /* Debug dump for completed functions */ if (plpgsql_DumpExecTree) diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c index 4454f2834a..fe2d42ecf3 100644 --- a/src/pl/plpgsql/src/pl_exec.c +++ b/src/pl/plpgsql/src/pl_exec.c @@ -3,7 +3,7 @@ * procedural language * * IDENTIFICATION - * $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_exec.c,v 1.134 2005/04/05 06:22:16 tgl Exp $ + * $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_exec.c,v 1.135 2005/04/07 14:53:04 tgl Exp $ * * This software is copyrighted by Jan Wieck - Hamburg. * @@ -1691,12 +1691,18 @@ exec_stmt_return(PLpgSQL_execstate *estate, PLpgSQL_stmt_return *stmt) &(estate->retisnull), &(estate->rettype)); } + + return PLPGSQL_RC_RETURN; } + /* + * Special hack for function returning VOID: instead of NULL, return a + * non-null VOID value. This is of dubious importance but is kept for + * backwards compatibility. Note that the only other way to get here + * is to have written "RETURN NULL" in a function returning tuple. + */ if (estate->fn_rettype == VOIDOID) { - /* Special hack for function returning VOID */ - Assert(stmt->retvarno < 0 && stmt->expr == NULL); estate->retval = (Datum) 0; estate->retisnull = false; estate->rettype = VOIDOID; diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out index ee1c52dfa4..08fbe46b3a 100644 --- a/src/test/regress/expected/plpgsql.out +++ b/src/test/regress/expected/plpgsql.out @@ -1739,7 +1739,8 @@ SELECT * FROM test_ret_rec_dyn(5) AS (a int, b numeric, c text); (1 row) -- --- Test handling of OUT parameters, including polymorphic cases +-- Test handling of OUT parameters, including polymorphic cases. +-- Note that RETURN is optional with OUT params; we try both ways. -- -- wrong way to do it: create function f1(in i int, out j int) returns int as $$ @@ -1769,7 +1770,6 @@ select * from f1(42); create or replace function f1(inout i int) as $$ begin i := i+1; - return; end$$ language plpgsql; select f1(42); f1 @@ -1805,7 +1805,6 @@ begin j := i; j := j+1; k := 'foo'; - return; end$$ language plpgsql; select f1(42); f1 @@ -1828,7 +1827,6 @@ begin j := j+1; k := 'foot'; return next; - return; end$$ language plpgsql; select * from f1(42); j | k @@ -2358,6 +2356,27 @@ create function void_return_expr() returns void as $$ begin return 5; end;$$ language plpgsql; -ERROR: function returning void cannot specify RETURN expression at or near "5" at character 72 +ERROR: RETURN cannot have a parameter in function returning void at or near "5" at character 72 LINE 3: return 5; ^ +-- VOID functions are allowed to omit RETURN +create function void_return_expr() returns void as $$ +begin + perform 2+2; +end;$$ language plpgsql; +select void_return_expr(); + void_return_expr +------------------ + +(1 row) + +-- but ordinary functions are not +create function missing_return_expr() returns int as $$ +begin + perform 2+2; +end;$$ language plpgsql; +select missing_return_expr(); +ERROR: control reached end of function without RETURN +CONTEXT: PL/pgSQL function "missing_return_expr" +drop function void_return_expr(); +drop function missing_return_expr(); diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql index e8079615f1..7ea7c8c6e0 100644 --- a/src/test/regress/sql/plpgsql.sql +++ b/src/test/regress/sql/plpgsql.sql @@ -1561,7 +1561,8 @@ SELECT * FROM test_ret_rec_dyn(1500) AS (a int, b int, c int); SELECT * FROM test_ret_rec_dyn(5) AS (a int, b numeric, c text); -- --- Test handling of OUT parameters, including polymorphic cases +-- Test handling of OUT parameters, including polymorphic cases. +-- Note that RETURN is optional with OUT params; we try both ways. -- -- wrong way to do it: @@ -1582,7 +1583,6 @@ select * from f1(42); create or replace function f1(inout i int) as $$ begin i := i+1; - return; end$$ language plpgsql; select f1(42); @@ -1608,7 +1608,6 @@ begin j := i; j := j+1; k := 'foo'; - return; end$$ language plpgsql; select f1(42); @@ -1624,7 +1623,6 @@ begin j := j+1; k := 'foot'; return next; - return; end$$ language plpgsql; select * from f1(42); @@ -2001,3 +1999,22 @@ create function void_return_expr() returns void as $$ begin return 5; end;$$ language plpgsql; + +-- VOID functions are allowed to omit RETURN +create function void_return_expr() returns void as $$ +begin + perform 2+2; +end;$$ language plpgsql; + +select void_return_expr(); + +-- but ordinary functions are not +create function missing_return_expr() returns int as $$ +begin + perform 2+2; +end;$$ language plpgsql; + +select missing_return_expr(); + +drop function void_return_expr(); +drop function missing_return_expr(); -- 2.11.0