Debian Codegraph @

doc/src/sgml/ref/alter_procedure.sgml

raw
1 <!--
2 doc/src/sgml/ref/alter_procedure.sgml
3 PostgreSQL documentation
4 -->
5
6 <refentry id="sql-alterprocedure">
7 <indexterm zone="sql-alterprocedure">
8 <primary>ALTER PROCEDURE</primary>
9 </indexterm>
10
11 <refmeta>
12 <refentrytitle>ALTER PROCEDURE</refentrytitle>
13 <manvolnum>7</manvolnum>
14 <refmiscinfo>SQL - Language Statements</refmiscinfo>
15 </refmeta>
16
17 <refnamediv>
18 <refname>ALTER PROCEDURE</refname>
19 <refpurpose>change the definition of a procedure</refpurpose>
20 </refnamediv>
21
22 <refsynopsisdiv>
23 <synopsis>
24 ALTER PROCEDURE <replaceable>name</replaceable> [ ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">argtype</replaceable> [, ...] ] ) ]
25 <replaceable class="parameter">action</replaceable> [ ... ] [ RESTRICT ]
26 ALTER PROCEDURE <replaceable>name</replaceable> [ ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">argtype</replaceable> [, ...] ] ) ]
27 RENAME TO <replaceable>new_name</replaceable>
28 ALTER PROCEDURE <replaceable>name</replaceable> [ ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">argtype</replaceable> [, ...] ] ) ]
29 OWNER TO { <replaceable>new_owner</replaceable> | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
30 ALTER PROCEDURE <replaceable>name</replaceable> [ ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">argtype</replaceable> [, ...] ] ) ]
31 SET SCHEMA <replaceable>new_schema</replaceable>
32 ALTER PROCEDURE <replaceable>name</replaceable> [ ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">argtype</replaceable> [, ...] ] ) ]
33 [ NO ] DEPENDS ON EXTENSION <replaceable>extension_name</replaceable>
34
35 <phrase>where <replaceable class="parameter">action</replaceable> is one of:</phrase>
36
37 [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
38 SET <replaceable class="parameter">configuration_parameter</replaceable> { TO | = } { <replaceable class="parameter">value</replaceable> | DEFAULT }
39 SET <replaceable class="parameter">configuration_parameter</replaceable> FROM CURRENT
40 RESET <replaceable class="parameter">configuration_parameter</replaceable>
41 RESET ALL
42 </synopsis>
43 </refsynopsisdiv>
44
45 <refsect1>
46 <title>Description</title>
47
48 <para>
49 <command>ALTER PROCEDURE</command> changes the definition of a
50 procedure.
51 </para>
52
53 <para>
54 You must own the procedure to use <command>ALTER PROCEDURE</command>.
55 To change a procedure's schema, you must also have <literal>CREATE</literal>
56 privilege on the new schema.
57 To alter the owner, you must be able to <literal>SET ROLE</literal> to the
58 new owning role, and that role must have <literal>CREATE</literal>
59 privilege on the procedure's schema.
60 (These restrictions enforce that altering the owner
61 doesn't do anything you couldn't do by dropping and recreating the procedure.
62 However, a superuser can alter ownership of any procedure anyway.)
63 </para>
64 </refsect1>
65
66 <refsect1>
67 <title>Parameters</title>
68
69 <variablelist>
70 <varlistentry>
71 <term><replaceable class="parameter">name</replaceable></term>
72 <listitem>
73 <para>
74 The name (optionally schema-qualified) of an existing procedure. If no
75 argument list is specified, the name must be unique in its schema.
76 </para>
77 </listitem>
78 </varlistentry>
79
80 <varlistentry>
81 <term><replaceable class="parameter">argmode</replaceable></term>
82
83 <listitem>
84 <para>
85 The mode of an argument: <literal>IN</literal>, <literal>OUT</literal>,
86 <literal>INOUT</literal>, or <literal>VARIADIC</literal>. If omitted,
87 the default is <literal>IN</literal>.
88 </para>
89 </listitem>
90 </varlistentry>
91
92 <varlistentry>
93 <term><replaceable class="parameter">argname</replaceable></term>
94
95 <listitem>
96 <para>
97 The name of an argument.
98 Note that <command>ALTER PROCEDURE</command> does not actually pay
99 any attention to argument names, since only the argument data
100 types are used to determine the procedure's identity.
101 </para>
102 </listitem>
103 </varlistentry>
104
105 <varlistentry>
106 <term><replaceable class="parameter">argtype</replaceable></term>
107
108 <listitem>
109 <para>
110 The data type(s) of the procedure's arguments (optionally
111 schema-qualified), if any.
112 See <xref linkend="sql-dropprocedure"/> for the details of how
113 the procedure is looked up using the argument data type(s).
114 </para>
115 </listitem>
116 </varlistentry>
117
118 <varlistentry>
119 <term><replaceable class="parameter">new_name</replaceable></term>
120 <listitem>
121 <para>
122 The new name of the procedure.
123 </para>
124 </listitem>
125 </varlistentry>
126
127 <varlistentry>
128 <term><replaceable class="parameter">new_owner</replaceable></term>
129 <listitem>
130 <para>
131 The new owner of the procedure. Note that if the procedure is
132 marked <literal>SECURITY DEFINER</literal>, it will subsequently
133 execute as the new owner.
134 </para>
135 </listitem>
136 </varlistentry>
137
138 <varlistentry>
139 <term><replaceable class="parameter">new_schema</replaceable></term>
140 <listitem>
141 <para>
142 The new schema for the procedure.
143 </para>
144 </listitem>
145 </varlistentry>
146
147 <varlistentry>
148 <term><replaceable class="parameter">extension_name</replaceable></term>
149 <listitem>
150 <para>
151 This form marks the procedure as dependent on the extension, or no longer
152 dependent on the extension if <literal>NO</literal> is specified.
153 A procedure that's marked as dependent on an extension is dropped when the
154 extension is dropped, even if cascade is not specified.
155 A procedure can depend upon multiple extensions, and will be dropped when
156 any one of those extensions is dropped.
157 </para>
158 </listitem>
159 </varlistentry>
160
161 <varlistentry>
162 <term><literal><optional> EXTERNAL </optional> SECURITY INVOKER</literal></term>
163 <term><literal><optional> EXTERNAL </optional> SECURITY DEFINER</literal></term>
164
165 <listitem>
166 <para>
167 Change whether the procedure is a security definer or not. The
168 key word <literal>EXTERNAL</literal> is ignored for SQL
169 conformance. See <xref linkend="sql-createprocedure"/> for more information about
170 this capability.
171 </para>
172 </listitem>
173 </varlistentry>
174
175 <varlistentry>
176 <term><replaceable>configuration_parameter</replaceable></term>
177 <term><replaceable>value</replaceable></term>
178 <listitem>
179 <para>
180 Add or change the assignment to be made to a configuration parameter
181 when the procedure is called. If
182 <replaceable>value</replaceable> is <literal>DEFAULT</literal>
183 or, equivalently, <literal>RESET</literal> is used, the procedure-local
184 setting is removed, so that the procedure executes with the value
185 present in its environment. Use <literal>RESET
186 ALL</literal> to clear all procedure-local settings.
187 <literal>SET FROM CURRENT</literal> saves the value of the parameter that
188 is current when <command>ALTER PROCEDURE</command> is executed as the value
189 to be applied when the procedure is entered.
190 </para>
191
192 <para>
193 See <xref linkend="sql-set"/> and
194 <xref linkend="runtime-config"/>
195 for more information about allowed parameter names and values.
196 </para>
197 </listitem>
198 </varlistentry>
199
200 <varlistentry>
201 <term><literal>RESTRICT</literal></term>
202
203 <listitem>
204 <para>
205 Ignored for conformance with the SQL standard.
206 </para>
207 </listitem>
208 </varlistentry>
209 </variablelist>
210 </refsect1>
211
212 <refsect1>
213 <title>Examples</title>
214
215 <para>
216 To rename the procedure <literal>insert_data</literal> with two arguments
217 of type <type>integer</type> to <literal>insert_record</literal>:
218 <programlisting>
219 ALTER PROCEDURE insert_data(integer, integer) RENAME TO insert_record;
220 </programlisting>
221 </para>
222
223 <para>
224 To change the owner of the procedure <literal>insert_data</literal> with
225 two arguments of type <type>integer</type> to <literal>joe</literal>:
226 <programlisting>
227 ALTER PROCEDURE insert_data(integer, integer) OWNER TO joe;
228 </programlisting>
229 </para>
230
231 <para>
232 To change the schema of the procedure <literal>insert_data</literal> with
233 two arguments of type <type>integer</type>
234 to <literal>accounting</literal>:
235 <programlisting>
236 ALTER PROCEDURE insert_data(integer, integer) SET SCHEMA accounting;
237 </programlisting>
238 </para>
239
240 <para>
241 To mark the procedure <literal>insert_data(integer, integer)</literal> as
242 being dependent on the extension <literal>myext</literal>:
243 <programlisting>
244 ALTER PROCEDURE insert_data(integer, integer) DEPENDS ON EXTENSION myext;
245 </programlisting>
246 </para>
247
248 <para>
249 To adjust the search path that is automatically set for a procedure:
250 <programlisting>
251 ALTER PROCEDURE check_password(text) SET search_path = admin, pg_temp;
252 </programlisting>
253 </para>
254
255 <para>
256 To disable automatic setting of <varname>search_path</varname> for a procedure:
257 <programlisting>
258 ALTER PROCEDURE check_password(text) RESET search_path;
259 </programlisting>
260 The procedure will now execute with whatever search path is used by its
261 caller.
262 </para>
263 </refsect1>
264
265 <refsect1>
266 <title>Compatibility</title>
267
268 <para>
269 This statement is partially compatible with the <command>ALTER
270 PROCEDURE</command> statement in the SQL standard. The standard allows more
271 properties of a procedure to be modified, but does not provide the
272 ability to rename a procedure, make a procedure a security definer,
273 attach configuration parameter values to a procedure,
274 or change the owner, schema, or volatility of a procedure. The standard also
275 requires the <literal>RESTRICT</literal> key word, which is optional in
276 <productname>PostgreSQL</productname>.
277 </para>
278 </refsect1>
279
280 <refsect1>
281 <title>See Also</title>
282
283 <simplelist type="inline">
284 <member><xref linkend="sql-createprocedure"/></member>
285 <member><xref linkend="sql-dropprocedure"/></member>
286 <member><xref linkend="sql-alterfunction"/></member>
287 <member><xref linkend="sql-alterroutine"/></member>
288 </simplelist>
289 </refsect1>
290 </refentry>