~ubuntu-branches/debian/squeeze/pgadmin3/squeeze

<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>

OWNER TO <em class="replaceable"><code>new_owner</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>

<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>

<a name="id742179"></a><h2>Parameters</h2>

<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>

<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>

100

</dt>

101

<dd><p> Change the volatility of the function to the specified

102

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.

103

</p></dd>

104

<dt>

105

<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>

106

</dt>

107

<dd><p> Change whether the function is a security definer or not. The

108

key word <code class="literal">EXTERNAL</code> is ignored for SQL

109

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

110

information about this capability.

111

</p></dd>

112

<dt><span class="term"><code class="literal">RESTRICT</code></span></dt>

113

<dd><p> Ignored for conformance with the SQL standard.

114

</p></dd>

115

</dl></div>

116

</div>

117

118

<a name="id742485"></a><h2>Examples</h2>

119

<p> To rename the function <code class="literal">sqrt</code> for type

120

<code class="type">integer</code> to <code class="literal">square_root</code>:

121

</p>

122

<pre class="programlisting">ALTER FUNCTION sqrt(integer) RENAME TO square_root;</pre>

123

<p>

124

</p>

125

<p> To change the owner of the function <code class="literal">sqrt</code> for type

126

<code class="type">integer</code> to <code class="literal">joe</code>:

127

</p>

128

<pre class="programlisting">ALTER FUNCTION sqrt(integer) OWNER TO joe;</pre>

129

<p>

130

</p>

131

<p> To change the schema of the function <code class="literal">sqrt</code> for type

132

<code class="type">integer</code> to <code class="literal">maths</code>:

133

</p>

134

<pre class="programlisting">ALTER FUNCTION sqrt(integer) SET SCHEMA maths;</pre>

135

<p>

136

</p>

137

</div>

138

139

<a name="id742571"></a><h2>Compatibility</h2>

140

<p> This statement is partially compatible with the <code class="command">ALTER

141

FUNCTION</code> statement in the SQL standard. The standard allows more

142

properties of a function to be modified, but does not provide the

143

ability to rename a function, make a function a security definer,

144

or change the owner, schema, or volatility of a function. The standard also

145

requires the <code class="literal">RESTRICT</code> key word, which is optional in

146

<span class="productname">PostgreSQL</span>.

147

</p>

148

</div>

149

150

151

<span class="simplelist"><a href="sql-createfunction.html">CREATE FUNCTION</a>, <a href="sql-dropfunction.html">DROP FUNCTION</a></span>

152

</div>

153

</div></body>

154

</html>

Older »