Debian Codegraph @

doc/src/sgml/ref/create_procedure.sgml

raw
1 <!--
2 doc/src/sgml/ref/create_procedure.sgml
3 PostgreSQL documentation
4 -->
5
6 <refentry id="sql-createprocedure">
7 <indexterm zone="sql-createprocedure">
8 <primary>CREATE PROCEDURE</primary>
9 </indexterm>
10
11 <refmeta>
12 <refentrytitle>CREATE PROCEDURE</refentrytitle>
13 <manvolnum>7</manvolnum>
14 <refmiscinfo>SQL - Language Statements</refmiscinfo>
15 </refmeta>
16
17 <refnamediv>
18 <refname>CREATE PROCEDURE</refname>
19 <refpurpose>define a new procedure</refpurpose>
20 </refnamediv>
21
22 <refsynopsisdiv>
23 <synopsis>
24 CREATE [ OR REPLACE ] PROCEDURE
25 <replaceable class="parameter">name</replaceable> ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">argtype</replaceable> [ { DEFAULT | = } <replaceable class="parameter">default_expr</replaceable> ] [, ...] ] )
26 { LANGUAGE <replaceable class="parameter">lang_name</replaceable>
27 | TRANSFORM { FOR TYPE <replaceable class="parameter">type_name</replaceable> } [, ... ]
28 | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
29 | SET <replaceable class="parameter">configuration_parameter</replaceable> { TO <replaceable class="parameter">value</replaceable> | = <replaceable class="parameter">value</replaceable> | FROM CURRENT }
30 | AS '<replaceable class="parameter">definition</replaceable>'
31 | AS '<replaceable class="parameter">obj_file</replaceable>', '<replaceable class="parameter">link_symbol</replaceable>'
32 | <replaceable class="parameter">sql_body</replaceable>
33 } ...
34 </synopsis>
35 </refsynopsisdiv>
36
37 <refsect1 id="sql-createprocedure-description">
38 <title>Description</title>
39
40 <para>
41 <command>CREATE PROCEDURE</command> defines a new procedure.
42 <command>CREATE OR REPLACE PROCEDURE</command> will either create a
43 new procedure, or replace an existing definition.
44 To be able to define a procedure, the user must have the
45 <literal>USAGE</literal> privilege on the language.
46 </para>
47
48 <para>
49 If a schema name is included, then the procedure is created in the
50 specified schema. Otherwise it is created in the current schema.
51 The name of the new procedure must not match any existing procedure or function
52 with the same input argument types in the same schema. However,
53 procedures and functions of different argument types can share a name (this is
54 called <firstterm>overloading</firstterm>).
55 </para>
56
57 <para>
58 To replace the current definition of an existing procedure, use
59 <command>CREATE OR REPLACE PROCEDURE</command>. It is not possible
60 to change the name or argument types of a procedure this way (if you
61 tried, you would actually be creating a new, distinct procedure).
62 </para>
63
64 <para>
65 When <command>CREATE OR REPLACE PROCEDURE</command> is used to replace an
66 existing procedure, the ownership and permissions of the procedure
67 do not change. All other procedure properties are assigned the
68 values specified or implied in the command. You must own the procedure
69 to replace it (this includes being a member of the owning role).
70 </para>
71
72 <para>
73 The user that creates the procedure becomes the owner of the procedure.
74 </para>
75
76 <para>
77 To be able to create a procedure, you must have <literal>USAGE</literal>
78 privilege on the argument types.
79 </para>
80
81 <para>
82 Refer to <xref linkend="xproc"/> for further information on writing
83 procedures.
84 </para>
85 </refsect1>
86
87 <refsect1>
88 <title>Parameters</title>
89
90 <variablelist>
91 <varlistentry>
92 <term><replaceable class="parameter">name</replaceable></term>
93
94 <listitem>
95 <para>
96 The name (optionally schema-qualified) of the procedure to create.
97 </para>
98 </listitem>
99 </varlistentry>
100
101 <varlistentry>
102 <term><replaceable class="parameter">argmode</replaceable></term>
103
104 <listitem>
105 <para>
106 The mode of an argument: <literal>IN</literal>, <literal>OUT</literal>,
107 <literal>INOUT</literal>, or <literal>VARIADIC</literal>. If omitted,
108 the default is <literal>IN</literal>.
109 </para>
110 </listitem>
111 </varlistentry>
112
113 <varlistentry>
114 <term><replaceable class="parameter">argname</replaceable></term>
115
116 <listitem>
117 <para>
118 The name of an argument.
119 </para>
120 </listitem>
121 </varlistentry>
122
123 <varlistentry>
124 <term><replaceable class="parameter">argtype</replaceable></term>
125
126 <listitem>
127 <para>
128 The data type(s) of the procedure's arguments (optionally
129 schema-qualified), if any. The argument types can be base, composite,
130 or domain types, or can reference the type of a table column.
131 </para>
132 <para>
133 Depending on the implementation language it might also be allowed
134 to specify <quote>pseudo-types</quote> such as <type>cstring</type>.
135 Pseudo-types indicate that the actual argument type is either
136 incompletely specified, or outside the set of ordinary SQL data types.
137 </para>
138 <para>
139 The type of a column is referenced by writing
140 <literal><replaceable
141 class="parameter">table_name</replaceable>.<replaceable
142 class="parameter">column_name</replaceable>%TYPE</literal>.
143 Using this feature can sometimes help make a procedure independent of
144 changes to the definition of a table.
145 </para>
146 </listitem>
147 </varlistentry>
148
149 <varlistentry>
150 <term><replaceable class="parameter">default_expr</replaceable></term>
151
152 <listitem>
153 <para>
154 An expression to be used as default value if the parameter is
155 not specified. The expression has to be coercible to the
156 argument type of the parameter.
157 All input parameters following a
158 parameter with a default value must have default values as well.
159 </para>
160 </listitem>
161 </varlistentry>
162
163 <varlistentry>
164 <term><replaceable class="parameter">lang_name</replaceable></term>
165
166 <listitem>
167 <para>
168 The name of the language that the procedure is implemented in.
169 It can be <literal>sql</literal>, <literal>c</literal>,
170 <literal>internal</literal>, or the name of a user-defined
171 procedural language, e.g., <literal>plpgsql</literal>. The default is
172 <literal>sql</literal> if <replaceable
173 class="parameter">sql_body</replaceable> is specified. Enclosing the
174 name in single quotes is deprecated and requires matching case.
175 </para>
176 </listitem>
177 </varlistentry>
178
179 <varlistentry>
180 <term><literal>TRANSFORM { FOR TYPE <replaceable class="parameter">type_name</replaceable> } [, ... ] }</literal></term>
181
182 <listitem>
183 <para>
184 Lists which transforms a call to the procedure should apply. Transforms
185 convert between SQL types and language-specific data types;
186 see <xref linkend="sql-createtransform"/>. Procedural language
187 implementations usually have hardcoded knowledge of the built-in types,
188 so those don't need to be listed here. If a procedural language
189 implementation does not know how to handle a type and no transform is
190 supplied, it will fall back to a default behavior for converting data
191 types, but this depends on the implementation.
192 </para>
193 </listitem>
194 </varlistentry>
195
196 <varlistentry>
197 <term><literal><optional>EXTERNAL</optional> SECURITY INVOKER</literal></term>
198 <term><literal><optional>EXTERNAL</optional> SECURITY DEFINER</literal></term>
199
200 <listitem>
201 <para><literal>SECURITY INVOKER</literal> indicates that the procedure
202 is to be executed with the privileges of the user that calls it.
203 That is the default. <literal>SECURITY DEFINER</literal>
204 specifies that the procedure is to be executed with the
205 privileges of the user that owns it.
206 </para>
207
208 <para>
209 The key word <literal>EXTERNAL</literal> is allowed for SQL
210 conformance, but it is optional since, unlike in SQL, this feature
211 applies to all procedures not only external ones.
212 </para>
213
214 <para>
215 A <literal>SECURITY DEFINER</literal> procedure cannot execute
216 transaction control statements (for example, <command>COMMIT</command>
217 and <command>ROLLBACK</command>, depending on the language).
218 </para>
219 </listitem>
220 </varlistentry>
221
222 <varlistentry>
223 <term><replaceable>configuration_parameter</replaceable></term>
224 <term><replaceable>value</replaceable></term>
225 <listitem>
226 <para>
227 The <literal>SET</literal> clause causes the specified configuration
228 parameter to be set to the specified value when the procedure is
229 entered, and then restored to its prior value when the procedure exits.
230 <literal>SET FROM CURRENT</literal> saves the value of the parameter that
231 is current when <command>CREATE PROCEDURE</command> is executed as the value
232 to be applied when the procedure is entered.
233 </para>
234
235 <para>
236 If a <literal>SET</literal> clause is attached to a procedure, then
237 the effects of a <command>SET LOCAL</command> command executed inside the
238 procedure for the same variable are restricted to the procedure: the
239 configuration parameter's prior value is still restored at procedure exit.
240 However, an ordinary
241 <command>SET</command> command (without <literal>LOCAL</literal>) overrides the
242 <literal>SET</literal> clause, much as it would do for a previous <command>SET
243 LOCAL</command> command: the effects of such a command will persist after
244 procedure exit, unless the current transaction is rolled back.
245 </para>
246
247 <para>
248 If a <literal>SET</literal> clause is attached to a procedure, then
249 that procedure cannot execute transaction control statements (for
250 example, <command>COMMIT</command> and <command>ROLLBACK</command>,
251 depending on the language).
252 </para>
253
254 <para>
255 See <xref linkend="sql-set"/> and
256 <xref linkend="runtime-config"/>
257 for more information about allowed parameter names and values.
258 </para>
259 </listitem>
260 </varlistentry>
261
262 <varlistentry>
263 <term><replaceable class="parameter">definition</replaceable></term>
264
265 <listitem>
266 <para>
267 A string constant defining the procedure; the meaning depends on the
268 language. It can be an internal procedure name, the path to an
269 object file, an SQL command, or text in a procedural language.
270 </para>
271
272 <para>
273 It is often helpful to use dollar quoting (see <xref
274 linkend="sql-syntax-dollar-quoting"/>) to write the procedure definition
275 string, rather than the normal single quote syntax. Without dollar
276 quoting, any single quotes or backslashes in the procedure definition must
277 be escaped by doubling them.
278 </para>
279
280 </listitem>
281 </varlistentry>
282
283 <varlistentry>
284 <term><literal><replaceable class="parameter">obj_file</replaceable>, <replaceable class="parameter">link_symbol</replaceable></literal></term>
285
286 <listitem>
287 <para>
288 This form of the <literal>AS</literal> clause is used for
289 dynamically loadable C language procedures when the procedure name
290 in the C language source code is not the same as the name of
291 the SQL procedure. The string <replaceable
292 class="parameter">obj_file</replaceable> is the name of the shared
293 library file containing the compiled C procedure, and is interpreted
294 as for the <link linkend="sql-load"><command>LOAD</command></link> command. The string
295 <replaceable class="parameter">link_symbol</replaceable> is the
296 procedure's link symbol, that is, the name of the procedure in the C
297 language source code. If the link symbol is omitted, it is assumed
298 to be the same as the name of the SQL procedure being defined.
299 </para>
300
301 <para>
302 When repeated <command>CREATE PROCEDURE</command> calls refer to
303 the same object file, the file is only loaded once per session.
304 To unload and
305 reload the file (perhaps during development), start a new session.
306 </para>
307
308 </listitem>
309 </varlistentry>
310
311 <varlistentry>
312 <term><replaceable class="parameter">sql_body</replaceable></term>
313
314 <listitem>
315 <para>
316 The body of a <literal>LANGUAGE SQL</literal> procedure. This should
317 be a block
318 <programlisting>
319 BEGIN ATOMIC
320 <replaceable>statement</replaceable>;
321 <replaceable>statement</replaceable>;
322 ...
323 <replaceable>statement</replaceable>;
324 END
325 </programlisting>
326 </para>
327
328 <para>
329 This is similar to writing the text of the procedure body as a string
330 constant (see <replaceable>definition</replaceable> above), but there
331 are some differences: This form only works for <literal>LANGUAGE
332 SQL</literal>, the string constant form works for all languages. This
333 form is parsed at procedure definition time, the string constant form is
334 parsed at execution time; therefore this form cannot support
335 polymorphic argument types and other constructs that are not resolvable
336 at procedure definition time. This form tracks dependencies between the
337 procedure and objects used in the procedure body, so <literal>DROP
338 ... CASCADE</literal> will work correctly, whereas the form using
339 string literals may leave dangling procedures. Finally, this form is
340 more compatible with the SQL standard and other SQL implementations.
341 </para>
342 </listitem>
343 </varlistentry>
344
345 </variablelist>
346 </refsect1>
347
348 <refsect1 id="sql-createprocedure-notes">
349 <title>Notes</title>
350
351 <para>
352 See <xref linkend="sql-createfunction"/> for more details on function
353 creation that also apply to procedures.
354 </para>
355
356 <para>
357 Use <xref linkend="sql-call"/> to execute a procedure.
358 </para>
359 </refsect1>
360
361 <refsect1 id="sql-createprocedure-examples">
362 <title>Examples</title>
363
364 <para>
365 <programlisting>
366 CREATE PROCEDURE insert_data(a integer, b integer)
367 LANGUAGE SQL
368 AS $$
369 INSERT INTO tbl VALUES (a);
370 INSERT INTO tbl VALUES (b);
371 $$;
372 </programlisting>
373 or
374 <programlisting>
375 CREATE PROCEDURE insert_data(a integer, b integer)
376 LANGUAGE SQL
377 BEGIN ATOMIC
378 INSERT INTO tbl VALUES (a);
379 INSERT INTO tbl VALUES (b);
380 END;
381 </programlisting>
382 and call like this:
383 <programlisting>
384 CALL insert_data(1, 2);
385 </programlisting></para>
386 </refsect1>
387
388 <refsect1 id="sql-createprocedure-compat">
389 <title>Compatibility</title>
390
391 <para>
392 A <command>CREATE PROCEDURE</command> command is defined in the SQL
393 standard. The <productname>PostgreSQL</productname> implementation can be
394 used in a compatible way but has many extensions. For details see also
395 <xref linkend="sql-createfunction"/>.
396 </para>
397 </refsect1>
398
399
400 <refsect1>
401 <title>See Also</title>
402
403 <simplelist type="inline">
404 <member><xref linkend="sql-alterprocedure"/></member>
405 <member><xref linkend="sql-dropprocedure"/></member>
406 <member><xref linkend="sql-call"/></member>
407 <member><xref linkend="sql-createfunction"/></member>
408 </simplelist>
409 </refsect1>
410
411 </refentry>