ALTER 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:
Synopsis
Use the ALTER FUNCTION
statement to change properties of an existing function.
Syntax
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 ::= [ arg_name ] [ 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
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 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 is sufficient; and this is typically used. You can use the full subprogram_signature. But you should realize that the arg_name 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.
Semantics
This is explained in the section Subprogram attributes.
Example
Supposed that you create a function like this:
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:
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:
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 for information on how to query subprogram metadata.
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:
name | schema | security | volatility | settings
------+--------+----------+------------+-----------------------
f | s3 | invoker | immutable | {statement_timeout=1}
Now rename the function:
alter function s3.f(int) rename to g;
Check the result by re-running the pg_prpc query. This is new result:
name | schema | security | volatility | settings
------+--------+----------+------------+-----------------------
g | s3 | invoker | immutable | {statement_timeout=1}
Now change the schema:
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:
name | schema | security | volatility | settings
------+--------+----------+------------+-----------------------
g | s4 | invoker | immutable | {statement_timeout=1}