doc/src/sgml/ref/alter_function.sgml

   1 <!--
   2 $PostgreSQL$
   3 PostgreSQL documentation
   4 -->
   5
   6 <refentry id="SQL-ALTERFUNCTION">
   7  <refmeta>
   8   <refentrytitle id="SQL-ALTERFUNCTION-TITLE">ALTER FUNCTION</refentrytitle>
   9   <manvolnum>7</manvolnum>
  10   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  11  </refmeta>
  12
  13  <refnamediv>
  14   <refname>ALTER FUNCTION</refname>
  15   <refpurpose>change the definition of a function</refpurpose>
  16  </refnamediv>
  17
  18  <indexterm zone="sql-alterfunction">
  19   <primary>ALTER FUNCTION</primary>
  20  </indexterm>
  21
  22  <refsynopsisdiv>
  23 <synopsis>
  24 ALTER FUNCTION <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 FUNCTION <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 FUNCTION <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>
  30 ALTER FUNCTION <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
  33 where <replaceable class="PARAMETER">action</replaceable> is one of:
  34
  35     CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
  36     IMMUTABLE | STABLE | VOLATILE
  37     [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
  38     COST <replaceable class="parameter">execution_cost</replaceable>
  39     ROWS <replaceable class="parameter">result_rows</replaceable>
  40     SET <replaceable class="parameter">configuration_parameter</replaceable> { TO | = } { <replaceable class="parameter">value</replaceable> | DEFAULT }
  41     SET <replaceable class="parameter">configuration_parameter</replaceable> FROM CURRENT
  42     RESET <replaceable class="parameter">configuration_parameter</replaceable>
  43     RESET ALL
  44 </synopsis>
  45  </refsynopsisdiv>
  46
  47  <refsect1>
  48   <title>Description</title>
  49
  50   <para>
  51    <command>ALTER FUNCTION</command> changes the definition of a
  52    function.
  53   </para>
  54
  55   <para>
  56    You must own the function to use <command>ALTER FUNCTION</>.
  57    To change a function's schema, you must also have <literal>CREATE</>
  58    privilege on the new schema.
  59    To alter the owner, you must also be a direct or indirect member of the new
  60    owning role, and that role must have <literal>CREATE</literal> privilege on
  61    the function's schema.  (These restrictions enforce that altering the owner
  62    doesn't do anything you couldn't do by dropping and recreating the function.
  63    However, a superuser can alter ownership of any function anyway.)
  64   </para>
  65  </refsect1>
  66
  67  <refsect1>
  68   <title>Parameters</title>
  69
  70   <variablelist>
  71    <varlistentry>
  72     <term><replaceable class="parameter">name</replaceable></term>
  73     <listitem>
  74      <para>
  75       The name (optionally schema-qualified) of an existing function.
  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>OUT</>,
  86       <literal>INOUT</>, or <literal>VARIADIC</>.
  87       If omitted, the default is <literal>IN</>.
  88       Note that <command>ALTER FUNCTION</command> does not actually pay
  89       any attention to <literal>OUT</> arguments, since only the input
  90       arguments are needed to determine the function's identity.
  91       So it is sufficient to list the <literal>IN</>, <literal>INOUT</>,
  92       and <literal>VARIADIC</> arguments.
  93      </para>
  94     </listitem>
  95    </varlistentry>
  96
  97    <varlistentry>
  98     <term><replaceable class="parameter">argname</replaceable></term>
  99
 100     <listitem>
 101      <para>
 102       The name of an argument.
 103       Note that <command>ALTER FUNCTION</command> does not actually pay
 104       any attention to argument names, since only the argument data
 105       types are needed to determine the function's identity.
 106      </para>
 107     </listitem>
 108    </varlistentry>
 109
 110    <varlistentry>
 111     <term><replaceable class="parameter">argtype</replaceable></term>
 112
 113     <listitem>
 114      <para>
 115       The data type(s) of the function's arguments (optionally
 116       schema-qualified), if any.
 117      </para>
 118     </listitem>
 119    </varlistentry>
 120
 121    <varlistentry>
 122     <term><replaceable class="parameter">new_name</replaceable></term>
 123     <listitem>
 124      <para>
 125       The new name of the function.
 126      </para>
 127     </listitem>
 128    </varlistentry>
 129
 130    <varlistentry>
 131     <term><replaceable class="parameter">new_owner</replaceable></term>
 132     <listitem>
 133      <para>
 134       The new owner of the function.  Note that if the function is
 135       marked <literal>SECURITY DEFINER</literal>, it will subsequently
 136       execute as the new owner.
 137      </para>
 138     </listitem>
 139    </varlistentry>
 140
 141    <varlistentry>
 142     <term><replaceable class="parameter">new_schema</replaceable></term>
 143     <listitem>
 144      <para>
 145       The new schema for the function.
 146      </para>
 147     </listitem>
 148    </varlistentry>
 149
 150     <varlistentry>
 151      <term><literal>CALLED ON NULL INPUT</literal></term>
 152      <term><literal>RETURNS NULL ON NULL INPUT</literal></term>
 153      <term><literal>STRICT</literal></term>
 154
 155      <listitem>
 156       <para>
 157        <literal>CALLED ON NULL INPUT</literal> changes the function so
 158        that it will be invoked when some or all of its arguments are
 159        null. <literal>RETURNS NULL ON NULL INPUT</literal> or
 160        <literal>STRICT</literal> changes the function so that it is not
 161        invoked if any of its arguments are null; instead, a null result
 162        is assumed automatically.  See <xref linkend="sql-createfunction"
 163        endterm="sql-createfunction-title"> for more information.
 164       </para>
 165      </listitem>
 166    </varlistentry>
 167
 168     <varlistentry>
 169      <term><literal>IMMUTABLE</literal></term>
 170      <term><literal>STABLE</literal></term>
 171      <term><literal>VOLATILE</literal></term>
 172
 173      <listitem>
 174       <para>
 175        Change the volatility of the function to the specified setting.
 176        See <xref linkend="sql-createfunction"
 177        endterm="sql-createfunction-title"> for details.
 178       </para>
 179     </listitem>
 180    </varlistentry>
 181
 182    <varlistentry>
 183     <term><literal><optional> EXTERNAL </optional> SECURITY INVOKER</literal></term>
 184     <term><literal><optional> EXTERNAL </optional> SECURITY DEFINER</literal></term>
 185
 186     <listitem>
 187      <para>
 188       Change whether the function is a security definer or not. The
 189       key word <literal>EXTERNAL</literal> is ignored for SQL
 190       conformance. See <xref linkend="sql-createfunction"
 191       endterm="sql-createfunction-title"> for more information about
 192       this capability.
 193      </para>
 194     </listitem>
 195    </varlistentry>
 196
 197     <varlistentry>
 198      <term><literal>COST</literal> <replaceable class="parameter">execution_cost</replaceable></term>
 199
 200      <listitem>
 201       <para>
 202        Change the estimated execution cost of the function.
 203        See <xref linkend="sql-createfunction"
 204        endterm="sql-createfunction-title"> for more information.
 205       </para>
 206      </listitem>
 207    </varlistentry>
 208
 209     <varlistentry>
 210      <term><literal>ROWS</literal> <replaceable class="parameter">result_rows</replaceable></term>
 211
 212      <listitem>
 213       <para>
 214        Change the estimated number of rows returned by a set-returning
 215        function.  See <xref linkend="sql-createfunction"
 216        endterm="sql-createfunction-title"> for more information.
 217       </para>
 218      </listitem>
 219    </varlistentry>
 220
 221      <varlistentry>
 222       <term><replaceable>configuration_parameter</replaceable></term>
 223       <term><replaceable>value</replaceable></term>
 224       <listitem>
 225        <para>
 226         Add or change the assignment to be made to a configuration parameter
 227         when the function is called.  If
 228         <replaceable>value</replaceable> is <literal>DEFAULT</literal>
 229         or, equivalently, <literal>RESET</literal> is used, the function-local
 230         setting is removed, so that the function executes with the value
 231         present in its environment.  Use <literal>RESET
 232         ALL</literal> to clear all function-local settings.
 233         <literal>SET FROM CURRENT</> saves the session's current value of
 234         the parameter as the value to be applied when the function is entered.
 235        </para>
 236
 237        <para>
 238         See <xref linkend="sql-set" endterm="sql-set-title"> and
 239         <xref linkend="runtime-config">
 240         for more information about allowed parameter names and values.
 241        </para>
 242       </listitem>
 243      </varlistentry>
 244
 245    <varlistentry>
 246     <term><literal>RESTRICT</literal></term>
 247
 248     <listitem>
 249      <para>
 250       Ignored for conformance with the SQL standard.
 251      </para>
 252     </listitem>
 253    </varlistentry>
 254   </variablelist>
 255  </refsect1>
 256
 257  <refsect1>
 258   <title>Examples</title>
 259
 260   <para>
 261    To rename the function <literal>sqrt</literal> for type
 262    <type>integer</type> to <literal>square_root</literal>:
 263 <programlisting>
 264 ALTER FUNCTION sqrt(integer) RENAME TO square_root;
 265 </programlisting>
 266   </para>
 267
 268   <para>
 269    To change the owner of the function <literal>sqrt</literal> for type
 270    <type>integer</type> to <literal>joe</literal>:
 271 <programlisting>
 272 ALTER FUNCTION sqrt(integer) OWNER TO joe;
 273 </programlisting>
 274   </para>
 275
 276   <para>
 277    To change the schema of the function <literal>sqrt</literal> for type
 278    <type>integer</type> to <literal>maths</literal>:
 279 <programlisting>
 280 ALTER FUNCTION sqrt(integer) SET SCHEMA maths;
 281 </programlisting>
 282   </para>
 283
 284   <para>
 285    To adjust the search path that is automatically set for a function:
 286 <programlisting>
 287 ALTER FUNCTION check_password(text) SET search_path = admin, pg_temp;
 288 </programlisting>
 289   </para>
 290
 291   <para>
 292    To disable automatic setting of <varname>search_path</> for a function:
 293 <programlisting>
 294 ALTER FUNCTION check_password(text) RESET search_path;
 295 </programlisting>
 296    The function will now execute with whatever search path is used by its
 297    caller.
 298   </para>
 299  </refsect1>
 300
 301  <refsect1>
 302   <title>Compatibility</title>
 303
 304   <para>
 305    This statement is partially compatible with the <command>ALTER
 306    FUNCTION</> statement in the SQL standard. The standard allows more
 307    properties of a function to be modified, but does not provide the
 308    ability to rename a function, make a function a security definer,
 309    attach configuration parameter values to a function,
 310    or change the owner, schema, or volatility of a function. The standard also
 311    requires the <literal>RESTRICT</> key word, which is optional in
 312    <productname>PostgreSQL</>.
 313   </para>
 314  </refsect1>
 315
 316  <refsect1>
 317   <title>See Also</title>
 318
 319   <simplelist type="inline">
 320    <member><xref linkend="sql-createfunction" endterm="sql-createfunction-title"></member>
 321    <member><xref linkend="sql-dropfunction" endterm="sql-dropfunction-title"></member>
 322   </simplelist>
 323  </refsect1>
 324 </refentry>