summaryrefslogtreecommitdiffstats
path: root/doc/src/sgml/html/sql-createprocedure.html
blob: a1d2af629b44a0a54f41c8a3d14d399ad9eca12a (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>CREATE PROCEDURE</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="sql-createpolicy.html" title="CREATE POLICY" /><link rel="next" href="sql-createpublication.html" title="CREATE PUBLICATION" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">CREATE PROCEDURE</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-createpolicy.html" title="CREATE POLICY">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><th width="60%" align="center">SQL Commands</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 15.7 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-createpublication.html" title="CREATE PUBLICATION">Next</a></td></tr></table><hr /></div><div class="refentry" id="SQL-CREATEPROCEDURE"><div class="titlepage"></div><a id="id-1.9.3.76.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">CREATE PROCEDURE</span></h2><p>CREATE PROCEDURE — define a new procedure</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis">
CREATE [ OR REPLACE ] PROCEDURE
    <em class="replaceable"><code>name</code></em> ( [ [ <em class="replaceable"><code>argmode</code></em> ] [ <em class="replaceable"><code>argname</code></em> ] <em class="replaceable"><code>argtype</code></em> [ { DEFAULT | = } <em class="replaceable"><code>default_expr</code></em> ] [, ...] ] )
  { LANGUAGE <em class="replaceable"><code>lang_name</code></em>
    | TRANSFORM { FOR TYPE <em class="replaceable"><code>type_name</code></em> } [, ... ]
    | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
    | SET <em class="replaceable"><code>configuration_parameter</code></em> { TO <em class="replaceable"><code>value</code></em> | = <em class="replaceable"><code>value</code></em> | FROM CURRENT }
    | AS '<em class="replaceable"><code>definition</code></em>'
    | AS '<em class="replaceable"><code>obj_file</code></em>', '<em class="replaceable"><code>link_symbol</code></em>'
    | <em class="replaceable"><code>sql_body</code></em>
  } ...
</pre></div><div class="refsect1" id="SQL-CREATEPROCEDURE-DESCRIPTION"><h2>Description</h2><p>
   <code class="command">CREATE PROCEDURE</code> defines a new procedure.
   <code class="command">CREATE OR REPLACE PROCEDURE</code> will either create a
   new procedure, or replace an existing definition.
   To be able to define a procedure, the user must have the
   <code class="literal">USAGE</code> privilege on the language.
  </p><p>
   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 <em class="firstterm">overloading</em>).
  </p><p>
   To replace the current definition of an existing procedure, use
   <code class="command">CREATE OR REPLACE PROCEDURE</code>.  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).
  </p><p>
   When <code class="command">CREATE OR REPLACE PROCEDURE</code> 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).
  </p><p>
   The user that creates the procedure becomes the owner of the procedure.
  </p><p>
   To be able to create a procedure, you must have <code class="literal">USAGE</code>
   privilege on the argument types.
  </p><p>
   Refer to <a class="xref" href="xproc.html" title="38.4. User-Defined Procedures">Section 38.4</a> for further information on writing
   procedures.
  </p></div><div class="refsect1" id="id-1.9.3.76.6"><h2>Parameters</h2><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>name</code></em></span></dt><dd><p>
       The name (optionally schema-qualified) of the procedure to create.
      </p></dd><dt><span class="term"><em class="replaceable"><code>argmode</code></em></span></dt><dd><p>
       The mode of an argument: <code class="literal">IN</code>, <code class="literal">OUT</code>,
       <code class="literal">INOUT</code>, or <code class="literal">VARIADIC</code>.  If omitted,
       the default is <code class="literal">IN</code>.
      </p></dd><dt><span class="term"><em class="replaceable"><code>argname</code></em></span></dt><dd><p>
       The name of an argument.
      </p></dd><dt><span class="term"><em class="replaceable"><code>argtype</code></em></span></dt><dd><p>
       The data type(s) of the procedure's arguments (optionally
       schema-qualified), if any. The argument types can be base, composite,
       or domain types, or can reference the type of a table column.
      </p><p>
       Depending on the implementation language it might also be allowed
       to specify <span class="quote"><span class="quote">pseudo-types</span></span> such as <code class="type">cstring</code>.
       Pseudo-types indicate that the actual argument type is either
       incompletely specified, or outside the set of ordinary SQL data types.
      </p><p>
       The type of a column is referenced by writing
       <code class="literal"><em class="replaceable"><code>table_name</code></em>.<em class="replaceable"><code>column_name</code></em>%TYPE</code>.
       Using this feature can sometimes help make a procedure independent of
       changes to the definition of a table.
      </p></dd><dt><span class="term"><em class="replaceable"><code>default_expr</code></em></span></dt><dd><p>
       An expression to be used as default value if the parameter is
       not specified.  The expression has to be coercible to the
       argument type of the parameter.
       All input parameters following a
       parameter with a default value must have default values as well.
      </p></dd><dt><span class="term"><em class="replaceable"><code>lang_name</code></em></span></dt><dd><p>
       The name of the language that the procedure is implemented in.
       It can be <code class="literal">sql</code>, <code class="literal">c</code>,
       <code class="literal">internal</code>, or the name of a user-defined
       procedural language, e.g., <code class="literal">plpgsql</code>.  The default is
       <code class="literal">sql</code> if <em class="replaceable"><code>sql_body</code></em> is specified.  Enclosing the
       name in single quotes is deprecated and requires matching case.
      </p></dd><dt><span class="term"><code class="literal">TRANSFORM { FOR TYPE <em class="replaceable"><code>type_name</code></em> } [, ... ] }</code></span></dt><dd><p>
       Lists which transforms a call to the procedure should apply.  Transforms
       convert between SQL types and language-specific data types;
       see <a class="xref" href="sql-createtransform.html" title="CREATE TRANSFORM"><span class="refentrytitle">CREATE TRANSFORM</span></a>.  Procedural language
       implementations usually have hardcoded knowledge of the built-in types,
       so those don't need to be listed here.  If a procedural language
       implementation does not know how to handle a type and no transform is
       supplied, it will fall back to a default behavior for converting data
       types, but this depends on the implementation.
      </p></dd><dt><span class="term"><code class="literal">[<span class="optional">EXTERNAL</span>] SECURITY INVOKER</code><br /></span><span class="term"><code class="literal">[<span class="optional">EXTERNAL</span>] SECURITY DEFINER</code></span></dt><dd><p><code class="literal">SECURITY INVOKER</code> indicates that the procedure
      is to be executed with the privileges of the user that calls it.
      That is the default.  <code class="literal">SECURITY DEFINER</code>
      specifies that the procedure is to be executed with the
      privileges of the user that owns it.
     </p><p>
      The key word <code class="literal">EXTERNAL</code> is allowed for SQL
      conformance, but it is optional since, unlike in SQL, this feature
      applies to all procedures not only external ones.
     </p><p>
      A <code class="literal">SECURITY DEFINER</code> procedure cannot execute
      transaction control statements (for example, <code class="command">COMMIT</code>
      and <code class="command">ROLLBACK</code>, depending on the language).
     </p></dd><dt><span class="term"><em class="replaceable"><code>configuration_parameter</code></em><br /></span><span class="term"><em class="replaceable"><code>value</code></em></span></dt><dd><p>
       The <code class="literal">SET</code> clause causes the specified configuration
       parameter to be set to the specified value when the procedure is
       entered, and then restored to its prior value when the procedure exits.
       <code class="literal">SET FROM CURRENT</code> saves the value of the parameter that
       is current when <code class="command">CREATE PROCEDURE</code> is executed as the value
       to be applied when the procedure is entered.
      </p><p>
       If a <code class="literal">SET</code> clause is attached to a procedure, then
       the effects of a <code class="command">SET LOCAL</code> 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
       <code class="command">SET</code> command (without <code class="literal">LOCAL</code>) overrides the
       <code class="literal">SET</code> clause, much as it would do for a previous <code class="command">SET
       LOCAL</code> command: the effects of such a command will persist after
       procedure exit, unless the current transaction is rolled back.
      </p><p>
       If a <code class="literal">SET</code> clause is attached to a procedure, then
       that procedure cannot execute transaction control statements (for
       example, <code class="command">COMMIT</code> and <code class="command">ROLLBACK</code>,
       depending on the language).
      </p><p>
       See <a class="xref" href="sql-set.html" title="SET"><span class="refentrytitle">SET</span></a> and
       <a class="xref" href="runtime-config.html" title="Chapter 20. Server Configuration">Chapter 20</a>
       for more information about allowed parameter names and values.
      </p></dd><dt><span class="term"><em class="replaceable"><code>definition</code></em></span></dt><dd><p>
       A string constant defining the procedure; the meaning depends on the
       language.  It can be an internal procedure name, the path to an
       object file, an SQL command, or text in a procedural language.
      </p><p>
       It is often helpful to use dollar quoting (see <a class="xref" href="sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING" title="4.1.2.4. Dollar-Quoted String Constants">Section 4.1.2.4</a>) 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.
      </p></dd><dt><span class="term"><code class="literal"><em class="replaceable"><code>obj_file</code></em>, <em class="replaceable"><code>link_symbol</code></em></code></span></dt><dd><p>
       This form of the <code class="literal">AS</code> clause is used for
       dynamically loadable C language procedures when the procedure name
       in the C language source code is not the same as the name of
       the SQL procedure. The string <em class="replaceable"><code>obj_file</code></em> is the name of the shared
       library file containing the compiled C procedure, and is interpreted
       as for the <a class="link" href="sql-load.html" title="LOAD"><code class="command">LOAD</code></a> command.  The string
       <em class="replaceable"><code>link_symbol</code></em> is the
       procedure's link symbol, that is, the name of the procedure in the C
       language source code.  If the link symbol is omitted, it is assumed
       to be the same as the name of the SQL procedure being defined.
      </p><p>
       When repeated <code class="command">CREATE PROCEDURE</code> 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.
      </p></dd><dt><span class="term"><em class="replaceable"><code>sql_body</code></em></span></dt><dd><p>
       The body of a <code class="literal">LANGUAGE SQL</code> procedure.  This should
       be a block
</p><pre class="programlisting">
BEGIN ATOMIC
  <em class="replaceable"><code>statement</code></em>;
  <em class="replaceable"><code>statement</code></em>;
  ...
  <em class="replaceable"><code>statement</code></em>;
END
</pre><p>
      </p><p>
       This is similar to writing the text of the procedure body as a string
       constant (see <em class="replaceable"><code>definition</code></em> above), but there
       are some differences: This form only works for <code class="literal">LANGUAGE
       SQL</code>, 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 <code class="literal">DROP
       ... CASCADE</code> 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.
      </p></dd></dl></div></div><div class="refsect1" id="SQL-CREATEPROCEDURE-NOTES"><h2>Notes</h2><p>
   See <a class="xref" href="sql-createfunction.html" title="CREATE FUNCTION"><span class="refentrytitle">CREATE FUNCTION</span></a> for more details on function
   creation that also apply to procedures.
  </p><p>
   Use <a class="xref" href="sql-call.html" title="CALL"><span class="refentrytitle">CALL</span></a> to execute a procedure.
  </p></div><div class="refsect1" id="SQL-CREATEPROCEDURE-EXAMPLES"><h2>Examples</h2><p>
</p><pre class="programlisting">
CREATE PROCEDURE insert_data(a integer, b integer)
LANGUAGE SQL
AS $$
INSERT INTO tbl VALUES (a);
INSERT INTO tbl VALUES (b);
$$;
</pre><p>
   or
</p><pre class="programlisting">
CREATE PROCEDURE insert_data(a integer, b integer)
LANGUAGE SQL
BEGIN ATOMIC
  INSERT INTO tbl VALUES (a);
  INSERT INTO tbl VALUES (b);
END;
</pre><p>
   and call like this:
</p><pre class="programlisting">
CALL insert_data(1, 2);
</pre></div><div class="refsect1" id="SQL-CREATEPROCEDURE-COMPAT"><h2>Compatibility</h2><p>
   A <code class="command">CREATE PROCEDURE</code> command is defined in the SQL
   standard.  The <span class="productname">PostgreSQL</span> implementation can be
   used in a compatible way but has many extensions.  For details see also
   <a class="xref" href="sql-createfunction.html" title="CREATE FUNCTION"><span class="refentrytitle">CREATE FUNCTION</span></a>.
  </p></div><div class="refsect1" id="id-1.9.3.76.10"><h2>See Also</h2><span class="simplelist"><a class="xref" href="sql-alterprocedure.html" title="ALTER PROCEDURE"><span class="refentrytitle">ALTER PROCEDURE</span></a>, <a class="xref" href="sql-dropprocedure.html" title="DROP PROCEDURE"><span class="refentrytitle">DROP PROCEDURE</span></a>, <a class="xref" href="sql-call.html" title="CALL"><span class="refentrytitle">CALL</span></a>, <a class="xref" href="sql-createfunction.html" title="CREATE FUNCTION"><span class="refentrytitle">CREATE FUNCTION</span></a></span></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sql-createpolicy.html" title="CREATE POLICY">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-createpublication.html" title="CREATE PUBLICATION">Next</a></td></tr><tr><td width="40%" align="left" valign="top">CREATE POLICY </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 15.7 Documentation">Home</a></td><td width="40%" align="right" valign="top"> CREATE PUBLICATION</td></tr></table></div></body></html>