info > ALTER_ROUTINE(7)

man perldoc info pydoc3 ri search

📛 NAME

ALTER ROUTINE — change the definition of a routine (aggregate, function, or procedure).

🚀 Quick Reference

Use Case	Command	Description
🔄 Rename a routine	ALTER ROUTINE foo(integer) RENAME TO bar;	Rename the routine with matching argument types.
📦 Change owner	ALTER ROUTINE foo(integer) OWNER TO alice;	Transfer ownership of the routine.
🗂️ Move to a schema	ALTER ROUTINE foo(integer) SET SCHEMA new_schema;	Relocate the routine to another schema.
🔒 Security definer	ALTER ROUTINE foo(integer) SECURITY DEFINER;	Execute with the privileges of the routine owner.
📊 Set volatility	ALTER ROUTINE foo(integer) IMMUTABLE;	Label as IMMUTABLE, STABLE, or VOLATILE.
⚙️ Change cost	ALTER ROUTINE foo(integer) COST 100;	Adjust estimated execution cost.
📈 Estimated rows	ALTER ROUTINE foo(integer) ROWS 1000;	Set approximate number of rows returned by the routine.
🧵 Parallel safety	ALTER ROUTINE foo(integer) PARALLEL SAFE;	Control whether parallel execution is allowed.
🛠️ Configuration	ALTER ROUTINE foo(integer) SET work_mem = '4MB';	Set session-level parameters for the routine.
🔗 Extension dependency	ALTER ROUTINE foo(integer) DEPENDS ON EXTENSION ext;	Mark routine as dependent (or NOT) on an extension.

📋 SYNOPSIS

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 (7), ALTER FUNCTION (7), and ALTER PROCEDURE (7) 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 (7) and ALTER PROCEDURE (7) for more details. Allowing routine names to refer to aggregate functions is a PostgreSQL extension.

📚 SEE ALSO

ALTER AGGREGATE (7), ALTER FUNCTION (7), ALTER PROCEDURE (7), DROP ROUTINE (7)

Note that there is no CREATE ROUTINE command.

📛 NAME 🚀 Quick Reference 📋 SYNOPSIS 📖 DESCRIPTION 💡 EXAMPLES 🔄 COMPATIBILITY 📚 SEE ALSO