CREATE PROCEDURE
CREATE PROCEDURE — Define a new procedure
Synopsis
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'
} ...
Introduction
CREATE PROCEDURE defines a new procedure. CREATE OR REPLACE PROCEDURE will create a new procedure or replace an existing definition. To define a procedure, the user must have USAGE privilege on the language being used.
If a schema name is included in the command, the procedure will be created in that schema. Otherwise, the procedure will be created in the current schema. The name of the new procedure cannot match any existing procedure or function in the same schema that has the same input parameter types. However, procedures and functions with different parameter types can share the same name (this is called overloading).
To replace the current definition of an existing procedure, use CREATE OR REPLACE PROCEDURE. You cannot change the name or parameter types of a procedure this way (attempting to do so will actually create a new, different procedure).
When CREATE OR REPLACE PROCEDURE is used to replace an existing procedure, the ownership and permissions of the procedure remain unchanged. All other procedure attributes are assigned the values specified or implied by the command. You must own (or be a member of the owning role of) the procedure to replace it.
The user who creates the procedure becomes its owner.
To create a procedure, the user must have USAGE privilege on the parameter types.
Parameters
name
The name of the procedure to be created (can be schema-qualified).
argmode
The mode of a parameter: IN, INOUT, or VARIADIC. If omitted, the default is IN (OUT parameters are currently not supported for procedures; use INOUT instead).
argname
The name of a parameter.
argtype
The data type(s) of the procedure's parameters, if any (can be schema-qualified). Parameter types can be base types, composite types, or domain types, or can reference the type of a table column.
Depending on the implementation language, it may also be possible to specify "pseudo-types" such as cstring. A pseudo-type indicates that the actual parameter type is either not fully specified or is outside the set of ordinary SQL data types. Writing table_name.column_name%TYPE references the type of a column. Using this feature can sometimes help create procedures that are unaffected by changes to table definitions.
default_expr
An expression to be used as the default value if a parameter is not specified. This expression must be coercible to the parameter's type. Input parameters following a parameter with a default value must also have default values.
lang_name
The name of the language used to implement the procedure. It can be sql, c, internal, or a user-defined procedural language such as plpgsql. Wrapping the name in single quotes is deprecated and requires case-sensitive matching.
TRANSFORM { FOR TYPE type_name } [, ... ] }
Lists the transforms that should be applied to calls of the procedure. Transforms convert between SQL types and language-specific data types; see CREATE TRANSFORM. Procedural language implementations typically hard-code knowledge of built-in types, so they do not need to be listed here. However, if a procedural language implementation does not know how to handle a type and no transform is provided, it will fall back to a default behavior for converting data types, but this is implementation-dependent.
[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. This is the default. SECURITY DEFINER specifies that the procedure is to be executed with the privileges of the user that owns it.
The keyword EXTERNAL is allowed for SQL conformance, but it is optional because, unlike in SQL, this feature applies to all procedures, not just external ones.
SECURITY DEFINER procedures cannot execute transaction control statements (such as COMMIT and ROLLBACK, depending on the implementation language).
configuration_parameter
value
The SET clause causes the specified configuration parameter to be set to the specified value when the procedure is entered, and restored to its previous value when the procedure exits. SET FROM CURRENT saves the current value of the parameter at the time CREATE PROCEDURE is executed as the value to be applied when entering the procedure.
If a SET clause is attached to a procedure, then the effect of a SET LOCAL command executed within the procedure for the same variable is limited to the procedure: the configuration parameter's previous value will still be restored when the procedure exits. However, a regular SET command (without LOCAL) overrides the SET clause, much as it would for a prior SET LOCAL command: the effect of such a command will persist after the procedure exits, unless the current transaction is rolled back.
If a SET clause is attached to a procedure, the procedure cannot execute transaction control statements (such as COMMIT and ROLLBACK, depending on the implementation language).
definition
A string constant defining the procedure; its meaning depends on the language. It can be an internal procedure name, a path to an object file, a SQL command, or text in a procedural language.
When writing the procedure's definition string, it is often helpful to use dollar quoting (see Section 4.1.2.4) rather than the ordinary 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 differs from the SQL procedure name. The string obj_file is the name of the shared library file containing the compiled C procedure, and it is resolved as per the LOAD 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 reference the same object file, the file is loaded only once per session. To unload and reload the file (perhaps during development), you should start a new session.
Notes
Considerations that apply to function creation also apply to procedures; see CREATE FUNCTION for more details.
Use CALL 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);