ALTER ROUTINE EARLY ACCESS

See the dedicated 'User-defined subprograms and anonymous blocks' section.

User-defined functions and procedures are part of a larger area of functionality. See this major section:

User-defined subprograms and anonymous blocks—"language SQL" and "language plpgsql"

Synopsis

Use the ALTER ROUTINE statement to change properties of an existing routine. A routine is either a function or a procedure.

alter_routine ::= ALTER ROUTINE subprogram_name ( 
                  [ subprogram_signature ] )  
                  { special_fn_and_proc_attribute
                    | { alterable_fn_and_proc_attribute
                        | alterable_fn_only_attribute } [ ... ] 
                      [ RESTRICT ] }

subprogram_signature ::= arg_decl [ , ... ]

arg_decl ::= [ formal_arg ] [ arg_mode ] arg_type

special_fn_and_proc_attribute ::= RENAME TO subprogram_name
                                  | OWNER TO 
                                    { role_name
                                      | CURRENT_ROLE
                                      | CURRENT_USER
                                      | SESSION_USER }
                                  | SET SCHEMA schema_name
                                  | [ NO ] DEPENDS ON EXTENSION 
                                    extension_name

alterable_fn_and_proc_attribute ::= SET run_time_parameter 
                                    { TO value
                                      | = value
                                      | FROM CURRENT }
                                    | RESET run_time_parameter
                                    | RESET ALL
                                    | [ EXTERNAL ] SECURITY 
                                      { INVOKER | DEFINER }

alterable_fn_only_attribute ::= volatility
                                | on_null_input
                                | PARALLEL parallel_mode
                                | [ NOT ] LEAKPROOF
                                | COST int_literal
                                | ROWS int_literal

volatility ::= IMMUTABLE | STABLE | VOLATILE

on_null_input ::= CALLED ON NULL INPUT
                  | RETURNS NULL ON NULL INPUT
                  | STRICT

parallel_mode ::= UNSAFE | RESTRICTED | SAFE

alter_routine

subprogram_signature

arg_decl

special_fn_and_proc_attribute

alterable_fn_and_proc_attribute

alterable_fn_only_attribute

volatility

on_null_input

parallel_mode

You must identify the routine by:

Its name and the schema where it lives. This can be done by using its fully qualified name or by using just its bare name and letting name resolution find it in the first schema on the search_path where it occurs. Notice that you don't need to (and cannot) mention the name of its owner.
Its signature. The subprogram_call_signature is sufficient; and this is typically used. You can use the full subprogram_signature. But you should realize that the formal_arg and arg_mode for each arg_decl carry no identifying information. (This is why it is not typically used when a function or procedure is to be altered or dropped.) This is explained in the section Subprogram overloading.

Description

ALTER ROUTINE is a variant of ALTER FUNCTION and ALTER PROCEDURE that can apply to either functions or procedures without specifying which type. It is useful when you need to alter a routine but don't know or care whether it is a function or procedure.

Supported actions

The same operations available in ALTER FUNCTION and ALTER PROCEDURE are supported. The following forms are accepted (subject to the same restrictions as on ALTER FUNCTION / ALTER PROCEDURE for the kind of routine):

ALTER ROUTINE ... RENAME TO new_name : Rename the routine.
ALTER ROUTINE ... SET SCHEMA new_schema : Move the routine to a different schema.
ALTER ROUTINE ... SET configuration_parameter = { new_value | DEFAULT } [ RESTRICT ] : Set a configuration parameter on the routine to a specific value. The caller's value is saved on entry and restored when the routine returns.
ALTER ROUTINE ... SET configuration_parameter FROM CURRENT [ RESTRICT ] : Set a configuration parameter on the routine to the session's current value at the time of the ALTER statement.
ALTER ROUTINE ... RESET configuration_parameter [ RESTRICT ] : Remove a configuration-parameter setting from the routine.
ALTER ROUTINE ... RESET ALL [ RESTRICT ] : Remove all configuration-parameter settings from the routine.
ALTER ROUTINE ... [ EXTERNAL ] SECURITY { INVOKER | DEFINER } [ RESTRICT ] : Change the security context.
ALTER ROUTINE ... [ NO ] DEPENDS ON EXTENSION extension_name : Mark dependency on an extension.
ALTER ROUTINE ... OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }: Change the routine's owner.

Example

Rename a routine (works for both functions and procedures):

CREATE FUNCTION f(int) RETURNS int LANGUAGE sql AS 'SELECT $1 * 2';

-- Rename without needing to know if it's a function or procedure
ALTER ROUTINE f(int) RENAME TO double_it;

-- Move to a different schema
CREATE SCHEMA utils;
ALTER ROUTINE double_it(int) SET SCHEMA utils;

-- Change security
ALTER ROUTINE utils.double_it(int) SECURITY DEFINER;