ALTER PROCEDURE
For production, use the latest stable version (v2024.1).
User-defined procedures are part of a larger area of functionality. See this major section:
Synopsis
Use the ALTER PROCEDURE
statement to change properties of an existing procedure.
Syntax
alter_procedure ::= ALTER PROCEDURE subprogram_name (
[ subprogram_signature ] )
{ special_fn_and_proc_attribute
| alterable_fn_and_proc_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 }
You must identify the to-be-altered procedure 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 procedure like this:
drop schema if exists s1 cascade;
drop schema if exists s2 cascade;
create schema s1;
create procedure s1.p(i in int)
security definer
language plpgsql
as $body$
begin
execute format('set my_namespace.x = %L', i::text);
end;
$body$;
call s1.p(42);
select current_setting('my_namespace.x')::int as "my_namespace.x";
This is the result:
my_namespace.x
----------------
42
Now suppose you realise that security definer was the wrong choice 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 procedure q() instead of p(); and you want it to be in schema s2 and not in schema s1. You must use three ALTER
statements to do this, thus:
alter procedure s1.p(int)
security invoker
set statement_timeout = 1;
The attempt draws a warning in the current preview version of YugabyteDB, thus:
0A000: ALTER PROCEDURE not supported yet
and the hint refers you to GitHub Issue #2717
In spite of the warning, the attempt actually has the intended effect. You can see this by inspecting the procedure'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,
proconfig as settings
from pg_proc
where
proowner::regrole::text = 'u1' and
proname::text in ('p', 'q');
This is the result:
name | schema | security | settings
------+--------+----------+-----------------------
p | s1 | invoker | {statement_timeout=1}
Now rename the procedure:
alter procedure s1.p(int) rename to q;
You get the 0A000 warning ("not supported yet") again. But, again, you get the intended result. Confirm this by re-running the pg_prpc query:
This is new result:
name | schema | security | settings
------+--------+----------+-----------------------
q | s1 | invoker | {statement_timeout=1}
Now change the schema:
create schema s2;
alter procedure s1.q(int) set schema s2;
This time you get a differently spelled warning:
0A000: ALTER PROCEDURE SET SCHEMA not supported yet
but when you check the procedure's metadata you see, once again, that the schema is actually changed as intended:
name | schema | security | settings
------+--------+----------+-----------------------
q | s2 | invoker | {statement_timeout=1}