From 2453ea142233ae57af452019c3b9a443dad1cdd0 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Mon, 5 Oct 2020 09:09:09 +0200 Subject: Support for OUT parameters in procedures Unlike for functions, OUT parameters for procedures are part of the signature. Therefore, they have to be listed in pg_proc.proargtypes as well as mentioned in ALTER PROCEDURE and DROP PROCEDURE. Reviewed-by: Andrew Dunstan Reviewed-by: Pavel Stehule Discussion: https://www.postgresql.org/message-id/flat/2b8490fe-51af-e671-c504-47359dc453c5@2ndquadrant.com --- doc/src/sgml/catalogs.sgml | 5 +-- doc/src/sgml/plpgsql.sgml | 38 ++++++++++++++++++++++ doc/src/sgml/ref/alter_extension.sgml | 11 ++++--- doc/src/sgml/ref/alter_procedure.sgml | 5 +-- doc/src/sgml/ref/comment.sgml | 11 ++++--- doc/src/sgml/ref/create_procedure.sgml | 6 ++-- doc/src/sgml/ref/drop_procedure.sgml | 5 +-- doc/src/sgml/ref/security_label.sgml | 11 ++++--- doc/src/sgml/xfunc.sgml | 59 ++++++++++++++++++++++++++++++++++ 9 files changed, 126 insertions(+), 25 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 0e580b157f5..3927b1030df 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -5875,8 +5875,9 @@ SCRAM-SHA-256$<iteration count>:&l An array with the data types of the function arguments. This includes only input arguments (including INOUT and - VARIADIC arguments), and thus represents - the call signature of the function. + VARIADIC arguments), as well as + OUT parameters of procedures, and thus represents + the call signature of the function or procedure. diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index c2bb3e32685..74b6b258780 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -478,6 +478,14 @@ $$ LANGUAGE plpgsql; included it, but it would be redundant. + + To call a function with OUT parameters, omit the + output parameter in the function call: + +SELECT sales_tax(100.00); + + + Output parameters are most useful when returning multiple values. A trivial example is: @@ -489,6 +497,11 @@ BEGIN prod := x * y; END; $$ LANGUAGE plpgsql; + +SELECT * FROM sum_n_product(2, 4); + sum | prod +-----+------ + 6 | 8 As discussed in , this @@ -497,6 +510,31 @@ $$ LANGUAGE plpgsql; RETURNS record. + + This also works with procedures, for example: + + +CREATE PROCEDURE sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$ +BEGIN + sum := x + y; + prod := x * y; +END; +$$ LANGUAGE plpgsql; + + + In a call to a procedure, all the parameters must be specified. For + output parameters, NULL may be specified. + +CALL sum_n_product(2, 4, NULL, NULL); + sum | prod +-----+------ + 6 | 8 + + Output parameters in procedures become more interesting in nested calls, + where they can be assigned to variables. See for details. + + Another way to declare a PL/pgSQL function is with RETURNS TABLE, for example: diff --git a/doc/src/sgml/ref/alter_extension.sgml b/doc/src/sgml/ref/alter_extension.sgml index c819c7bb4e3..38fd60128b7 100644 --- a/doc/src/sgml/ref/alter_extension.sgml +++ b/doc/src/sgml/ref/alter_extension.sgml @@ -212,11 +212,12 @@ ALTER EXTENSION name DROP IN, OUT, INOUT, or VARIADIC. If omitted, the default is IN. - Note that ALTER EXTENSION does not actually pay - any attention to OUT arguments, since only the input - arguments are needed to determine the function's identity. - So it is sufficient to list the IN, INOUT, - and VARIADIC arguments. + Note that ALTER EXTENSION does not actually pay any + attention to OUT arguments for functions and + aggregates (but not procedures), since only the input arguments are + needed to determine the function's identity. So it is sufficient to + list the IN, INOUT, and + VARIADIC arguments for functions and aggregates. diff --git a/doc/src/sgml/ref/alter_procedure.sgml b/doc/src/sgml/ref/alter_procedure.sgml index bcf45c7a85f..5c176fb5d87 100644 --- a/doc/src/sgml/ref/alter_procedure.sgml +++ b/doc/src/sgml/ref/alter_procedure.sgml @@ -81,8 +81,9 @@ ALTER PROCEDURE name [ ( [ [ name [ - The mode of an argument: IN or VARIADIC. - If omitted, the default is IN. + The mode of an argument: IN, OUT, + INOUT, or VARIADIC. If omitted, + the default is IN. diff --git a/doc/src/sgml/ref/security_label.sgml b/doc/src/sgml/ref/security_label.sgml index e9688cce214..9b87bcd5196 100644 --- a/doc/src/sgml/ref/security_label.sgml +++ b/doc/src/sgml/ref/security_label.sgml @@ -127,11 +127,12 @@ SECURITY LABEL [ FOR provider ] ON argument: IN, OUT, INOUT, or VARIADIC. If omitted, the default is IN. - Note that SECURITY LABEL does not actually - pay any attention to OUT arguments, since only the input - arguments are needed to determine the function's identity. - So it is sufficient to list the IN, INOUT, - and VARIADIC arguments. + Note that SECURITY LABEL does not actually pay any + attention to OUT arguments for functions and + aggregates (but not procedures), since only the input arguments are + needed to determine the function's identity. So it is sufficient to + list the IN, INOUT, and + VARIADIC arguments for functions and aggregates. diff --git a/doc/src/sgml/xfunc.sgml b/doc/src/sgml/xfunc.sgml index 8c74c11d3b5..2863f7c2065 100644 --- a/doc/src/sgml/xfunc.sgml +++ b/doc/src/sgml/xfunc.sgml @@ -179,6 +179,24 @@ SELECT clean_emp(); + + You can also write this as a procedure, thus avoiding the issue of the + return type. For example: + +CREATE PROCEDURE clean_emp() AS ' + DELETE FROM emp + WHERE salary < 0; +' LANGUAGE SQL; + +CALL clean_emp(); + + In simple cases like this, the difference between a function returning + void and a procedure is mostly stylistic. However, + procedures offer additional functionality such as transaction control + that is not available in functions. Also, procedures are SQL standard + whereas returning void is a PostgreSQL extension. + + The entire body of a SQL function is parsed before any of it is @@ -716,6 +734,47 @@ DROP FUNCTION sum_n_product (int, int); + + <acronym>SQL</acronym> Procedures with Output Parameters + + + procedures + output parameter + + + + Output parameters are also supported in procedures, but they work a bit + differently from functions. Notably, output parameters + are included in the signature of a procedure and + must be specified in the procedure call. + + + + For example, the bank account debiting routine from earlier could be + written like this: + +CREATE PROCEDURE tp1 (accountno integer, debit numeric, OUT new_balance numeric) AS $$ + UPDATE bank + SET balance = balance - debit + WHERE accountno = tp1.accountno + RETURNING balance; +$$ LANGUAGE SQL; + + To call this procedure, it is irrelevant what is passed as the argument + of the OUT parameter, so you could pass + NULL: + +CALL tp1(17, 100.0, NULL); + + + + + Procedures with output parameters are more useful in PL/pgSQL, where the + output parameters can be assigned to variables. See for details. + + + <acronym>SQL</acronym> Functions with Variable Numbers of Arguments -- cgit v1.2.3