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
|
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>ALTER FUNCTION</title>
<link rel="stylesheet" href="stylesheet.css" type="text/css">
<link rev="made" href="pgsql-docs@postgresql.org">
<meta name="generator" content="DocBook XSL Stylesheets V1.70.0">
<link rel="start" href="index.html" title="PostgreSQL 8.1.4 Documentation">
<link rel="up" href="sql-commands.html" title="SQL Commands">
<link rel="prev" href="sql-alterdomain.html" title="ALTER DOMAIN">
<link rel="next" href="sql-altergroup.html" title="ALTER GROUP">
<link rel="copyright" href="ln-legalnotice.html" title="Legal Notice">
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="refentry" lang="en">
<a name="sql-alterfunction"></a><div class="titlepage"></div>
<div class="refnamediv">
<h2>Name</h2>
<p>ALTER FUNCTION — change the definition of a function</p>
</div>
<a name="id741990"></a><div class="refsynopsisdiv">
<h2>Synopsis</h2>
<pre class="synopsis">ALTER FUNCTION <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 FUNCTION <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 FUNCTION <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>
ALTER FUNCTION <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>
where <em class="replaceable"><code>action</code></em> is one of:
CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
IMMUTABLE | STABLE | VOLATILE
[ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER</pre>
</div>
<div class="refsect1" lang="en">
<a name="id742135"></a><h2>Description</h2>
<p> <code class="command">ALTER FUNCTION</code> changes the definition of a
function.
</p>
<p> You must own the function to use <code class="command">ALTER FUNCTION</code>.
To change a function'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 function's schema. (These restrictions enforce that altering the owner
doesn't do anything you couldn't do by dropping and recreating the function.
However, a superuser can alter ownership of any function anyway.)
</p>
</div>
<div class="refsect1" lang="en">
<a name="id742179"></a><h2>Parameters</h2>
<div class="variablelist"><dl>
<dt><span class="term"><em class="replaceable"><code>name</code></em></span></dt>
<dd><p> The name (optionally schema-qualified) of an existing function.
</p></dd>
<dt><span class="term"><em class="replaceable"><code>argmode</code></em></span></dt>
<dd><p> The mode of an argument: either <code class="literal">IN</code>, <code class="literal">OUT</code>,
or <code class="literal">INOUT</code>. If omitted, the default is <code class="literal">IN</code>.
Note that <code class="command">ALTER FUNCTION</code> does not actually pay
any attention to <code class="literal">OUT</code> arguments, since only the input
arguments are needed to determine the function's identity.
So it is sufficient to list the <code class="literal">IN</code> and <code class="literal">INOUT</code>
arguments.
</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 FUNCTION</code> does not actually pay
any attention to argument names, since only the argument data
types are needed to determine the function'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 function's arguments (optionally
schema-qualified), if any.
</p></dd>
<dt><span class="term"><em class="replaceable"><code>new_name</code></em></span></dt>
<dd><p> The new name of the function.
</p></dd>
<dt><span class="term"><em class="replaceable"><code>new_owner</code></em></span></dt>
<dd><p> The new owner of the function. Note that if the function 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 function.
</p></dd>
<dt>
<span xmlns="http://www.w3.org/TR/xhtml1/transitional" class="term"><code xmlns="" class="literal">CALLED ON NULL INPUT</code></span><br xmlns="http://www.w3.org/TR/xhtml1/transitional"></br><span xmlns="http://www.w3.org/TR/xhtml1/transitional" class="term"><code xmlns="" class="literal">RETURNS NULL ON NULL INPUT</code></span><br xmlns="http://www.w3.org/TR/xhtml1/transitional"></br><span class="term"><code class="literal">STRICT</code></span>
</dt>
<dd><p> <code class="literal">CALLED ON NULL INPUT</code> changes the function so
that it will be invoked when some or all of its arguments are
null. <code class="literal">RETURNS NULL ON NULL INPUT</code> or
<code class="literal">STRICT</code> changes the function so that it is not
invoked if any of its arguments are null; instead, a null result
is assumed automatically. See <a href="sql-createfunction.html" title="CREATE FUNCTION"><span class="refentrytitle"><a name="sql-createfunction-title"></a>CREATE FUNCTION</span></a> for more information.
</p></dd>
<dt>
<span xmlns="http://www.w3.org/TR/xhtml1/transitional" class="term"><code xmlns="" class="literal">IMMUTABLE</code></span><br xmlns="http://www.w3.org/TR/xhtml1/transitional"></br><span xmlns="http://www.w3.org/TR/xhtml1/transitional" class="term"><code xmlns="" class="literal">STABLE</code></span><br xmlns="http://www.w3.org/TR/xhtml1/transitional"></br><span class="term"><code class="literal">VOLATILE</code></span>
</dt>
<dd><p> Change the volatility of the function to the specified
setting. See <a href="sql-createfunction.html" title="CREATE FUNCTION"><span class="refentrytitle"><a name="sql-createfunction-title"></a>CREATE FUNCTION</span></a> for details.
</p></dd>
<dt>
<span xmlns="http://www.w3.org/TR/xhtml1/transitional" class="term"><code xmlns="" class="literal">[<span class="optional"> EXTERNAL </span>] SECURITY INVOKER</code></span><br xmlns="http://www.w3.org/TR/xhtml1/transitional"></br><span class="term"><code class="literal">[<span class="optional"> EXTERNAL </span>] SECURITY DEFINER</code></span>
</dt>
<dd><p> Change whether the function is a security definer or not. The
key word <code class="literal">EXTERNAL</code> is ignored for SQL
conformance. See <a href="sql-createfunction.html" title="CREATE FUNCTION"><span class="refentrytitle"><a name="sql-createfunction-title"></a>CREATE FUNCTION</span></a> for more
information about this capability.
</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" lang="en">
<a name="id742485"></a><h2>Examples</h2>
<p> To rename the function <code class="literal">sqrt</code> for type
<code class="type">integer</code> to <code class="literal">square_root</code>:
</p>
<pre class="programlisting">ALTER FUNCTION sqrt(integer) RENAME TO square_root;</pre>
<p>
</p>
<p> To change the owner of the function <code class="literal">sqrt</code> for type
<code class="type">integer</code> to <code class="literal">joe</code>:
</p>
<pre class="programlisting">ALTER FUNCTION sqrt(integer) OWNER TO joe;</pre>
<p>
</p>
<p> To change the schema of the function <code class="literal">sqrt</code> for type
<code class="type">integer</code> to <code class="literal">maths</code>:
</p>
<pre class="programlisting">ALTER FUNCTION sqrt(integer) SET SCHEMA maths;</pre>
<p>
</p>
</div>
<div class="refsect1" lang="en">
<a name="id742571"></a><h2>Compatibility</h2>
<p> This statement is partially compatible with the <code class="command">ALTER
FUNCTION</code> statement in the SQL standard. The standard allows more
properties of a function to be modified, but does not provide the
ability to rename a function, make a function a security definer,
or change the owner, schema, or volatility of a function. 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" lang="en">
<a name="id742604"></a><h2>See Also</h2>
<span class="simplelist"><a href="sql-createfunction.html">CREATE FUNCTION</a>, <a href="sql-dropfunction.html">DROP FUNCTION</a></span>
</div>
</div></body>
</html>
|
