1CREATE PROCEDURE(7)      PostgreSQL 13.3 Documentation     CREATE PROCEDURE(7)
2
3
4

NAME

6       CREATE_PROCEDURE - define a new procedure
7

SYNOPSIS

9       CREATE [ OR REPLACE ] PROCEDURE
10           name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
11         { LANGUAGE lang_name
12           | TRANSFORM { FOR TYPE type_name } [, ... ]
13           | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
14           | SET configuration_parameter { TO value | = value | FROM CURRENT }
15           | AS 'definition'
16           | AS 'obj_file', 'link_symbol'
17         } ...
18

DESCRIPTION

20       CREATE PROCEDURE defines a new procedure.  CREATE OR REPLACE PROCEDURE
21       will either create a new procedure, or replace an existing definition.
22       To be able to define a procedure, the user must have the USAGE
23       privilege on the language.
24
25       If a schema name is included, then the procedure is created in the
26       specified schema. Otherwise it is created in the current schema. The
27       name of the new procedure must not match any existing procedure or
28       function with the same input argument types in the same schema.
29       However, procedures and functions of different argument types can share
30       a name (this is called overloading).
31
32       To replace the current definition of an existing procedure, use CREATE
33       OR REPLACE PROCEDURE. It is not possible to change the name or argument
34       types of a procedure this way (if you tried, you would actually be
35       creating a new, distinct procedure).
36
37       When CREATE OR REPLACE PROCEDURE is used to replace an existing
38       procedure, the ownership and permissions of the procedure do not
39       change. All other procedure properties are assigned the values
40       specified or implied in the command. You must own the procedure to
41       replace it (this includes being a member of the owning role).
42
43       The user that creates the procedure becomes the owner of the procedure.
44
45       To be able to create a procedure, you must have USAGE privilege on the
46       argument types.
47
48       Refer to Section 37.4 for further information on writing procedures.
49

PARAMETERS

51       name
52           The name (optionally schema-qualified) of the procedure to create.
53
54       argmode
55           The mode of an argument: IN, INOUT, or VARIADIC. If omitted, the
56           default is IN. (OUT arguments are currently not supported for
57           procedures. Use INOUT instead.)
58
59       argname
60           The name of an argument.
61
62       argtype
63           The data type(s) of the procedure's arguments (optionally
64           schema-qualified), if any. The argument types can be base,
65           composite, or domain types, or can reference the type of a table
66           column.
67
68           Depending on the implementation language it might also be allowed
69           to specify “pseudo-types” such as cstring. Pseudo-types indicate
70           that the actual argument type is either incompletely specified, or
71           outside the set of ordinary SQL data types.
72
73           The type of a column is referenced by writing
74           table_name.column_name%TYPE. Using this feature can sometimes help
75           make a procedure independent of changes to the definition of a
76           table.
77
78       default_expr
79           An expression to be used as default value if the parameter is not
80           specified. The expression has to be coercible to the argument type
81           of the parameter. All input parameters following a parameter with a
82           default value must have default values as well.
83
84       lang_name
85           The name of the language that the procedure is implemented in. It
86           can be sql, c, internal, or the name of a user-defined procedural
87           language, e.g., plpgsql. Enclosing the name in single quotes is
88           deprecated and requires matching case.
89
90       TRANSFORM { FOR TYPE type_name } [, ... ] }
91           Lists which transforms a call to the procedure should apply.
92           Transforms convert between SQL types and language-specific data
93           types; see CREATE TRANSFORM (CREATE_TRANSFORM(7)). Procedural
94           language implementations usually have hardcoded knowledge of the
95           built-in types, so those don't need to be listed here. If a
96           procedural language implementation does not know how to handle a
97           type and no transform is supplied, it will fall back to a default
98           behavior for converting data types, but this depends on the
99           implementation.
100
101       [EXTERNAL] SECURITY INVOKER
102       [EXTERNAL] SECURITY DEFINER
103           SECURITY INVOKER indicates that the procedure is to be executed
104           with the privileges of the user that calls it. That is the default.
105           SECURITY DEFINER specifies that the procedure is to be executed
106           with the privileges of the user that owns it.
107
108           The key word EXTERNAL is allowed for SQL conformance, but it is
109           optional since, unlike in SQL, this feature applies to all
110           procedures not only external ones.
111
112           A SECURITY DEFINER procedure cannot execute transaction control
113           statements (for example, COMMIT and ROLLBACK, depending on the
114           language).
115
116       configuration_parameter
117       value
118           The SET clause causes the specified configuration parameter to be
119           set to the specified value when the procedure is entered, and then
120           restored to its prior value when the procedure exits.  SET FROM
121           CURRENT saves the value of the parameter that is current when
122           CREATE PROCEDURE is executed as the value to be applied when the
123           procedure is entered.
124
125           If a SET clause is attached to a procedure, then the effects of a
126           SET LOCAL command executed inside the procedure for the same
127           variable are restricted to the procedure: the configuration
128           parameter's prior value is still restored at procedure exit.
129           However, an ordinary SET command (without LOCAL) overrides the SET
130           clause, much as it would do for a previous SET LOCAL command: the
131           effects of such a command will persist after procedure exit, unless
132           the current transaction is rolled back.
133
134           If a SET clause is attached to a procedure, then that procedure
135           cannot execute transaction control statements (for example, COMMIT
136           and ROLLBACK, depending on the language).
137
138           See SET(7) and Chapter 19 for more information about allowed
139           parameter names and values.
140
141       definition
142           A string constant defining the procedure; the meaning depends on
143           the language. It can be an internal procedure name, the path to an
144           object file, an SQL command, or text in a procedural language.
145
146           It is often helpful to use dollar quoting (see Section 4.1.2.4) to
147           write the procedure definition string, rather than the normal
148           single quote syntax. Without dollar quoting, any single quotes or
149           backslashes in the procedure definition must be escaped by doubling
150           them.
151
152       obj_file, link_symbol
153           This form of the AS clause is used for dynamically loadable C
154           language procedures when the procedure name in the C language
155           source code is not the same as the name of the SQL procedure. The
156           string obj_file is the name of the shared library file containing
157           the compiled C procedure, and is interpreted as for the LOAD(7)
158           command. The string link_symbol is the procedure's link symbol,
159           that is, the name of the procedure in the C language source code.
160           If the link symbol is omitted, it is assumed to be the same as the
161           name of the SQL procedure being defined.
162
163           When repeated CREATE PROCEDURE calls refer to the same object file,
164           the file is only loaded once per session. To unload and reload the
165           file (perhaps during development), start a new session.
166

NOTES

168       See CREATE FUNCTION (CREATE_FUNCTION(7)) for more details on function
169       creation that also apply to procedures.
170
171       Use CALL(7) to execute a procedure.
172

EXAMPLES

174           CREATE PROCEDURE insert_data(a integer, b integer)
175           LANGUAGE SQL
176           AS $$
177           INSERT INTO tbl VALUES (a);
178           INSERT INTO tbl VALUES (b);
179           $$;
180
181           CALL insert_data(1, 2);
182

COMPATIBILITY

184       A CREATE PROCEDURE command is defined in the SQL standard. The
185       PostgreSQL version is similar but not fully compatible. For details see
186       also CREATE FUNCTION (CREATE_FUNCTION(7)).
187

SEE ALSO

189       ALTER PROCEDURE (ALTER_PROCEDURE(7)), DROP PROCEDURE
190       (DROP_PROCEDURE(7)), CALL(7), CREATE FUNCTION (CREATE_FUNCTION(7))
191
192
193
194PostgreSQL 13.3                      2021                  CREATE PROCEDURE(7)
Impressum