--- title: "CREATE PROCEDURE" url: "https://docs.yugabyte.com/stable/api/ysql/the-sql-language/statements/ddl_create_procedure/" --- # CREATE PROCEDURE Use the CREATE PROCEDURE statement to create a procedure in a database. 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 `CREATE PROCEDURE` statement to create a procedure in a database. ## Syntax - [![Grammar Icon](/icons/file-lines.svg)Grammar](#grammar-1) - [![Diagram Icon](/icons/diagram.svg)Diagram](#diagram-1) ```output.ebnf create_procedure ::= CREATE [ OR REPLACE ] PROCEDURE subprogram_name ( [ arg_decl_with_dflt [ , ... ] ] ) { unalterable_proc_attribute | alterable_fn_and_proc_attribute } [ ... ] arg_decl_with_dflt ::= arg_decl [ { DEFAULT | = } expression ] arg_decl ::= [ formal_arg ] [ arg_mode ] arg_type subprogram_signature ::= arg_decl [ , ... ] unalterable_proc_attribute ::= LANGUAGE lang_name | AS subprogram_implementation lang_name ::= SQL | PLPGSQL | C subprogram_implementation ::= ' sql_stmt_list ' | ' plpgsql_block_stmt ' | ' obj_file ' [ , ' link_symbol ' ] sql_stmt_list ::= sql_stmt ; [ sql_stmt ... ] alterable_fn_and_proc_attribute ::= SET run_time_parameter { TO value | = value | FROM CURRENT } | RESET run_time_parameter | RESET ALL | [ EXTERNAL ] SECURITY { INVOKER | DEFINER } ``` #### create\_procedure CREATEORREPLACEPROCEDURE[subprogram\_name](/stable/api/ysql/syntax_resources/grammar_diagrams#subprogram-name)(,[arg\_decl\_with\_dflt](#arg-decl-with-dflt))[unalterable\_proc\_attribute](#unalterable-proc-attribute)[alterable\_fn\_and\_proc\_attribute](#alterable-fn-and-proc-attribute) #### arg\_decl\_with\_dflt [arg\_decl](#arg-decl)DEFAULT=[expression](/stable/api/ysql/syntax_resources/grammar_diagrams#expression) #### 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) #### subprogram\_signature ,[arg\_decl](#arg-decl) #### unalterable\_proc\_attribute LANGUAGE[lang\_name](#lang-name)AS[subprogram\_implementation](#subprogram-implementation) #### lang\_name SQLPLPGSQLC #### subprogram\_implementation '[sql\_stmt\_list](#sql-stmt-list)''[plpgsql\_block\_stmt](/stable/api/ysql/syntax_resources/grammar_diagrams#plpgsql-block-stmt)''[obj\_file](/stable/api/ysql/syntax_resources/grammar_diagrams#obj-file)','[link\_symbol](/stable/api/ysql/syntax_resources/grammar_diagrams#link-symbol)' #### sql\_stmt\_list [sql\_stmt](/stable/api/ysql/syntax_resources/grammar_diagrams#sql-stmt);[sql\_stmt](/stable/api/ysql/syntax_resources/grammar_diagrams#sql-stmt); #### 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 'create procedure' and the 'subprogram\_signature' rule. When you write a `CREATE PROCEDURE` statement, you will already have decided what formal arguments it will have—i.e. for each, what will be its name, mode, data type, and optionally its default value. When, later, you alter or drop a procedure, you must identify it. You do this, in [`ALTER PROCEDURE`](/stable/api/ysql/the-sql-language/statements/ddl_alter_procedure/ "ALTER PROCEDURE") and [`DROP PROCEDURE`](/stable/api/ysql/the-sql-language/statements/ddl_drop_procedure/ "DROP PROCEDURE"), typically by specifying just its [*subprogram\_call\_signature*](/stable/api/ysql/user-defined-subprograms-and-anon-blocks/subprogram-overloading/#subprogram-call-signature "subprogram_call_signature"). You are allowed to use the full *subprogram\_signature*. But this is unconventional. Notice that the *subprogram\_signature* does not include the optional specification of default values; and you *cannot* mention these when you alter or drop a procedure. The distinction between the *subprogram\_signature* and the *subprogram\_call\_signature* is discussed carefully in the section [Subprogram overloading](/stable/api/ysql/user-defined-subprograms-and-anon-blocks/subprogram-overloading/ "Subprogram overloading"). ## Semantics - The meanings of the various procedure attributes are explained in the section [Subprogram attributes](/stable/api/ysql/user-defined-subprograms-and-anon-blocks/subprogram-attributes/ "Subprogram attributes"). - A procedure, like other schema objects such as a table, inevitably has an owner. You cannot specify the owner explicitly when a procedure is created. Rather, it's defined implicitly as what the *current\_user* built-in function returns when it's invoked in the session that creates the procedure. This user must have the *usage* privilege on the procedure's schema and its argument data types. You (optionally) specify the procedure's schema and (mandatorily) its name within its schema as the argument of the [*subprogram\_name*](/stable/api/ysql/syntax_resources/grammar_diagrams/#subprogram-name "subprogram_name") rule. - If a procedure with the given name, schema, and argument types already exists then `CREATE PROCEDURE` will draw an error unless the `CREATE OR REPLACE PROCEDURE` variant is used. - Procedures with different [*subprogram\_call\_signatures*](/stable/api/ysql/user-defined-subprograms-and-anon-blocks/subprogram-overloading/#subprogram-call-signature "subprogram_call_signatures") can share the same [*subprogram\_name*](/stable/api/ysql/syntax_resources/grammar_diagrams/#subprogram-name "subprogram_name"). (The same holds for functions.) See section [Subprogram overloading](/stable/api/ysql/user-defined-subprograms-and-anon-blocks/subprogram-overloading/ "Subprogram overloading"). - `CREATE OR REPLACE PROCEDURE` doesn't change permissions that have been granted on an existing procedure. To use this statement, the current user must own the procedure, or be a member of the role that owns it. - In contrast, if you drop and then recreate a procedure, the new procedure is not the same entity as the old one. So you will have to drop existing objects that depend upon the old procedure. (Dropping the procedure `CASCADE` achieves this.) Alternatively, `ALTER PROCEDURE` can be used to change most of the auxiliary attributes of an existing procedure. - The languages supported by default are `SQL`, `PLPGSQL` and `C`. 'Create procedure' grants 'execute' to 'public'. *Execute* is granted automatically to *public* when you create a new procedure. This is very unlikely to be want you want—and so this behavior presents a disguised security risk. Yugabyte recommends that your standard practice be to revoke this privilege immediately after creating a procedure. You cannot set the 'depends on extension' attribute with 'create procedure'. A procedure's *depends on extension* attribute cannot be set using `CREATE [OR REPLACE] PROCEDURE`. You must use [`ALTER PROCEDURE`](/stable/api/ysql/the-sql-language/statements/ddl_alter_procedure "ALTER PROCEDURE") to set it. ## Examples - Set up an accounts table. ```plpgsql create schema s; create table s.accounts ( id integer primary key, name text not null, balance decimal(15,2) not null); insert into s.accounts values (1, 'Jane', 100.00); insert into s.accounts values (2, 'John', 50.00); select * from s.accounts order by 1; ``` ```output id | name | balance ----+------+--------- 1 | Jane | 100.00 2 | John | 50.00 (2 rows) ``` - Define a *transfer* procedure to transfer money from one account to another. ```plpgsql create or replace procedure s.transfer(from_id in int, to_id in int, amnt in decimal) security definer set search_path = pg_catalog, pg_temp language plpgsql as $body$ begin if amnt <= 0.00 then raise exception 'The transfer amount must be positive'; end if; if from_id = to_id then raise exception 'Sender and receiver cannot be the same'; end if; update s.accounts set balance = balance - amnt where id = from_id; update s.accounts set balance = balance + amnt where id = to_id; end; $body$; ``` - Transfer $20.00 from Jane to John. ```plpgsql call s.transfer(1, 2, 20.00); select * from s.accounts order by 1; ``` ```output id | name | balance ----+------+--------- 1 | Jane | 80.00 2 | John | 70.00 (2 rows) ``` - Errors will be thrown for unsupported argument values. ```plpgsql CALL s.transfer(2, 2, 20.00); ``` ```output ERROR: Sender and receiver cannot be the same ``` ```plpgsql call s.transfer(1, 2, -20.00); ``` ```output ERROR: The transfer amount must be positive ``` Always name the formal arguments and write the procedure's body last. YSQL inherits from PostgreSQL the ability to specify the arguments only by listing their data types and to reference them in the body using the positional notation `$1`, `$2`, and so on. The earliest versions of PostgreSQL didn't allow named parameters. But the version that YSQL is based on does allow this. Your code will be very much easier to understand if you use named arguments like the example does. The syntax rules allow you to write the alterable and unalterable attributes in any order, like this: ```plpgsql create or replace procedure s.transfer(from_id in int, to_id in int, amnt in decimal) security definer as $body$ begin if amnt <= 0.00 then raise exception 'The transfer amount must be positive'; end if; if from_id = to_id then raise exception 'Sender and receiver cannot be the same'; end if; update s.accounts set balance = balance - amnt where id = from_id; update s.accounts set balance = balance + amnt where id = to_id; end; $body$ set search_path = pg_catalog, pg_temp language plpgsql; ``` Yugabyte recommends that you avoid exploiting this freedom and choose a standard order where, especially, you write the body last. (Try the \\*sf* meta-command for a procedure that you created. It always shows the source text last, no matter what order your *create \[or replace]* used.) For example, it helps readability to specify the language immediately before the body. Following this practice will allow you to review, and discuss, your code in a natural way by distinguishing, informally, between the procedure *header* (i.e. everything that comes before the body) and the *implementation* (i.e. the body). ## See also - [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") - [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")