create_procedure(7)

NAME#

CREATE_PROCEDURE - define a new procedure

SYNOPSIS#

CREATE [ OR REPLACE ] PROCEDURE
\n
    \nname\n ( [ [ \nargmode\n ] [ \nargname\n ] \nargtype\n [ { DEFAULT | = } \ndefault_expr\n ] [, ...] ] )
\n
  { LANGUAGE \nlang_name\n
\n
    | TRANSFORM { FOR TYPE \ntype_name\n } [, ... ]
\n
    | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
\n
    | SET \nconfiguration_parameter\n { TO \nvalue\n | = \nvalue\n | FROM CURRENT }
\n
    | AS '\ndefinition\n'
\n
    | AS '\nobj_file\n', '\nlink_symbol\n'
\n
    | \nsql_body\n
\n
  } ...

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.

Refer to Section 36.4 for further information on writing procedures.

PARAMETERS#

name

argmode

argname

argtype

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

lang_name

TRANSFORM { FOR TYPE type_name } [, ... ] }

[EXTERNAL] SECURITY INVOKER [EXTERNAL] SECURITY DEFINER

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.

A SECURITY DEFINER procedure cannot execute transaction control statements (for example, COMMIT and ROLLBACK, depending on the language).

configuration_parameter value

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.

If a SET clause is attached to a procedure, then that procedure cannot execute transaction control statements (for example, COMMIT and ROLLBACK, depending on the language).

See SET(7) and Chapter 19 for more information about allowed parameter names and values.

definition

It is often helpful to use dollar quoting (see Section 4.1.2.4) 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

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.

sql_body

BEGIN ATOMIC
\n
  \nstatement\n;
\n
  \nstatement\n;
\n
  ...
\n
  \nstatement\n;
END

This is similar to writing the text of the procedure body as a string constant (see definition above), but there are some differences: This form only works for LANGUAGE SQL, the string constant form works for all languages. This form is parsed at procedure definition time, the string constant form is parsed at execution time; therefore this form cannot support polymorphic argument types and other constructs that are not resolvable at procedure definition time. This form tracks dependencies between the procedure and objects used in the procedure body, so DROP ... CASCADE will work correctly, whereas the form using string literals may leave dangling procedures. Finally, this form is more compatible with the SQL standard and other SQL implementations.

NOTES#

See CREATE FUNCTION (CREATE_FUNCTION(7)) for more details on function creation that also apply to procedures.

Use CALL(7) 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);
$$;

or

CREATE PROCEDURE insert_data(a integer, b integer)
LANGUAGE SQL
BEGIN ATOMIC
\n
  INSERT INTO tbl VALUES (a);
\n
  INSERT INTO tbl VALUES (b);
END;

and call like this:

CALL insert_data(1, 2);

COMPATIBILITY#

A CREATE PROCEDURE command is defined in the SQL standard. The PostgreSQL implementation can be used in a compatible way but has many extensions. For details see also CREATE FUNCTION (CREATE_FUNCTION(7)).

SEE ALSO#

ALTER PROCEDURE (ALTER_PROCEDURE(7)), DROP PROCEDURE (DROP_PROCEDURE(7)), CALL(7), CREATE FUNCTION (CREATE_FUNCTION(7))