--- title: "ALTER FUNCTION" url: "https://docs.yugabyte.com/stable/api/ysql/the-sql-language/statements/ddl_alter_function/" --- # ALTER FUNCTION Change properties of an existing function. See the dedicated 'User-defined subprograms and anonymous blocks' section. User-defined functions 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 FUNCTION` statement to change properties of an existing function. ## Syntax - [![Grammar Icon](/icons/file-lines.svg)Grammar](#grammar-1) - [![Diagram Icon](/icons/diagram.svg)Diagram](#diagram-1) ```output.ebnf alter_function ::= ALTER FUNCTION 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\_function ALTERFUNCTION[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 to-be-altered function 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"). ## Semantics This is explained in the section [Subprogram attributes](/stable/api/ysql/user-defined-subprograms-and-anon-blocks/subprogram-attributes/ "Subprogram attributes"). ## Example Supposed that you create a function like this: ```plpgsql drop schema if exists s3 cascade; drop schema if exists s4 cascade; create schema s3; create function s3.f(i in int) returns text security definer volatile language plpgsql as $body$ begin return 'Result: '||(i*2)::text; end; $body$; select s3.f(17) as "s3.f(17)"; ``` This is the result: ```output s3.f(17) ------------ Result: 34 ``` Now suppose you realise that *security definer* was the wrong choice, that you want to mark it *immutable*, and that you want to set the *statement\_timeout* attribute (never mind that this is unrealistic here). Suppose, too, that: you want to call the function *g()* instead of *f()*; and you want it to be in schema *s4* and not in schema *s3*. You must use three `ALTER` statements to do this, thus: ```plpgsql alter function s3.f(int) security invoker immutable set statement_timeout = 1; ``` Check the effect by inspecting the function's metadata. See the section [The "pg\_proc" catalog table for subprograms](/stable/api/ysql/user-defined-subprograms-and-anon-blocks/pg-proc-catalog-table/ 'The "pg_proc" catalog table for subprograms') for information on how to query subprogram metadata. ```plpgsql select proname::text as name, pronamespace::regnamespace::text as schema, case when prosecdef then 'definer' else 'invoker' end as security, case when provolatile = 'v' then 'volatile' when provolatile = 's' then 'stable' when provolatile = 'i' then 'immutable' end as volatility, proconfig as settings from pg_proc where proowner::regrole::text = 'u1' and proname::text in ('f', 'g'); ``` This is the result: ```output name | schema | security | volatility | settings ------+--------+----------+------------+----------------------- f | s3 | invoker | immutable | {statement_timeout=1} ``` Now rename the function: ```plpgsql alter function s3.f(int) rename to g; ``` Check the result by re-running the *pg\_prpc* query. This is new result: ```output name | schema | security | volatility | settings ------+--------+----------+------------+----------------------- g | s3 | invoker | immutable | {statement_timeout=1} ``` Now change the schema: ```plpgsql create schema s4; alter function s3.g(int) set schema s4; ``` Check the result by re-running the *pg\_prpc* query. This is new result: ```output name | schema | security | volatility | settings ------+--------+----------+------------+----------------------- g | s4 | invoker | immutable | {statement_timeout=1} ``` ### Mark extension dependencies Use `DEPENDS ON EXTENSION` to mark a function as dependent on an extension: ```sql CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; -- Create a function that depends on the extension CREATE FUNCTION s4.g(int) RETURNS text SECURITY DEFINER LANGUAGE plpgsql AS $$BEGIN RETURN 'Result'; END$$; -- Mark the dependency on the extension ALTER FUNCTION s4.g(int) DEPENDS ON EXTENSION "uuid-ossp"; -- Remove the dependency mark ALTER FUNCTION s4.g(int) NO DEPENDS ON EXTENSION "uuid-ossp"; ``` When an extension is dropped, all dependent objects are dropped or updated accordingly. ## See also - [ALTER ROUTINE](/stable/api/ysql/the-sql-language/statements/ddl_alter_routine "ALTER ROUTINE") - [CREATE FUNCTION](/stable/api/ysql/the-sql-language/statements/ddl_create_function "CREATE FUNCTION") - [DROP FUNCTION](/stable/api/ysql/the-sql-language/statements/ddl_drop_function "DROP FUNCTION") - [CREATE PROCEDURE](/stable/api/ysql/the-sql-language/statements/ddl_create_procedure "CREATE PROCEDURE") - [ALTER PROCEDURE](/stable/api/ysql/the-sql-language/statements/ddl_alter_procedure "ALTER PROCEDURE") - [DROP PROCEDURE](/stable/api/ysql/the-sql-language/statements/ddl_drop_procedure "DROP PROCEDURE")