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