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
|
<?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>ALTER 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-alterpolicy.html" title="ALTER POLICY" /><link rel="next" href="sql-alterpublication.html" title="ALTER PUBLICATION" /></head><body id="docContent" class="container-fluid col-10"><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">ALTER PROCEDURE</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-alterpolicy.html" title="ALTER 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 14.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-alterpublication.html" title="ALTER PUBLICATION">Next</a></td></tr></table><hr></hr></div><div class="refentry" id="SQL-ALTERPROCEDURE"><div class="titlepage"></div><a id="id-1.9.3.24.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">ALTER PROCEDURE</span></h2><p>ALTER PROCEDURE — change the definition of a procedure</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis">
ALTER 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> [, ...] ] ) ]
<em class="replaceable"><code>action</code></em> [ ... ] [ RESTRICT ]
ALTER 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> [, ...] ] ) ]
RENAME TO <em class="replaceable"><code>new_name</code></em>
ALTER 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> [, ...] ] ) ]
OWNER TO { <em class="replaceable"><code>new_owner</code></em> | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER 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> [, ...] ] ) ]
SET SCHEMA <em class="replaceable"><code>new_schema</code></em>
ALTER 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> [, ...] ] ) ]
[ NO ] DEPENDS ON EXTENSION <em class="replaceable"><code>extension_name</code></em>
<span class="phrase">where <em class="replaceable"><code>action</code></em> is one of:</span>
[ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
SET <em class="replaceable"><code>configuration_parameter</code></em> { TO | = } { <em class="replaceable"><code>value</code></em> | DEFAULT }
SET <em class="replaceable"><code>configuration_parameter</code></em> FROM CURRENT
RESET <em class="replaceable"><code>configuration_parameter</code></em>
RESET ALL
</pre></div><div class="refsect1" id="id-1.9.3.24.5"><h2>Description</h2><p>
<code class="command">ALTER PROCEDURE</code> changes the definition of a
procedure.
</p><p>
You must own the procedure to use <code class="command">ALTER PROCEDURE</code>.
To change a procedure's schema, you must also have <code class="literal">CREATE</code>
privilege on the new schema.
To alter the owner, you must also be a direct or indirect member of the new
owning role, and that role must have <code class="literal">CREATE</code> privilege on
the procedure's schema. (These restrictions enforce that altering the owner
doesn't do anything you couldn't do by dropping and recreating the procedure.
However, a superuser can alter ownership of any procedure anyway.)
</p></div><div class="refsect1" id="id-1.9.3.24.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 an existing procedure. If no
argument list is specified, the name must be unique in its schema.
</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.
Note that <code class="command">ALTER PROCEDURE</code> does not actually pay
any attention to argument names, since only the argument data
types are used to determine the procedure's identity.
</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.
See <a class="xref" href="sql-dropprocedure.html" title="DROP PROCEDURE"><span class="refentrytitle">DROP PROCEDURE</span></a> for the details of how
the procedure is looked up using the argument data type(s).
</p></dd><dt><span class="term"><em class="replaceable"><code>new_name</code></em></span></dt><dd><p>
The new name of the procedure.
</p></dd><dt><span class="term"><em class="replaceable"><code>new_owner</code></em></span></dt><dd><p>
The new owner of the procedure. Note that if the procedure is
marked <code class="literal">SECURITY DEFINER</code>, it will subsequently
execute as the new owner.
</p></dd><dt><span class="term"><em class="replaceable"><code>new_schema</code></em></span></dt><dd><p>
The new schema for the procedure.
</p></dd><dt><span class="term"><em class="replaceable"><code>extension_name</code></em></span></dt><dd><p>
This form marks the procedure as dependent on the extension, or no longer
dependent on the extension if <code class="literal">NO</code> is specified.
A procedure that's marked as dependent on an extension is dropped when the
extension is dropped, even if cascade is not specified.
A procedure can depend upon multiple extensions, and will be dropped when
any one of those extensions is dropped.
</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>
Change whether the procedure is a security definer or not. The
key word <code class="literal">EXTERNAL</code> is ignored for SQL
conformance. See <a class="xref" href="sql-createprocedure.html" title="CREATE PROCEDURE"><span class="refentrytitle">CREATE PROCEDURE</span></a> for more information about
this capability.
</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>
Add or change the assignment to be made to a configuration parameter
when the procedure is called. If
<em class="replaceable"><code>value</code></em> is <code class="literal">DEFAULT</code>
or, equivalently, <code class="literal">RESET</code> is used, the procedure-local
setting is removed, so that the procedure executes with the value
present in its environment. Use <code class="literal">RESET
ALL</code> to clear all procedure-local settings.
<code class="literal">SET FROM CURRENT</code> saves the value of the parameter that
is current when <code class="command">ALTER PROCEDURE</code> is executed as the value
to be applied when the procedure is entered.
</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"><code class="literal">RESTRICT</code></span></dt><dd><p>
Ignored for conformance with the SQL standard.
</p></dd></dl></div></div><div class="refsect1" id="id-1.9.3.24.7"><h2>Examples</h2><p>
To rename the procedure <code class="literal">insert_data</code> with two arguments
of type <code class="type">integer</code> to <code class="literal">insert_record</code>:
</p><pre class="programlisting">
ALTER PROCEDURE insert_data(integer, integer) RENAME TO insert_record;
</pre><p>
</p><p>
To change the owner of the procedure <code class="literal">insert_data</code> with
two arguments of type <code class="type">integer</code> to <code class="literal">joe</code>:
</p><pre class="programlisting">
ALTER PROCEDURE insert_data(integer, integer) OWNER TO joe;
</pre><p>
</p><p>
To change the schema of the procedure <code class="literal">insert_data</code> with
two arguments of type <code class="type">integer</code>
to <code class="literal">accounting</code>:
</p><pre class="programlisting">
ALTER PROCEDURE insert_data(integer, integer) SET SCHEMA accounting;
</pre><p>
</p><p>
To mark the procedure <code class="literal">insert_data(integer, integer)</code> as
being dependent on the extension <code class="literal">myext</code>:
</p><pre class="programlisting">
ALTER PROCEDURE insert_data(integer, integer) DEPENDS ON EXTENSION myext;
</pre><p>
</p><p>
To adjust the search path that is automatically set for a procedure:
</p><pre class="programlisting">
ALTER PROCEDURE check_password(text) SET search_path = admin, pg_temp;
</pre><p>
</p><p>
To disable automatic setting of <code class="varname">search_path</code> for a procedure:
</p><pre class="programlisting">
ALTER PROCEDURE check_password(text) RESET search_path;
</pre><p>
The procedure will now execute with whatever search path is used by its
caller.
</p></div><div class="refsect1" id="id-1.9.3.24.8"><h2>Compatibility</h2><p>
This statement is partially compatible with the <code class="command">ALTER
PROCEDURE</code> statement in the SQL standard. The standard allows more
properties of a procedure to be modified, but does not provide the
ability to rename a procedure, make a procedure a security definer,
attach configuration parameter values to a procedure,
or change the owner, schema, or volatility of a procedure. The standard also
requires the <code class="literal">RESTRICT</code> key word, which is optional in
<span class="productname">PostgreSQL</span>.
</p></div><div class="refsect1" id="id-1.9.3.24.9"><h2>See Also</h2><span class="simplelist"><a class="xref" href="sql-createprocedure.html" title="CREATE PROCEDURE"><span class="refentrytitle">CREATE 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-alterfunction.html" title="ALTER FUNCTION"><span class="refentrytitle">ALTER FUNCTION</span></a>, <a class="xref" href="sql-alterroutine.html" title="ALTER ROUTINE"><span class="refentrytitle">ALTER ROUTINE</span></a></span></div></div><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navfooter"><hr></hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sql-alterpolicy.html" title="ALTER 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-alterpublication.html" title="ALTER PUBLICATION">Next</a></td></tr><tr><td width="40%" align="left" valign="top">ALTER POLICY </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 14.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> ALTER PUBLICATION</td></tr></table></div></body></html>
|
