doc/src/sgml/ref/alter_function.sgml

   1 <!--
   2 doc/src/sgml/ref/alter_function.sgml
   3 PostgreSQL documentation
   4 -->
   5
   6 <refentry id="sql-alterfunction">
   7  <indexterm zone="sql-alterfunction">
   8   <primary>ALTER FUNCTION</primary>
   9  </indexterm>
  10
  11  <refmeta>
  12   <refentrytitle>ALTER FUNCTION</refentrytitle>
  13   <manvolnum>7</manvolnum>
  14   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  15  </refmeta>
  16
  17  <refnamediv>
  18   <refname>ALTER FUNCTION</refname>
  19   <refpurpose>change the definition of a function</refpurpose>
  20  </refnamediv>
  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> | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
  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 ALTER FUNCTION <replaceable>name</replaceable> [ ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">argtype</replaceable> [, ...] ] ) ]
  33     [ NO ] DEPENDS ON EXTENSION <replaceable>extension_name</replaceable>
  34
  35 <phrase>where <replaceable class="parameter">action</replaceable> is one of:</phrase>
  36
  37     CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
  38     IMMUTABLE | STABLE | VOLATILE
  39     [ NOT ] LEAKPROOF
  40     [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
  41     PARALLEL { UNSAFE | RESTRICTED | SAFE }
  42     COST <replaceable class="parameter">execution_cost</replaceable>
  43     ROWS <replaceable class="parameter">result_rows</replaceable>
  44     SUPPORT <replaceable class="parameter">support_function</replaceable>
  45     SET <replaceable class="parameter">configuration_parameter</replaceable> { TO | = } { <replaceable class="parameter">value</replaceable> | DEFAULT }
  46     SET <replaceable class="parameter">configuration_parameter</replaceable> FROM CURRENT
  47     RESET <replaceable class="parameter">configuration_parameter</replaceable>
  48     RESET ALL
  49 </synopsis>
  50  </refsynopsisdiv>
  51
  52  <refsect1>
  53   <title>Description</title>
  54
  55   <para>
  56    <command>ALTER FUNCTION</command> changes the definition of a
  57    function.
  58   </para>
  59
  60   <para>
  61    You must own the function to use <command>ALTER FUNCTION</command>.
  62    To change a function's schema, you must also have <literal>CREATE</literal>
  63    privilege on the new schema.  To alter the owner, you must be able to
  64    <literal>SET ROLE</literal> to the new owning role, and that role must
  65    have <literal>CREATE</literal> privilege on
  66    the function's schema.  (These restrictions enforce that altering the owner
  67    doesn't do anything you couldn't do by dropping and recreating the function.
  68    However, a superuser can alter ownership of any function anyway.)
  69   </para>
  70  </refsect1>
  71
  72  <refsect1>
  73   <title>Parameters</title>
  74
  75   <variablelist>
  76    <varlistentry>
  77     <term><replaceable class="parameter">name</replaceable></term>
  78     <listitem>
  79      <para>
  80       The name (optionally schema-qualified) of an existing function.  If no
  81       argument list is specified, the name must be unique in its schema.
  82      </para>
  83     </listitem>
  84    </varlistentry>
  85
  86    <varlistentry>
  87     <term><replaceable class="parameter">argmode</replaceable></term>
  88
  89     <listitem>
  90      <para>
  91       The mode of an argument: <literal>IN</literal>, <literal>OUT</literal>,
  92       <literal>INOUT</literal>, or <literal>VARIADIC</literal>.
  93       If omitted, the default is <literal>IN</literal>.
  94       Note that <command>ALTER FUNCTION</command> does not actually pay
  95       any attention to <literal>OUT</literal> arguments, since only the input
  96       arguments are needed to determine the function's identity.
  97       So it is sufficient to list the <literal>IN</literal>, <literal>INOUT</literal>,
  98       and <literal>VARIADIC</literal> arguments.
  99      </para>
 100     </listitem>
 101    </varlistentry>
 102
 103    <varlistentry>
 104     <term><replaceable class="parameter">argname</replaceable></term>
 105
 106     <listitem>
 107      <para>
 108       The name of an argument.
 109       Note that <command>ALTER FUNCTION</command> does not actually pay
 110       any attention to argument names, since only the argument data
 111       types are needed to determine the function's identity.
 112      </para>
 113     </listitem>
 114    </varlistentry>
 115
 116    <varlistentry>
 117     <term><replaceable class="parameter">argtype</replaceable></term>
 118
 119     <listitem>
 120      <para>
 121       The data type(s) of the function's arguments (optionally
 122       schema-qualified), if any.
 123      </para>
 124     </listitem>
 125    </varlistentry>
 126
 127    <varlistentry>
 128     <term><replaceable class="parameter">new_name</replaceable></term>
 129     <listitem>
 130      <para>
 131       The new name of the function.
 132      </para>
 133     </listitem>
 134    </varlistentry>
 135
 136    <varlistentry>
 137     <term><replaceable class="parameter">new_owner</replaceable></term>
 138     <listitem>
 139      <para>
 140       The new owner of the function.  Note that if the function is
 141       marked <literal>SECURITY DEFINER</literal>, it will subsequently
 142       execute as the new owner.
 143      </para>
 144     </listitem>
 145    </varlistentry>
 146
 147    <varlistentry>
 148     <term><replaceable class="parameter">new_schema</replaceable></term>
 149     <listitem>
 150      <para>
 151       The new schema for the function.
 152      </para>
 153     </listitem>
 154    </varlistentry>
 155
 156    <varlistentry>
 157     <term><literal>DEPENDS ON EXTENSION <replaceable class="parameter">extension_name</replaceable></literal></term>
 158     <term><literal>NO DEPENDS ON EXTENSION <replaceable class="parameter">extension_name</replaceable></literal></term>
 159     <listitem>
 160      <para>
 161       This form marks the function as dependent on the extension, or no longer
 162       dependent on that extension if <literal>NO</literal> is specified.
 163       A function that's marked as dependent on an extension is dropped when the
 164       extension is dropped, even if <literal>CASCADE</literal> is not specified.
 165       A function can depend upon multiple extensions, and will be dropped when
 166       any one of those extensions is dropped.
 167      </para>
 168     </listitem>
 169    </varlistentry>
 170
 171     <varlistentry>
 172      <term><literal>CALLED ON NULL INPUT</literal></term>
 173      <term><literal>RETURNS NULL ON NULL INPUT</literal></term>
 174      <term><literal>STRICT</literal></term>
 175
 176      <listitem>
 177       <para><literal>CALLED ON NULL INPUT</literal> changes the function so
 178        that it will be invoked when some or all of its arguments are
 179        null. <literal>RETURNS NULL ON NULL INPUT</literal> or
 180        <literal>STRICT</literal> changes the function so that it is not
 181        invoked if any of its arguments are null; instead, a null result
 182        is assumed automatically.  See <xref linkend="sql-createfunction"/>
 183        for more information.
 184       </para>
 185      </listitem>
 186    </varlistentry>
 187
 188     <varlistentry>
 189      <term><literal>IMMUTABLE</literal></term>
 190      <term><literal>STABLE</literal></term>
 191      <term><literal>VOLATILE</literal></term>
 192
 193      <listitem>
 194       <para>
 195        Change the volatility of the function to the specified setting.
 196        See <xref linkend="sql-createfunction"/> for details.
 197       </para>
 198     </listitem>
 199    </varlistentry>
 200
 201    <varlistentry>
 202     <term><literal><optional> EXTERNAL </optional> SECURITY INVOKER</literal></term>
 203     <term><literal><optional> EXTERNAL </optional> SECURITY DEFINER</literal></term>
 204
 205     <listitem>
 206      <para>
 207       Change whether the function is a security definer or not. The
 208       key word <literal>EXTERNAL</literal> is ignored for SQL
 209       conformance. See <xref linkend="sql-createfunction"/> for more information about
 210       this capability.
 211      </para>
 212     </listitem>
 213    </varlistentry>
 214
 215    <varlistentry>
 216     <term><literal>PARALLEL</literal></term>
 217
 218     <listitem>
 219      <para>
 220       Change whether the function is deemed safe for parallelism.
 221       See <xref linkend="sql-createfunction"/> for details.
 222      </para>
 223     </listitem>
 224    </varlistentry>
 225
 226    <varlistentry>
 227     <term><literal>LEAKPROOF</literal></term>
 228     <listitem>
 229      <para>
 230       Change whether the function is considered leakproof or not.
 231       See <xref linkend="sql-createfunction"/> for more information about
 232       this capability.
 233      </para>
 234     </listitem>
 235    </varlistentry>
 236
 237     <varlistentry>
 238      <term><literal>COST</literal> <replaceable class="parameter">execution_cost</replaceable></term>
 239
 240      <listitem>
 241       <para>
 242        Change the estimated execution cost of the function.
 243        See <xref linkend="sql-createfunction"/> for more information.
 244       </para>
 245      </listitem>
 246    </varlistentry>
 247
 248     <varlistentry>
 249      <term><literal>ROWS</literal> <replaceable class="parameter">result_rows</replaceable></term>
 250
 251      <listitem>
 252       <para>
 253        Change the estimated number of rows returned by a set-returning
 254        function.  See <xref linkend="sql-createfunction"/> for more information.
 255       </para>
 256      </listitem>
 257    </varlistentry>
 258
 259    <varlistentry>
 260     <term><literal>SUPPORT</literal> <replaceable class="parameter">support_function</replaceable></term>
 261
 262     <listitem>
 263      <para>
 264       Set or change the planner support function to use for this function.
 265       See <xref linkend="xfunc-optimization"/> for details.  You must be
 266       superuser to use this option.
 267      </para>
 268
 269      <para>
 270       This option cannot be used to remove the support function altogether,
 271       since it must name a new support function.  Use <command>CREATE OR
 272       REPLACE FUNCTION</command> if you need to do that.
 273      </para>
 274     </listitem>
 275    </varlistentry>
 276
 277      <varlistentry>
 278       <term><replaceable>configuration_parameter</replaceable></term>
 279       <term><replaceable>value</replaceable></term>
 280       <listitem>
 281        <para>
 282         Add or change the assignment to be made to a configuration parameter
 283         when the function is called.  If
 284         <replaceable>value</replaceable> is <literal>DEFAULT</literal>
 285         or, equivalently, <literal>RESET</literal> is used, the function-local
 286         setting is removed, so that the function executes with the value
 287         present in its environment.  Use <literal>RESET
 288         ALL</literal> to clear all function-local settings.
 289         <literal>SET FROM CURRENT</literal> saves the value of the parameter that
 290         is current when <command>ALTER FUNCTION</command> is executed as the value
 291         to be applied when the function is entered.
 292        </para>
 293
 294        <para>
 295         See <xref linkend="sql-set"/> and
 296         <xref linkend="runtime-config"/>
 297         for more information about allowed parameter names and values.
 298        </para>
 299       </listitem>
 300      </varlistentry>
 301
 302    <varlistentry>
 303     <term><literal>RESTRICT</literal></term>
 304
 305     <listitem>
 306      <para>
 307       Ignored for conformance with the SQL standard.
 308      </para>
 309     </listitem>
 310    </varlistentry>
 311   </variablelist>
 312  </refsect1>
 313
 314  <refsect1>
 315   <title>Examples</title>
 316
 317   <para>
 318    To rename the function <literal>sqrt</literal> for type
 319    <type>integer</type> to <literal>square_root</literal>:
 320 <programlisting>
 321 ALTER FUNCTION sqrt(integer) RENAME TO square_root;
 322 </programlisting>
 323   </para>
 324
 325   <para>
 326    To change the owner of the function <literal>sqrt</literal> for type
 327    <type>integer</type> to <literal>joe</literal>:
 328 <programlisting>
 329 ALTER FUNCTION sqrt(integer) OWNER TO joe;
 330 </programlisting>
 331   </para>
 332
 333   <para>
 334    To change the schema of the function <literal>sqrt</literal> for type
 335    <type>integer</type> to <literal>maths</literal>:
 336 <programlisting>
 337 ALTER FUNCTION sqrt(integer) SET SCHEMA maths;
 338 </programlisting>
 339   </para>
 340
 341   <para>
 342    To mark the function <literal>sqrt</literal> for type
 343    <type>integer</type> as being dependent on the extension
 344    <literal>mathlib</literal>:
 345 <programlisting>
 346 ALTER FUNCTION sqrt(integer) DEPENDS ON EXTENSION mathlib;
 347 </programlisting>
 348   </para>
 349
 350   <para>
 351    To adjust the search path that is automatically set for a function:
 352 <programlisting>
 353 ALTER FUNCTION check_password(text) SET search_path = admin, pg_temp;
 354 </programlisting>
 355   </para>
 356
 357   <para>
 358    To disable automatic setting of <varname>search_path</varname> for a function:
 359 <programlisting>
 360 ALTER FUNCTION check_password(text) RESET search_path;
 361 </programlisting>
 362    The function will now execute with whatever search path is used by its
 363    caller.
 364   </para>
 365  </refsect1>
 366
 367  <refsect1>
 368   <title>Compatibility</title>
 369
 370   <para>
 371    This statement is partially compatible with the <command>ALTER
 372    FUNCTION</command> statement in the SQL standard. The standard allows more
 373    properties of a function to be modified, but does not provide the
 374    ability to rename a function, make a function a security definer,
 375    attach configuration parameter values to a function,
 376    or change the owner, schema, or volatility of a function. The standard also
 377    requires the <literal>RESTRICT</literal> key word, which is optional in
 378    <productname>PostgreSQL</productname>.
 379   </para>
 380  </refsect1>
 381
 382  <refsect1>
 383   <title>See Also</title>
 384
 385   <simplelist type="inline">
 386    <member><xref linkend="sql-createfunction"/></member>
 387    <member><xref linkend="sql-dropfunction"/></member>
 388    <member><xref linkend="sql-alterprocedure"/></member>
 389    <member><xref linkend="sql-alterroutine"/></member>
 390   </simplelist>
 391  </refsect1>
 392 </refentry>