Home Explore Blog CI



postgresql

4th chunk of `doc/src/sgml/ref/alter_procedure.sgml`
05dfe983902972018821b8a0f1c5f8efc7df368667be302c0000000100000d21
 present in its environment.  Use <literal>RESET
        ALL</literal> to clear all procedure-local settings.
        <literal>SET FROM CURRENT</literal> saves the value of the parameter that
        is current when <command>ALTER PROCEDURE</command> is executed as the value
        to be applied when the procedure is entered.
       </para>

       <para>
        See <xref linkend="sql-set"/> and
        <xref linkend="runtime-config"/>
        for more information about allowed parameter names and values.
       </para>
      </listitem>
     </varlistentry>

   <varlistentry>
    <term><literal>RESTRICT</literal></term>

    <listitem>
     <para>
      Ignored for conformance with the SQL standard.
     </para>
    </listitem>
   </varlistentry>
  </variablelist>
 </refsect1>

 <refsect1>
  <title>Examples</title>

  <para>
   To rename the procedure <literal>insert_data</literal> with two arguments
   of type <type>integer</type> to <literal>insert_record</literal>:
<programlisting>
ALTER PROCEDURE insert_data(integer, integer) RENAME TO insert_record;
</programlisting>
  </para>

  <para>
   To change the owner of the procedure <literal>insert_data</literal> with
   two arguments of type <type>integer</type> to <literal>joe</literal>:
<programlisting>
ALTER PROCEDURE insert_data(integer, integer) OWNER TO joe;
</programlisting>
  </para>

  <para>
   To change the schema of the procedure <literal>insert_data</literal> with
   two arguments of type <type>integer</type>
   to <literal>accounting</literal>:
<programlisting>
ALTER PROCEDURE insert_data(integer, integer) SET SCHEMA accounting;
</programlisting>
  </para>

  <para>
   To mark the procedure <literal>insert_data(integer, integer)</literal> as
   being dependent on the extension <literal>myext</literal>:
<programlisting>
ALTER PROCEDURE insert_data(integer, integer) DEPENDS ON EXTENSION myext;
</programlisting>
  </para>

  <para>
   To adjust the search path that is automatically set for a procedure:
<programlisting>
ALTER PROCEDURE check_password(text) SET search_path = admin, pg_temp;
</programlisting>
  </para>

  <para>
   To disable automatic setting of <varname>search_path</varname> for a procedure:
<programlisting>
ALTER PROCEDURE check_password(text) RESET search_path;
</programlisting>
   The procedure will now execute with whatever search path is used by its
   caller.
  </para>
 </refsect1>

 <refsect1>
  <title>Compatibility</title>

  <para>
   This statement is partially compatible with the <command>ALTER
   PROCEDURE</command> statement in the SQL standard. The standard allows more
   properties of a procedure to be modified, but does not provide the
   ability to rename a procedure, make a procedure a security definer,
   attach configuration parameter values to a procedure,
   or change the owner, schema, or volatility of a procedure. The standard also
   requires the <literal>RESTRICT</literal> key word, which is optional in
   <productname>PostgreSQL</productname>.
  </para>
 </refsect1>

 <refsect1>
  <title>See Also</title>

  <simplelist type="inline">
   <member><xref linkend="sql-createprocedure"/></member>
   <member><xref linkend="sql-dropprocedure"/></member>
   <member><xref linkend="sql-alterfunction"/></member>
   <member><xref linkend="sql-alterroutine"/></member>
  </simplelist>
 </refsect1>
</refentry>

Title: ALTER PROCEDURE Examples, Compatibility, and See Also
Summary
This section provides practical examples of using the ALTER PROCEDURE command, including renaming a procedure, changing its owner and schema, marking it as dependent on an extension, and adjusting its search path. It then discusses the command's compatibility with the SQL standard, noting PostgreSQL extensions. Finally, it provides links to related commands such as CREATE PROCEDURE, DROP PROCEDURE, ALTER FUNCTION, and ALTER ROUTINE.