From e4128ee767df3c8c715eb08f8977647ae49dfb59 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Thu, 30 Nov 2017 08:46:13 -0500 Subject: SQL procedures This adds a new object type "procedure" that is similar to a function but does not have a return type and is invoked by the new CALL statement instead of SELECT or similar. This implementation is aligned with the SQL standard and compatible with or similar to other SQL implementations. This commit adds new commands CALL, CREATE/ALTER/DROP PROCEDURE, as well as ALTER/DROP ROUTINE that can refer to either a function or a procedure (or an aggregate function, as an extension to SQL). There is also support for procedures in various utility commands such as COMMENT and GRANT, as well as support in pg_dump and psql. Support for defining procedures is available in all the languages supplied by the core distribution. While this commit is mainly syntax sugar around existing functionality, future features will rely on having procedures as a separate object type. Reviewed-by: Andrew Dunstan --- doc/src/sgml/catalogs.sgml | 2 +- doc/src/sgml/ddl.sgml | 2 +- doc/src/sgml/ecpg.sgml | 4 +- doc/src/sgml/information_schema.sgml | 18 +- doc/src/sgml/plperl.sgml | 4 + doc/src/sgml/plpgsql.sgml | 17 +- doc/src/sgml/plpython.sgml | 6 +- doc/src/sgml/pltcl.sgml | 3 +- doc/src/sgml/ref/allfiles.sgml | 6 + doc/src/sgml/ref/alter_default_privileges.sgml | 12 +- doc/src/sgml/ref/alter_extension.sgml | 12 +- doc/src/sgml/ref/alter_function.sgml | 2 + doc/src/sgml/ref/alter_procedure.sgml | 281 ++++++++++++++++++++ doc/src/sgml/ref/alter_routine.sgml | 102 ++++++++ doc/src/sgml/ref/call.sgml | 97 +++++++ doc/src/sgml/ref/comment.sgml | 13 +- doc/src/sgml/ref/create_function.sgml | 10 +- doc/src/sgml/ref/create_procedure.sgml | 341 +++++++++++++++++++++++++ doc/src/sgml/ref/drop_function.sgml | 2 + doc/src/sgml/ref/drop_procedure.sgml | 162 ++++++++++++ doc/src/sgml/ref/drop_routine.sgml | 94 +++++++ doc/src/sgml/ref/grant.sgml | 25 +- doc/src/sgml/ref/revoke.sgml | 4 +- doc/src/sgml/ref/security_label.sgml | 12 +- doc/src/sgml/reference.sgml | 6 + doc/src/sgml/xfunc.sgml | 33 +++ 26 files changed, 1222 insertions(+), 48 deletions(-) create mode 100644 doc/src/sgml/ref/alter_procedure.sgml create mode 100644 doc/src/sgml/ref/alter_routine.sgml create mode 100644 doc/src/sgml/ref/call.sgml create mode 100644 doc/src/sgml/ref/create_procedure.sgml create mode 100644 doc/src/sgml/ref/drop_procedure.sgml create mode 100644 doc/src/sgml/ref/drop_routine.sgml (limited to 'doc/src') diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index da881a77371..3f02202cafb 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -5241,7 +5241,7 @@ SCRAM-SHA-256$<iteration count>:&l prorettype oid pg_type.oid - Data type of the return value + Data type of the return value, or null for a procedure diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index e6f50ec819b..9f583266de9 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -3947,7 +3947,7 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01'; - Functions and operators + Functions, procedures, and operators diff --git a/doc/src/sgml/ecpg.sgml b/doc/src/sgml/ecpg.sgml index d1872c1a5c6..5a8d1f1b95b 100644 --- a/doc/src/sgml/ecpg.sgml +++ b/doc/src/sgml/ecpg.sgml @@ -4778,7 +4778,9 @@ EXEC SQL WHENEVER condition actionDO name (args) - Call the specified C functions with the specified arguments. + Call the specified C functions with the specified arguments. (This + use is different from the meaning of CALL + and DO in the normal PostgreSQL grammar.) diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgml index 99b0ea8519e..0faa72f1d3f 100644 --- a/doc/src/sgml/information_schema.sgml +++ b/doc/src/sgml/information_schema.sgml @@ -3972,8 +3972,8 @@ ORDER BY c.ordinal_position; <literal>routines</literal> - The view routines contains all functions in the - current database. Only those functions are shown that the current + The view routines contains all functions and procedures in the + current database. Only those functions and procedures are shown that the current user has access to (by way of being the owner or having some privilege). @@ -4037,8 +4037,8 @@ ORDER BY c.ordinal_position; routine_type character_data - Always FUNCTION (In the future there might - be other types of routines.) + FUNCTION for a + function, PROCEDURE for a procedure @@ -4087,7 +4087,7 @@ ORDER BY c.ordinal_position; the view element_types), else USER-DEFINED (in that case, the type is identified in type_udt_name and associated - columns). + columns). Null for a procedure. @@ -4180,7 +4180,7 @@ ORDER BY c.ordinal_position; sql_identifier Name of the database that the return data type of the function - is defined in (always the current database) + is defined in (always the current database). Null for a procedure. @@ -4189,7 +4189,7 @@ ORDER BY c.ordinal_position; sql_identifier Name of the schema that the return data type of the function is - defined in + defined in. Null for a procedure. @@ -4197,7 +4197,7 @@ ORDER BY c.ordinal_position; type_udt_name sql_identifier - Name of the return data type of the function + Name of the return data type of the function. Null for a procedure. @@ -4314,7 +4314,7 @@ ORDER BY c.ordinal_position; If the function automatically returns null if any of its arguments are null, then YES, else - NO. + NO. Null for a procedure. diff --git a/doc/src/sgml/plperl.sgml b/doc/src/sgml/plperl.sgml index 33e39d85e40..100162dead5 100644 --- a/doc/src/sgml/plperl.sgml +++ b/doc/src/sgml/plperl.sgml @@ -67,6 +67,10 @@ $$ LANGUAGE plperl; as discussed below. + + In a PL/Perl procedure, any return value from the Perl code is ignored. + + PL/Perl also supports anonymous code blocks called with the statement: diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index 6d14b344487..7d23ed437e2 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -156,7 +156,8 @@ Finally, a PL/pgSQL function can be declared to return - void if it has no useful return value. + void if it has no useful return value. (Alternatively, it + could be written as a procedure in that case.) @@ -1865,6 +1866,18 @@ SELECT * FROM get_available_flightid(CURRENT_DATE); + + Returning From a Procedure + + + A procedure does not have a return value. A procedure can therefore end + without a RETURN statement. If + a RETURN statement is desired to exit the code early, + then NULL must be returned. Returning any other value + will result in an error. + + + Conditionals @@ -5244,7 +5257,7 @@ show errors; Here is how this function would end up in PostgreSQL: -CREATE OR REPLACE FUNCTION cs_update_referrer_type_proc() RETURNS void AS $func$ +CREATE OR REPLACE PROCEDURE cs_update_referrer_type_proc() AS $func$ DECLARE referrer_keys CURSOR IS SELECT * FROM cs_referrer_keys diff --git a/doc/src/sgml/plpython.sgml b/doc/src/sgml/plpython.sgml index ec5f671632d..0dbeee1fa2e 100644 --- a/doc/src/sgml/plpython.sgml +++ b/doc/src/sgml/plpython.sgml @@ -207,7 +207,11 @@ $$ LANGUAGE plpythonu; yield (in case of a result-set statement). If you do not provide a return value, Python returns the default None. PL/Python translates - Python's None into the SQL null value. + Python's None into the SQL null value. In a procedure, + the result from the Python code must be None (typically + achieved by ending the procedure without a return + statement or by using a return statement without + argument); otherwise, an error will be raised. diff --git a/doc/src/sgml/pltcl.sgml b/doc/src/sgml/pltcl.sgml index 0646a8ba0ba..8018783b0a0 100644 --- a/doc/src/sgml/pltcl.sgml +++ b/doc/src/sgml/pltcl.sgml @@ -97,7 +97,8 @@ $$ LANGUAGE pltcl; Tcl script as variables named 1 ... n. The result is returned from the Tcl code in the usual way, with - a return statement. + a return statement. In a procedure, the return value + from the Tcl code is ignored. diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml index 01acc2ef9da..22e68932115 100644 --- a/doc/src/sgml/ref/allfiles.sgml +++ b/doc/src/sgml/ref/allfiles.sgml @@ -26,8 +26,10 @@ Complete list of usable sgml source files in this directory. + + @@ -48,6 +50,7 @@ Complete list of usable sgml source files in this directory. + @@ -75,6 +78,7 @@ Complete list of usable sgml source files in this directory. + @@ -122,8 +126,10 @@ Complete list of usable sgml source files in this directory. + + diff --git a/doc/src/sgml/ref/alter_default_privileges.sgml b/doc/src/sgml/ref/alter_default_privileges.sgml index ab2c35b4dd0..0c09f1db5cd 100644 --- a/doc/src/sgml/ref/alter_default_privileges.sgml +++ b/doc/src/sgml/ref/alter_default_privileges.sgml @@ -39,7 +39,7 @@ GRANT { { USAGE | SELECT | UPDATE } TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ] GRANT { EXECUTE | ALL [ PRIVILEGES ] } - ON FUNCTIONS + ON { FUNCTIONS | ROUTINES } TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ] GRANT { USAGE | ALL [ PRIVILEGES ] } @@ -66,7 +66,7 @@ REVOKE [ GRANT OPTION FOR ] REVOKE [ GRANT OPTION FOR ] { EXECUTE | ALL [ PRIVILEGES ] } - ON FUNCTIONS + ON { FUNCTIONS | ROUTINES } FROM { [ GROUP ] role_name | PUBLIC } [, ...] [ CASCADE | RESTRICT ] @@ -93,7 +93,13 @@ REVOKE [ GRANT OPTION FOR ] affect privileges assigned to already-existing objects.) Currently, only the privileges for schemas, tables (including views and foreign tables), sequences, functions, and types (including domains) can be - altered. + altered. For this command, functions include aggregates and procedures. + The words FUNCTIONS and ROUTINES are + equivalent in this command. (ROUTINES is preferred + going forward as the standard term for functions and procedures taken + together. In earlier PostgreSQL releases, only the + word FUNCTIONS was allowed. It is not possible to set + default privileges for functions and procedures separately.) diff --git a/doc/src/sgml/ref/alter_extension.sgml b/doc/src/sgml/ref/alter_extension.sgml index e54925507e1..a2d405d6cdf 100644 --- a/doc/src/sgml/ref/alter_extension.sgml +++ b/doc/src/sgml/ref/alter_extension.sgml @@ -45,6 +45,8 @@ ALTER EXTENSION name DROP object_name USING index_method | OPERATOR FAMILY object_name USING index_method | [ PROCEDURAL ] LANGUAGE object_name | + PROCEDURE procedure_name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] | + ROUTINE routine_name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] | SCHEMA object_name | SEQUENCE object_name | SERVER object_name | @@ -170,12 +172,14 @@ ALTER EXTENSION name DROP aggregate_name function_name operator_name + procedure_name + routine_name The name of an object to be added to or removed from the extension. Names of tables, aggregates, domains, foreign tables, functions, operators, - operator classes, operator families, sequences, text search objects, + operator classes, operator families, procedures, routines, sequences, text search objects, types, and views can be schema-qualified. @@ -204,7 +208,7 @@ ALTER EXTENSION name DROP - The mode of a function or aggregate + The mode of a function, procedure, or aggregate argument: IN, OUT, INOUT, or VARIADIC. If omitted, the default is IN. @@ -222,7 +226,7 @@ ALTER EXTENSION name DROP - The name of a function or aggregate argument. + The name of a function, procedure, or aggregate argument. Note that ALTER EXTENSION does not actually pay any attention to argument names, since only the argument data types are needed to determine the function's identity. @@ -235,7 +239,7 @@ ALTER EXTENSION name DROP - The data type of a function or aggregate argument. + The data type of a function, procedure, or aggregate argument. diff --git a/doc/src/sgml/ref/alter_function.sgml b/doc/src/sgml/ref/alter_function.sgml index 196d2dde0c0..d8747e07482 100644 --- a/doc/src/sgml/ref/alter_function.sgml +++ b/doc/src/sgml/ref/alter_function.sgml @@ -359,6 +359,8 @@ ALTER FUNCTION check_password(text) RESET search_path; + + diff --git a/doc/src/sgml/ref/alter_procedure.sgml b/doc/src/sgml/ref/alter_procedure.sgml new file mode 100644 index 00000000000..dae80076d95 --- /dev/null +++ b/doc/src/sgml/ref/alter_procedure.sgml @@ -0,0 +1,281 @@ + + + + + ALTER PROCEDURE + + + + ALTER PROCEDURE + 7 + SQL - Language Statements + + + + ALTER PROCEDURE + change the definition of a procedure + + + + +ALTER PROCEDURE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] + action [ ... ] [ RESTRICT ] +ALTER PROCEDURE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] + RENAME TO new_name +ALTER PROCEDURE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] + OWNER TO { new_owner | CURRENT_USER | SESSION_USER } +ALTER PROCEDURE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] + SET SCHEMA new_schema +ALTER PROCEDURE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] + DEPENDS ON EXTENSION extension_name + +where action is one of: + + [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER + SET configuration_parameter { TO | = } { value | DEFAULT } + SET configuration_parameter FROM CURRENT + RESET configuration_parameter + RESET ALL + + + + + Description + + + ALTER PROCEDURE changes the definition of a + procedure. + + + + You must own the procedure to use ALTER PROCEDURE. + To change a procedure's schema, you must also have CREATE + privilege on the new schema. + To alter the owner, you must also be a direct or indirect member of the new + owning role, and that role must have CREATE privilege on + the procedure's schema. (These restrictions enforce that altering the owner + doesn't do anything you couldn't do by dropping and recreating the procedure. + However, a superuser can alter ownership of any procedure anyway.) + + + + + Parameters + + + + name + + + The name (optionally schema-qualified) of an existing procedure. If no + argument list is specified, the name must be unique in its schema. + + + + + + argmode + + + + The mode of an argument: IN or VARIADIC. + If omitted, the default is IN. + + + + + + argname + + + + The name of an argument. + Note that ALTER PROCEDURE does not actually pay + any attention to argument names, since only the argument data + types are needed to determine the procedure's identity. + + + + + + argtype + + + + The data type(s) of the procedure's arguments (optionally + schema-qualified), if any. + + + + + + new_name + + + The new name of the procedure. + + + + + + new_owner + + + The new owner of the procedure. Note that if the procedure is + marked SECURITY DEFINER, it will subsequently + execute as the new owner. + + + + + + new_schema + + + The new schema for the procedure. + + + + + + extension_name + + + The name of the extension that the procedure is to depend on. + + + + + + EXTERNAL SECURITY INVOKER + EXTERNAL SECURITY DEFINER + + + + Change whether the procedure is a security definer or not. The + key word EXTERNAL is ignored for SQL + conformance. See for more information about + this capability. + + + + + + configuration_parameter + value + + + Add or change the assignment to be made to a configuration parameter + when the procedure is called. If + value is DEFAULT + or, equivalently, RESET is used, the procedure-local + setting is removed, so that the procedure executes with the value + present in its environment. Use RESET + ALL to clear all procedure-local settings. + SET FROM CURRENT saves the value of the parameter that + is current when ALTER PROCEDURE is executed as the value + to be applied when the procedure is entered. + + + + See and + + for more information about allowed parameter names and values. + + + + + + RESTRICT + + + + Ignored for conformance with the SQL standard. + + + + + + + + Examples + + + To rename the procedure insert_data with two arguments + of type integer to insert_record: + +ALTER PROCEDURE insert_data(integer, integer) RENAME TO insert_record; + + + + + To change the owner of the procedure insert_data with + two arguments of type integer to joe: + +ALTER PROCEDURE insert_data(integer, integer) OWNER TO joe; + + + + + To change the schema of the procedure insert_data with + two arguments of type integer + to accounting: + +ALTER PROCEDURE insert_data(integer, integer) SET SCHEMA accounting; + + + + + To mark the procedure insert_data(integer, integer) as + being dependent on the extension myext: + +ALTER PROCEDURE insert_data(integer, integer) DEPENDS ON EXTENSION myext; + + + + + To adjust the search path that is automatically set for a procedure: + +ALTER PROCEDURE check_password(text) SET search_path = admin, pg_temp; + + + + + To disable automatic setting of search_path for a procedure: + +ALTER PROCEDURE check_password(text) RESET search_path; + + The procedure will now execute with whatever search path is used by its + caller. + + + + + Compatibility + + + This statement is partially compatible with the ALTER + PROCEDURE statement in the SQL standard. The standard allows more + properties of a procedure to be modified, but does not provide the + ability to rename a procedure, make a procedure a security definer, + attach configuration parameter values to a procedure, + or change the owner, schema, or volatility of a procedure. The standard also + requires the RESTRICT key word, which is optional in + PostgreSQL. + + + + + See Also + + + + + + + + + diff --git a/doc/src/sgml/ref/alter_routine.sgml b/doc/src/sgml/ref/alter_routine.sgml new file mode 100644 index 00000000000..d1699691e10 --- /dev/null +++ b/doc/src/sgml/ref/alter_routine.sgml @@ -0,0 +1,102 @@ + + + + + ALTER ROUTINE + + + + ALTER ROUTINE + 7 + SQL - Language Statements + + + + ALTER ROUTINE + change the definition of a routine + + + + +ALTER ROUTINE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] + action [ ... ] [ RESTRICT ] +ALTER ROUTINE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] + RENAME TO new_name +ALTER ROUTINE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] + OWNER TO { new_owner | CURRENT_USER | SESSION_USER } +ALTER ROUTINE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] + SET SCHEMA new_schema +ALTER ROUTINE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] + DEPENDS ON EXTENSION extension_name + +where action is one of: + + IMMUTABLE | STABLE | VOLATILE | [ NOT ] LEAKPROOF + [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER + PARALLEL { UNSAFE | RESTRICTED | SAFE } + COST execution_cost + ROWS result_rows + SET configuration_parameter { TO | = } { value | DEFAULT } + SET configuration_parameter FROM CURRENT + RESET configuration_parameter + RESET ALL + + + + + Description + + + ALTER ROUTINE changes the definition of a routine, which + can be an aggregate function, a normal function, or a procedure. See + under , , + and for the description of the + parameters, more examples, and further details. + + + + + Examples + + + To rename the routine foo for type + integer to foobar: + +ALTER ROUTINE foo(integer) RENAME TO foobar; + + This command will work independent of whether foo is an + aggregate, function, or procedure. + + + + + Compatibility + + + This statement is partially compatible with the ALTER + ROUTINE statement in the SQL standard. See + under + and for more details. Allowing + routine names to refer to aggregate functions is + a PostgreSQL extension. + + + + + See Also + + + + + + + + + + Note that there is no CREATE ROUTINE command. + + + diff --git a/doc/src/sgml/ref/call.sgml b/doc/src/sgml/ref/call.sgml new file mode 100644 index 00000000000..2741d8d15ec --- /dev/null +++ b/doc/src/sgml/ref/call.sgml @@ -0,0 +1,97 @@ + + + + + CALL + + + + CALL + 7 + SQL - Language Statements + + + + CALL + invoke a procedure + + + + +CALL name ( [ argument ] [ , ...] ) + + + + + Description + + + CALL executes a procedure. + + + + + Parameters + + + + name + + + The name (optionally schema-qualified) of the procedure. + + + + + + argument + + + An argument for the procedure call. + See for the full details on + function and procedure call syntax, including use of named parameters. + + + + + + + + Notes + + + The user must have EXECUTE privilege on the procedure in + order to be allowed to invoke it. + + + + To call a function (not a procedure), use SELECT instead. + + + + + Examples + +CALL do_db_maintenance(); + + + + + Compatibility + + + CALL conforms to the SQL standard. + + + + + See Also + + + + + + diff --git a/doc/src/sgml/ref/comment.sgml b/doc/src/sgml/ref/comment.sgml index 7d66c1a34ca..965c5a40ad7 100644 --- a/doc/src/sgml/ref/comment.sgml +++ b/doc/src/sgml/ref/comment.sgml @@ -46,8 +46,10 @@ COMMENT ON OPERATOR FAMILY object_name USING index_method | POLICY policy_name ON table_name | [ PROCEDURAL ] LANGUAGE object_name | + PROCEDURE procedure_name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] | PUBLICATION object_name | ROLE object_name | + ROUTINE routine_name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] | RULE rule_name ON table_name | SCHEMA object_name | SEQUENCE object_name | @@ -121,13 +123,15 @@ COMMENT ON function_name operator_name policy_name + procedure_name + routine_name rule_name trigger_name The name of the object to be commented. Names of tables, aggregates, collations, conversions, domains, foreign tables, functions, - indexes, operators, operator classes, operator families, sequences, + indexes, operators, operator classes, operator families, procedures, routines, sequences, statistics, text search objects, types, and views can be schema-qualified. When commenting on a column, relation_name must refer @@ -170,7 +174,7 @@ COMMENT ON argmode - The mode of a function or aggregate + The mode of a function, procedure, or aggregate argument: IN, OUT, INOUT, or VARIADIC. If omitted, the default is IN. @@ -187,7 +191,7 @@ COMMENT ON argname - The name of a function or aggregate argument. + The name of a function, procedure, or aggregate argument. Note that COMMENT does not actually pay any attention to argument names, since only the argument data types are needed to determine the function's identity. @@ -199,7 +203,7 @@ COMMENT ON argtype - The data type of a function or aggregate argument. + The data type of a function, procedure, or aggregate argument. @@ -325,6 +329,7 @@ COMMENT ON OPERATOR - (NONE, integer) IS 'Unary minus'; COMMENT ON OPERATOR CLASS int4ops USING btree IS '4 byte integer operators for btrees'; COMMENT ON OPERATOR FAMILY integer_ops USING btree IS 'all integer operators for btrees'; COMMENT ON POLICY my_policy ON mytable IS 'Filter rows by users'; +COMMENT ON PROCEDURE my_proc (integer, integer) IS 'Runs a report'; COMMENT ON ROLE my_role IS 'Administration group for finance tables'; COMMENT ON RULE my_rule ON my_table IS 'Logs updates of employee records'; COMMENT ON SCHEMA my_schema IS 'Departmental data'; diff --git a/doc/src/sgml/ref/create_function.sgml b/doc/src/sgml/ref/create_function.sgml index 75331165fef..fd229d11937 100644 --- a/doc/src/sgml/ref/create_function.sgml +++ b/doc/src/sgml/ref/create_function.sgml @@ -55,9 +55,9 @@ CREATE [ OR REPLACE ] FUNCTION If a schema name is included, then the function is created in the specified schema. Otherwise it is created in the current schema. - The name of the new function must not match any existing function + The name of the new function must not match any existing function or procedure with the same input argument types in the same schema. However, - functions of different argument types can share a name (this is + functions and procedures of different argument types can share a name (this is called overloading). @@ -450,7 +450,7 @@ CREATE [ OR REPLACE ] FUNCTION - execution_cost + COST execution_cost @@ -466,7 +466,7 @@ CREATE [ OR REPLACE ] FUNCTION - result_rows + ROWS result_rows @@ -818,7 +818,7 @@ COMMIT; Compatibility - A CREATE FUNCTION command is defined in SQL:1999 and later. + A CREATE FUNCTION command is defined in the SQL standard. The PostgreSQL version is similar but not fully compatible. The attributes are not portable, neither are the different available languages. diff --git a/doc/src/sgml/ref/create_procedure.sgml b/doc/src/sgml/ref/create_procedure.sgml new file mode 100644 index 00000000000..d712043824a --- /dev/null +++ b/doc/src/sgml/ref/create_procedure.sgml @@ -0,0 +1,341 @@ + + + + + CREATE PROCEDURE + + + + CREATE PROCEDURE + 7 + SQL - Language Statements + + + + CREATE PROCEDURE + define a new procedure + + + + +CREATE [ OR REPLACE ] PROCEDURE + name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] ) + { LANGUAGE lang_name + | TRANSFORM { FOR TYPE type_name } [, ... ] + | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER + | SET configuration_parameter { TO value | = value | FROM CURRENT } + | AS 'definition' + | AS 'obj_file', 'link_symbol' + } ... + + + + + Description + + + CREATE PROCEDURE defines a new procedure. + CREATE OR REPLACE PROCEDURE will either create a + new procedure, or replace an existing definition. + To be able to define a procedure, the user must have the + USAGE privilege on the language. + + + + If a schema name is included, then the procedure is created in the + specified schema. Otherwise it is created in the current schema. + The name of the new procedure must not match any existing procedure or function + with the same input argument types in the same schema. However, + procedures and functions of different argument types can share a name (this is + called overloading). + + + + To replace the current definition of an existing procedure, use + CREATE OR REPLACE PROCEDURE. It is not possible + to change the name or argument types of a procedure this way (if you + tried, you would actually be creating a new, distinct procedure). + + + + When CREATE OR REPLACE PROCEDURE is used to replace an + existing procedure, the ownership and permissions of the procedure + do not change. All other procedure properties are assigned the + values specified or implied in the command. You must own the procedure + to replace it (this includes being a member of the owning role). + + + + The user that creates the procedure becomes the owner of the procedure. + + + + To be able to create a procedure, you must have USAGE + privilege on the argument types. + + + + + Parameters + + + + name + + + + The name (optionally schema-qualified) of the procedure to create. + + + + + + argmode + + + + The mode of an argument: IN or VARIADIC. + If omitted, the default is IN. + + + + + + argname + + + + The name of an argument. + + + + + + argtype + + + + The data type(s) of the procedure's arguments (optionally + schema-qualified), if any. The argument types can be base, composite, + or domain types, or can reference the type of a table column. + + + Depending on the implementation language it might also be allowed + to specify pseudo-types such as cstring. + Pseudo-types indicate that the actual argument type is either + incompletely specified, or outside the set of ordinary SQL data types. + + + The type of a column is referenced by writing + table_name.column_name%TYPE. + Using this feature can sometimes help make a procedure independent of + changes to the definition of a table. + + + + + + default_expr + + + + An expression to be used as default value if the parameter is + not specified. The expression has to be coercible to the + argument type of the parameter. + All input parameters following a + parameter with a default value must have default values as well. + + + + + + lang_name + + + + The name of the language that the procedure is implemented in. + It can be sql, c, + internal, or the name of a user-defined + procedural language, e.g. plpgsql. Enclosing the + name in single quotes is deprecated and requires matching case. + + + + + + TRANSFORM { FOR TYPE type_name } [, ... ] } + + + + Lists which transforms a call to the procedure should apply. Transforms + convert between SQL types and language-specific data types; + see . Procedural language + implementations usually have hardcoded knowledge of the built-in types, + so those don't need to be listed here. If a procedural language + implementation does not know how to handle a type and no transform is + supplied, it will fall back to a default behavior for converting data + types, but this depends on the implementation. + + + + + + EXTERNAL SECURITY INVOKER + EXTERNAL SECURITY DEFINER + + + SECURITY INVOKER indicates that the procedure + is to be executed with the privileges of the user that calls it. + That is the default. SECURITY DEFINER + specifies that the procedure is to be executed with the + privileges of the user that owns it. + + + + The key word EXTERNAL is allowed for SQL + conformance, but it is optional since, unlike in SQL, this feature + applies to all procedures not only external ones. + + + + + + configuration_parameter + value + + + The SET clause causes the specified configuration + parameter to be set to the specified value when the procedure is + entered, and then restored to its prior value when the procedure exits. + SET FROM CURRENT saves the value of the parameter that + is current when CREATE PROCEDURE is executed as the value + to be applied when the procedure is entered. + + + + If a SET clause is attached to a procedure, then + the effects of a SET LOCAL command executed inside the + procedure for the same variable are restricted to the procedure: the + configuration parameter's prior value is still restored at procedure exit. + However, an ordinary + SET command (without LOCAL) overrides the + SET clause, much as it would do for a previous SET + LOCAL command: the effects of such a command will persist after + procedure exit, unless the current transaction is rolled back. + + + + See and + + for more information about allowed parameter names and values. + + + + + + definition + + + + A string constant defining the procedure; the meaning depends on the + language. It can be an internal procedure name, the path to an + object file, an SQL command, or text in a procedural language. + + + + It is often helpful to use dollar quoting (see ) to write the procedure definition + string, rather than the normal single quote syntax. Without dollar + quoting, any single quotes or backslashes in the procedure definition must + be escaped by doubling them. + + + + + + + obj_file, link_symbol + + + + This form of the AS clause is used for + dynamically loadable C language procedures when the procedure name + in the C language source code is not the same as the name of + the SQL procedure. The string obj_file is the name of the shared + library file containing the compiled C procedure, and is interpreted + as for the command. The string + link_symbol is the + procedure's link symbol, that is, the name of the procedure in the C + language source code. If the link symbol is omitted, it is assumed + to be the same as the name of the SQL procedure being defined. + + + + When repeated CREATE PROCEDURE calls refer to + the same object file, the file is only loaded once per session. + To unload and + reload the file (perhaps during development), start a new session. + + + + + + + + + Notes + + + See for more details on function + creation that also apply to procedures. + + + + Use to execute a procedure. + + + + + Examples + + +CREATE PROCEDURE insert_data(a integer, b integer) +LANGUAGE SQL +AS $$ +INSERT INTO tbl VALUES (a); +INSERT INTO tbl VALUES (b); +$$; + +CALL insert_data(1, 2); + + + + + Compatibility + + + A CREATE PROCEDURE command is defined in the SQL + standard. The PostgreSQL version is similar but + not fully compatible. For details see + also . + + + + + + See Also + + + + + + + + + + diff --git a/doc/src/sgml/ref/drop_function.sgml b/doc/src/sgml/ref/drop_function.sgml index eda1a59c846..127fdfe4197 100644 --- a/doc/src/sgml/ref/drop_function.sgml +++ b/doc/src/sgml/ref/drop_function.sgml @@ -185,6 +185,8 @@ DROP FUNCTION update_employee_salaries(); + + diff --git a/doc/src/sgml/ref/drop_procedure.sgml b/doc/src/sgml/ref/drop_procedure.sgml new file mode 100644 index 00000000000..fef61b66ac1 --- /dev/null +++ b/doc/src/sgml/ref/drop_procedure.sgml @@ -0,0 +1,162 @@ + + + + + DROP PROCEDURE + + + + DROP PROCEDURE + 7 + SQL - Language Statements + + + + DROP PROCEDURE + remove a procedure + + + + +DROP PROCEDURE [ IF EXISTS ] name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] [, ...] + [ CASCADE | RESTRICT ] + + + + + Description + + + DROP PROCEDURE removes the definition of an existing + procedure. To execute this command the user must be the + owner of the procedure. The argument types to the + procedure must be specified, since several different procedures + can exist with the same name and different argument lists. + + + + + Parameters + + + + IF EXISTS + + + Do not throw an error if the procedure does not exist. A notice is issued + in this case. + + + + + + name + + + The name (optionally schema-qualified) of an existing procedure. If no + argument list is specified, the name must be unique in its schema. + + + + + + argmode + + + + The mode of an argument: IN or VARIADIC. + If omitted, the default is IN. + + + + + + argname + + + + The name of an argument. + Note that DROP PROCEDURE does not actually pay + any attention to argument names, since only the argument data + types are needed to determine the procedure's identity. + + + + + + argtype + + + + The data type(s) of the procedure's arguments (optionally + schema-qualified), if any. + + + + + + CASCADE + + + Automatically drop objects that depend on the procedure, + and in turn all objects that depend on those objects + (see ). + + + + + + RESTRICT + + + Refuse to drop the procedure if any objects depend on it. This + is the default. + + + + + + + + Examples + + +DROP PROCEDURE do_db_maintenance(); + + + + + Compatibility + + + This command conforms to the SQL standard, with + these PostgreSQL extensions: + + + The standard only allows one procedure to be dropped per command. + + + The IF EXISTS option + + + The ability to specify argument modes and names + + + + + + + See Also + + + + + + + + + + diff --git a/doc/src/sgml/ref/drop_routine.sgml b/doc/src/sgml/ref/drop_routine.sgml new file mode 100644 index 00000000000..5cd1a0f11eb --- /dev/null +++ b/doc/src/sgml/ref/drop_routine.sgml @@ -0,0 +1,94 @@ + + + + + DROP ROUTINE + + + + DROP ROUTINE + 7 + SQL - Language Statements + + + + DROP ROUTINE + remove a routine + + + + +DROP ROUTINE [ IF EXISTS ] name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] [, ...] + [ CASCADE | RESTRICT ] + + + + + Description + + + DROP ROUTINE removes the definition of an existing + routine, which can be an aggregate function, a normal function, or a + procedure. See + under , , + and for the description of the + parameters, more examples, and further details. + + + + + Examples + + + To drop the routine foo for type + integer: + +DROP ROUTINE foo(integer); + + This command will work independent of whether foo is an + aggregate, function, or procedure. + + + + + Compatibility + + + This command conforms to the SQL standard, with + these PostgreSQL extensions: + + + The standard only allows one routine to be dropped per command. + + + The IF EXISTS option + + + The ability to specify argument modes and names + + + Aggregate functions are an extension. + + + + + + + See Also + + + + + + + + + + Note that there is no CREATE ROUTINE command. + + + + diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml index a5e895d09d7..ff64c7a3bae 100644 --- a/doc/src/sgml/ref/grant.sgml +++ b/doc/src/sgml/ref/grant.sgml @@ -55,8 +55,8 @@ GRANT { USAGE | ALL [ PRIVILEGES ] } TO role_specification [, ...] [ WITH GRANT OPTION ] GRANT { EXECUTE | ALL [ PRIVILEGES ] } - ON { FUNCTION function_name [ ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) ] [, ...] - | ALL FUNCTIONS IN SCHEMA schema_name [, ...] } + ON { { FUNCTION | PROCEDURE | ROUTINE } routine_name [ ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) ] [, ...] + | ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA schema_name [, ...] } TO role_specification [, ...] [ WITH GRANT OPTION ] GRANT { USAGE | ALL [ PRIVILEGES ] } @@ -96,7 +96,7 @@ GRANT role_name [, ...] TO The GRANT command has two basic variants: one that grants privileges on a database object (table, column, view, foreign - table, sequence, database, foreign-data wrapper, foreign server, function, + table, sequence, database, foreign-data wrapper, foreign server, function, procedure, procedural language, schema, or tablespace), and one that grants membership in a role. These variants are similar in many ways, but they are different enough to be described separately. @@ -115,8 +115,11 @@ GRANT role_name [, ...] TO There is also an option to grant privileges on all objects of the same type within one or more schemas. This functionality is currently supported - only for tables, sequences, and functions (but note that ALL - TABLES is considered to include views and foreign tables). + only for tables, sequences, functions, and procedures. ALL + TABLES also affects views and foreign tables, just like the + specific-object GRANT command. ALL + FUNCTIONS also affects aggregate functions, but not procedures, + again just like the specific-object GRANT command. @@ -169,7 +172,7 @@ GRANT role_name [, ...] TO PUBLIC are as follows: CONNECT and TEMPORARY (create temporary tables) privileges for databases; - EXECUTE privilege for functions; and + EXECUTE privilege for functions and procedures; and USAGE privilege for languages and data types (including domains). The object owner can, of course, REVOKE @@ -329,10 +332,12 @@ GRANT role_name [, ...] TO EXECUTE - Allows the use of the specified function and the use of any - operators that are implemented on top of the function. This is - the only type of privilege that is applicable to functions. - (This syntax works for aggregate functions, as well.) + Allows the use of the specified function or procedure and the use of + any operators that are implemented on top of the function. This is the + only type of privilege that is applicable to functions and procedures. + The FUNCTION syntax also works for aggregate + functions. Alternatively, use ROUTINE to refer to a function, + aggregate function, or procedure regardless of what it is. diff --git a/doc/src/sgml/ref/revoke.sgml b/doc/src/sgml/ref/revoke.sgml index 4d133a782b6..7018202f144 100644 --- a/doc/src/sgml/ref/revoke.sgml +++ b/doc/src/sgml/ref/revoke.sgml @@ -70,8 +70,8 @@ REVOKE [ GRANT OPTION FOR ] REVOKE [ GRANT OPTION FOR ] { EXECUTE | ALL [ PRIVILEGES ] } - ON { FUNCTION function_name [ ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) ] [, ...] - | ALL FUNCTIONS IN SCHEMA schema_name [, ...] } + ON { { FUNCTION | PROCEDURE | ROUTINE } function_name [ ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) ] [, ...] + | ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA schema_name [, ...] } FROM { [ GROUP ] role_name | PUBLIC } [, ...] [ CASCADE | RESTRICT ] diff --git a/doc/src/sgml/ref/security_label.sgml b/doc/src/sgml/ref/security_label.sgml index d52113e0359..e9cfdec9f94 100644 --- a/doc/src/sgml/ref/security_label.sgml +++ b/doc/src/sgml/ref/security_label.sgml @@ -34,8 +34,10 @@ SECURITY LABEL [ FOR provider ] ON LARGE OBJECT large_object_oid | MATERIALIZED VIEW object_name | [ PROCEDURAL ] LANGUAGE object_name | + PROCEDURE procedure_name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] | PUBLICATION object_name | ROLE object_name | + ROUTINE routine_name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] | SCHEMA object_name | SEQUENCE object_name | SUBSCRIPTION object_name | @@ -93,10 +95,12 @@ SECURITY LABEL [ FOR provider ] ON table_name.column_name aggregate_name function_name + procedure_name + routine_name The name of the object to be labeled. Names of tables, - aggregates, domains, foreign tables, functions, sequences, types, and + aggregates, domains, foreign tables, functions, procedures, routines, sequences, types, and views can be schema-qualified. @@ -119,7 +123,7 @@ SECURITY LABEL [ FOR provider ] ON - The mode of a function or aggregate + The mode of a function, procedure, or aggregate argument: IN, OUT, INOUT, or VARIADIC. If omitted, the default is IN. @@ -137,7 +141,7 @@ SECURITY LABEL [ FOR provider ] ON - The name of a function or aggregate argument. + The name of a function, procedure, or aggregate argument. Note that SECURITY LABEL does not actually pay any attention to argument names, since only the argument data types are needed to determine the function's identity. @@ -150,7 +154,7 @@ SECURITY LABEL [ FOR provider ] ON - The data type of a function or aggregate argument. + The data type of a function, procedure, or aggregate argument. diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml index d20eaa87e76..d27fb414f7c 100644 --- a/doc/src/sgml/reference.sgml +++ b/doc/src/sgml/reference.sgml @@ -54,8 +54,10 @@ &alterOperatorClass; &alterOperatorFamily; &alterPolicy; + &alterProcedure; &alterPublication; &alterRole; + &alterRoutine; &alterRule; &alterSchema; &alterSequence; @@ -76,6 +78,7 @@ &alterView; &analyze; &begin; + &call; &checkpoint; &close; &cluster; @@ -103,6 +106,7 @@ &createOperatorClass; &createOperatorFamily; &createPolicy; + &createProcedure; &createPublication; &createRole; &createRule; @@ -150,8 +154,10 @@ &dropOperatorFamily; &dropOwned; &dropPolicy; + &dropProcedure; &dropPublication; &dropRole; + &dropRoutine; &dropRule; &dropSchema; &dropSequence; diff --git a/doc/src/sgml/xfunc.sgml b/doc/src/sgml/xfunc.sgml index 508ee7a96c0..bbc3766cc21 100644 --- a/doc/src/sgml/xfunc.sgml +++ b/doc/src/sgml/xfunc.sgml @@ -72,6 +72,39 @@ + + User-defined Procedures + + + procedure + user-defined + + + + A procedure is a database object similar to a function. The difference is + that a procedure does not return a value, so there is no return type + declaration. While a function is called as part of a query or DML + command, a procedure is called explicitly using + the statement. + + + + The explanations on how to define user-defined functions in the rest of + this chapter apply to procedures as well, except that + the command is used instead, there is + no return type, and some other features such as strictness don't apply. + + + + Collectively, functions and procedures are also known + as routinesroutine. + There are commands such as + and that can operate on functions and + procedures without having to know which kind it is. Note, however, that + there is no CREATE ROUTINE command. + + + Query Language (<acronym>SQL</acronym>) Functions -- cgit v1.2.3