↑↓ select↵ open⌫ change scopeOpen full search

PG.CENTER connects PostgreSQL documentation, reference, and ecosystem knowledge. Maintained by Pigsty.

Wiki / SQL Commands

ALTER ROUTINE

change the definition of a routine

English manual coverage begins with PostgreSQL 6.4. Source history and runtime observations have their own recorded version boundaries.

Reading PG 18·Select a recorded version

11121314151617181920
anchor
SQL-ALTERROUTINE
file
sql-alterroutine.html
lang
en
name
ALTER ROUTINE
purpose
change the definition of a routine
purpose zh
更改一个例程的定义
sections same as
Unknown
slug
18

Syntax

ALTER ROUTINE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
action [ ... ] [ RESTRICT ]
ALTER ROUTINE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
RENAME TO new_name
ALTER ROUTINE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER ROUTINE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
SET SCHEMA new_schema
ALTER ROUTINE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
[ NO ] DEPENDS ON EXTENSION extension_name
where action is one of:

IMMUTABLE | STABLE | VOLATILE
[ NOT ] LEAKPROOF
[ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
PARALLEL { UNSAFE | RESTRICTED | SAFE }
COST execution_cost
ROWS result_rows
SET configuration_parameter { TO | = } { value | DEFAULT }
SET configuration_parameter FROM CURRENT
RESET configuration_parameter
RESET ALL
Syntax diagrams

Read left to right. Branches are choices, bypasses are optional, loops repeat. Select an underlined parameter to open its rule.

ALTER ROUTINE name ( argmode argname argtype , ) action RESTRICT
ALTER ROUTINE · Syntax 2
ALTER ROUTINE name ( argmode argname argtype , ) RENAME TO new_name
ALTER ROUTINE · Syntax 3
ALTER ROUTINE name ( argmode argname argtype , ) OWNER TO new_owner CURRENT_ROLE CURRENT_USER SESSION_USER
ALTER ROUTINE · Syntax 4
ALTER ROUTINE name ( argmode argname argtype , ) SET SCHEMA new_schema
ALTER ROUTINE · Syntax 5
ALTER ROUTINE name ( argmode argname argtype , ) NO DEPENDS ON EXTENSION extension_name
action
IMMUTABLE STABLE VOLATILE NOT LEAKPROOF EXTERNAL SECURITY INVOKER EXTERNAL SECURITY DEFINER PARALLEL UNSAFE RESTRICTED SAFE COST execution_cost ROWS result_rows SET configuration_parameter TO = value DEFAULT SET configuration_parameter FROM CURRENT RESET configuration_parameter RESET ALL

Definition PG 18

ALTER ROUTINE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
action [ ... ] [ RESTRICT ]
ALTER ROUTINE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
RENAME TO new_name
ALTER ROUTINE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER ROUTINE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
SET SCHEMA new_schema
ALTER ROUTINE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
[ NO ] DEPENDS ON EXTENSION extension_name
where action is one of:

IMMUTABLE | STABLE | VOLATILE
[ NOT ] LEAKPROOF
[ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
PARALLEL { UNSAFE | RESTRICTED | SAFE }
COST execution_cost
ROWS result_rows
SET configuration_parameter { TO | = } { value | DEFAULT }
SET configuration_parameter FROM CURRENT
RESET configuration_parameter
RESET ALL

Description

ALTER ROUTINE changes the definition of a routine, which can be an aggregate function, a normal function, or a procedure. See under ALTER AGGREGATE, ALTER FUNCTION, and ALTER PROCEDURE for the description of the parameters, more examples, and further details.

Examples

To rename the routine foo for type integer to foobar:

ALTER ROUTINE foo(integer) RENAME TO foobar;

This command will work independent of whether foo is an aggregate, function, or procedure.

Compatibility

This statement is partially compatible with the ALTER ROUTINE statement in the SQL standard. See under ALTER FUNCTION and ALTER PROCEDURE for more details. Allowing routine names to refer to aggregate functions is a PostgreSQL extension.

See Also

ALTER AGGREGATE, ALTER FUNCTION, ALTER PROCEDURE, DROP ROUTINE

Note that there is no CREATE ROUTINE command.

Compare versions

Complete source facts

synopsis html

ALTER ROUTINE <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 ROUTINE <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>
ALTER ROUTINE <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> [, ...] ] ) ]
    OWNER TO { <em class="replaceable"><code>new_owner</code></em> | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER ROUTINE <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> [, ...] ] ) ]
    SET SCHEMA <em class="replaceable"><code>new_schema</code></em>
ALTER ROUTINE <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> [, ...] ] ) ]
    [ NO ] DEPENDS ON EXTENSION <em class="replaceable"><code>extension_name</code></em>

<span class="phrase">where <em class="replaceable"><code>action</code></em> is one of:</span>

    IMMUTABLE | STABLE | VOLATILE
    [ NOT ] LEAKPROOF
    [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
    PARALLEL { UNSAFE | RESTRICTED | SAFE }
    COST <em class="replaceable"><code>execution_cost</code></em>
    ROWS <em class="replaceable"><code>result_rows</code></em>
    SET <em class="replaceable"><code>configuration_parameter</code></em> { TO | = } { <em class="replaceable"><code>value</code></em> | DEFAULT }
    SET <em class="replaceable"><code>configuration_parameter</code></em> FROM CURRENT
    RESET <em class="replaceable"><code>configuration_parameter</code></em>
    RESET ALL

synopsis text

ALTER ROUTINE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
action [ ... ] [ RESTRICT ]
ALTER ROUTINE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
RENAME TO new_name
ALTER ROUTINE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER ROUTINE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
SET SCHEMA new_schema
ALTER ROUTINE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
[ NO ] DEPENDS ON EXTENSION extension_name
where action is one of:

IMMUTABLE | STABLE | VOLATILE
[ NOT ] LEAKPROOF
[ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
PARALLEL { UNSAFE | RESTRICTED | SAFE }
COST execution_cost
ROWS result_rows
SET configuration_parameter { TO | = } { value | DEFAULT }
SET configuration_parameter FROM CURRENT
RESET configuration_parameter
RESET ALL

Source references

Full definition and evidence JSON

Definition sources

center · PostgreSQL 18 · wiki-repair-20261023:75f990ec4194add7fb6ca39b379b933d9a8a18eee7df3debfa2ad7cae364be45

Text language: en · wiki-repair-20261023:75f990ec4194add7fb6ca39b379b933d9a8a18eee7df3debfa2ad7cae364be45