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