Try a preview of AI-powered search below!

Synopsis

Use the ALTER FUNCTION statement to change properties of an existing function.

Syntax

• Grammar
• Diagram

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

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 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.

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}

See also

• CREATE FUNCTION
• DROP FUNCTION
• CREATE PROCEDURE
• ALTER PROCEDURE
• DROP PROCEDURE