1CREATE PROCEDURE(7)      PostgreSQL 14.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           | sql_body
18         } ...
19

DESCRIPTION

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

PARAMETERS

52       name
53           The name (optionally schema-qualified) of the procedure to create.
54
55       argmode
56           The mode of an argument: IN, OUT, INOUT, or VARIADIC. If omitted,
57           the default is IN.
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. The default is sql if sql_body is
88           specified. Enclosing the name in single quotes is deprecated and
89           requires matching case.
90
91       TRANSFORM { FOR TYPE type_name } [, ... ] }
92           Lists which transforms a call to the procedure should apply.
93           Transforms convert between SQL types and language-specific data
94           types; see CREATE TRANSFORM (CREATE_TRANSFORM(7)). Procedural
95           language implementations usually have hardcoded knowledge of the
96           built-in types, so those don't need to be listed here. If a
97           procedural language implementation does not know how to handle a
98           type and no transform is supplied, it will fall back to a default
99           behavior for converting data types, but this depends on the
100           implementation.
101
102       [EXTERNAL] SECURITY INVOKER
103       [EXTERNAL] SECURITY DEFINER
104           SECURITY INVOKER indicates that the procedure is to be executed
105           with the privileges of the user that calls it. That is the default.
106           SECURITY DEFINER specifies that the procedure is to be executed
107           with the privileges of the user that owns it.
108
109           The key word EXTERNAL is allowed for SQL conformance, but it is
110           optional since, unlike in SQL, this feature applies to all
111           procedures not only external ones.
112
113           A SECURITY DEFINER procedure cannot execute transaction control
114           statements (for example, COMMIT and ROLLBACK, depending on the
115           language).
116
117       configuration_parameter
118       value
119           The SET clause causes the specified configuration parameter to be
120           set to the specified value when the procedure is entered, and then
121           restored to its prior value when the procedure exits.  SET FROM
122           CURRENT saves the value of the parameter that is current when
123           CREATE PROCEDURE is executed as the value to be applied when the
124           procedure is entered.
125
126           If a SET clause is attached to a procedure, then the effects of a
127           SET LOCAL command executed inside the procedure for the same
128           variable are restricted to the procedure: the configuration
129           parameter's prior value is still restored at procedure exit.
130           However, an ordinary SET command (without LOCAL) overrides the SET
131           clause, much as it would do for a previous SET LOCAL command: the
132           effects of such a command will persist after procedure exit, unless
133           the current transaction is rolled back.
134
135           If a SET clause is attached to a procedure, then that procedure
136           cannot execute transaction control statements (for example, COMMIT
137           and ROLLBACK, depending on the language).
138
139           See SET(7) and Chapter 20 for more information about allowed
140           parameter names and values.
141
142       definition
143           A string constant defining the procedure; the meaning depends on
144           the language. It can be an internal procedure name, the path to an
145           object file, an SQL command, or text in a procedural language.
146
147           It is often helpful to use dollar quoting (see Section 4.1.2.4) to
148           write the procedure definition string, rather than the normal
149           single quote syntax. Without dollar quoting, any single quotes or
150           backslashes in the procedure definition must be escaped by doubling
151           them.
152
153       obj_file, link_symbol
154           This form of the AS clause is used for dynamically loadable C
155           language procedures when the procedure name in the C language
156           source code is not the same as the name of the SQL procedure. The
157           string obj_file is the name of the shared library file containing
158           the compiled C procedure, and is interpreted as for the LOAD
159           command. The string link_symbol is the procedure's link symbol,
160           that is, the name of the procedure in the C language source code.
161           If the link symbol is omitted, it is assumed to be the same as the
162           name of the SQL procedure being defined.
163
164           When repeated CREATE PROCEDURE calls refer to the same object file,
165           the file is only loaded once per session. To unload and reload the
166           file (perhaps during development), start a new session.
167
168       sql_body
169           The body of a LANGUAGE SQL procedure. This should be a block
170
171               BEGIN ATOMIC
172                 statement;
173                 statement;
174                 ...
175                 statement;
176               END
177
178           This is similar to writing the text of the procedure body as a
179           string constant (see definition above), but there are some
180           differences: This form only works for LANGUAGE SQL, the string
181           constant form works for all languages. This form is parsed at
182           procedure definition time, the string constant form is parsed at
183           execution time; therefore this form cannot support polymorphic
184           argument types and other constructs that are not resolvable at
185           procedure definition time. This form tracks dependencies between
186           the procedure and objects used in the procedure body, so DROP ...
187           CASCADE will work correctly, whereas the form using string literals
188           may leave dangling procedures. Finally, this form is more
189           compatible with the SQL standard and other SQL implementations.
190

NOTES

192       See CREATE FUNCTION (CREATE_FUNCTION(7)) for more details on function
193       creation that also apply to procedures.
194
195       Use CALL(7) to execute a procedure.
196

EXAMPLES

198           CREATE PROCEDURE insert_data(a integer, b integer)
199           LANGUAGE SQL
200           AS $$
201           INSERT INTO tbl VALUES (a);
202           INSERT INTO tbl VALUES (b);
203           $$;
204
205       or
206
207           CREATE PROCEDURE insert_data(a integer, b integer)
208           LANGUAGE SQL
209           BEGIN ATOMIC
210             INSERT INTO tbl VALUES (a);
211             INSERT INTO tbl VALUES (b);
212           END;
213
214       and call like this:
215
216           CALL insert_data(1, 2);
217

COMPATIBILITY

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

SEE ALSO

224       ALTER PROCEDURE (ALTER_PROCEDURE(7)), DROP PROCEDURE
225       (DROP_PROCEDURE(7)), CALL(7), CREATE FUNCTION (CREATE_FUNCTION(7))
226
227
228
229PostgreSQL 14.3                      2022                  CREATE PROCEDURE(7)
Impressum