summaryrefslogtreecommitdiffstats
path: root/doc/src/sgml/html/sql-alterprocedure.html
blob: 55f6d5cfb89dfdb6f68b2050ab075b8836b98147 (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
<?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 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 15.6 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 /></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 class="navfooter"><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 15.6 Documentation">Home</a></td><td width="40%" align="right" valign="top"> ALTER PUBLICATION</td></tr></table></div></body></html>