---
title: "ALTER PROCEDURE"
url: "https://docs.yugabyte.com/stable/api/ysql/the-sql-language/statements/ddl_alter_procedure/"
---

# ALTER PROCEDURE

Change properties of an existing procedure.

See the dedicated 'User-defined subprograms and anonymous blocks' section.

User-defined 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 PROCEDURE` statement to change properties of an existing procedure.

## Syntax

- [![Grammar Icon](/icons/file-lines.svg)Grammar](#grammar-1)
- [![Diagram Icon](/icons/diagram.svg)Diagram](#diagram-1)

```output.ebnf
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 }
```

#### alter\_procedure

ALTERPROCEDURE[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)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

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*](/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 procedure like this:

```plpgsql
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:

```output
 my_namespace.x
----------------
             42
```

Now suppose you want to change the security attribute, set a configuration parameter, rename the procedure, and move it to a different schema. You must use separate `ALTER` statements for each change:

```plpgsql
alter procedure s1.p(int) security invoker;
alter procedure s1.p(int) set statement_timeout = '1s';
alter procedure s1.p(int) rename to q;
```

Create a schema to move the procedure to:

```plpgsql
create schema s2;
alter procedure s1.q(int) set schema s2;
```

You can verify the changes by querying the procedure'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,
  proconfig                         as settings
from pg_proc
where
  proname::text in ('p', 'q')
order by proname;
```

This shows the updated procedure:

```output
 name | schema | security |       settings
------+--------+----------+-----------------------
 q    | s2     | invoker  | {statement_timeout=1s}
```

### Mark extension dependencies

Use `DEPENDS ON EXTENSION` to mark a procedure as dependent on an extension:

```sql
CREATE EXTENSION "uuid-ossp";

-- Mark the procedure as dependent on the extension
ALTER PROCEDURE s2.q(int) DEPENDS ON EXTENSION "uuid-ossp";

-- Remove the dependency mark if needed
ALTER PROCEDURE s2.q(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 PROCEDURE](/stable/api/ysql/the-sql-language/statements/ddl_create_procedure "CREATE PROCEDURE")
- [DROP PROCEDURE](/stable/api/ysql/the-sql-language/statements/ddl_drop_procedure "DROP PROCEDURE")
- [CREATE FUNCTION](/stable/api/ysql/the-sql-language/statements/ddl_create_function "CREATE FUNCTION")
- [ALTER FUNCTION](/stable/api/ysql/the-sql-language/statements/ddl_alter_function "ALTER FUNCTION")
- [DROP FUNCTION](/stable/api/ysql/the-sql-language/statements/ddl_drop_function "DROP FUNCTION")