--- title: "ALTER ROUTINE" url: "https://docs.yugabyte.com/stable/api/ysql/the-sql-language/statements/ddl_alter_routine/" --- # ALTER ROUTINE Change properties of an existing routine (function or procedure). 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"](/stable/api/ysql/user-defined-subprograms-and-anon-blocks/ '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. ## Syntax - [![Grammar Icon](/icons/file-lines.svg)Grammar](#grammar-1) - [![Diagram Icon](/icons/diagram.svg)Diagram](#diagram-1) ```output.ebnf 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 ALTERROUTINE[subprogram\_name](/stable/api/ysql/syntax_resources/grammar_diagrams#subprogram-name)([subprogram\_signature](#subprogram-signature))[special\_fn\_and\_proc\_attribute](#special-fn-and-proc-attribute)[alterable\_fn\_and\_proc\_attribute](#alterable-fn-and-proc-attribute)[alterable\_fn\_only\_attribute](#alterable-fn-only-attribute)RESTRICT #### subprogram\_signature ,[arg\_decl](#arg-decl) #### arg\_decl [formal\_arg](/stable/api/ysql/syntax_resources/grammar_diagrams#formal-arg)[arg\_mode](/stable/api/ysql/syntax_resources/grammar_diagrams#arg-mode)[arg\_type](/stable/api/ysql/syntax_resources/grammar_diagrams#arg-type) #### special\_fn\_and\_proc\_attribute RENAMETO[subprogram\_name](/stable/api/ysql/syntax_resources/grammar_diagrams#subprogram-name)OWNERTO[role\_name](/stable/api/ysql/syntax_resources/grammar_diagrams#role-name)CURRENT\_ROLECURRENT\_USERSESSION\_USERSETSCHEMA[schema\_name](/stable/api/ysql/syntax_resources/grammar_diagrams#schema-name)NODEPENDSONEXTENSION[extension\_name](/stable/api/ysql/syntax_resources/grammar_diagrams#extension-name) #### alterable\_fn\_and\_proc\_attribute SET[run\_time\_parameter](/stable/api/ysql/syntax_resources/grammar_diagrams#run-time-parameter)TO[value](/stable/api/ysql/syntax_resources/grammar_diagrams#value)=[value](/stable/api/ysql/syntax_resources/grammar_diagrams#value)FROMCURRENTRESET[run\_time\_parameter](/stable/api/ysql/syntax_resources/grammar_diagrams#run-time-parameter)RESETALLEXTERNALSECURITYINVOKERDEFINER #### alterable\_fn\_only\_attribute [volatility](#volatility)[on\_null\_input](#on-null-input)PARALLEL[parallel\_mode](#parallel-mode)NOTLEAKPROOFCOST[int\_literal](/stable/api/ysql/syntax_resources/grammar_diagrams#int-literal)ROWS[int\_literal](/stable/api/ysql/syntax_resources/grammar_diagrams#int-literal) #### volatility IMMUTABLESTABLEVOLATILE #### on\_null\_input CALLEDONNULLINPUTRETURNSNULLONNULLINPUTSTRICT #### parallel\_mode UNSAFERESTRICTEDSAFE 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*](/stable/api/ysql/user-defined-subprograms-and-anon-blocks/subprogram-overloading/#subprogram-call-signature "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](/stable/api/ysql/user-defined-subprograms-and-anon-blocks/subprogram-overloading/ "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](/stable/api/ysql/the-sql-language/statements/ddl_alter_function "ALTER FUNCTION") and [ALTER PROCEDURE](/stable/api/ysql/the-sql-language/statements/ddl_alter_procedure "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): ```sql 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; ``` ## See also - [ALTER FUNCTION](/stable/api/ysql/the-sql-language/statements/ddl_alter_function "ALTER FUNCTION") - [ALTER PROCEDURE](/stable/api/ysql/the-sql-language/statements/ddl_alter_procedure "ALTER PROCEDURE") - [CREATE FUNCTION](/stable/api/ysql/the-sql-language/statements/ddl_create_function "CREATE FUNCTION") - [CREATE PROCEDURE](/stable/api/ysql/the-sql-language/statements/ddl_create_procedure "CREATE PROCEDURE")