--- title: "Create function" url: "https://docs.yugabyte.com/stable/api/ysql/the-sql-language/statements/ddl_create_function/" --- # Create function Use the CREATE FUNCTION statement to create a function in a database. 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: - [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 FUNCTION` statement to create a function in a database. ## Syntax - [![Grammar Icon](/icons/file-lines.svg)Grammar](#grammar-1) - [![Diagram Icon](/icons/diagram.svg)Diagram](#diagram-1) ```output.ebnf create_function ::= CREATE [ OR REPLACE ] FUNCTION subprogram_name ( [ arg_decl_with_dflt [ , ... ] ] ) { RETURNS data_type | RETURNS TABLE ( { column_name data_type } [ , ... ] ) } { unalterable_fn_attribute | alterable_fn_only_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_fn_attribute ::= WINDOW | 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 } 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 ``` #### create\_function CREATEORREPLACEFUNCTION[subprogram\_name](/stable/api/ysql/syntax_resources/grammar_diagrams#subprogram-name)(,[arg\_decl\_with\_dflt](#arg-decl-with-dflt))RETURNS[data\_type](/stable/api/ysql/syntax_resources/grammar_diagrams#data-type)RETURNSTABLE(,[column\_name](/stable/api/ysql/syntax_resources/grammar_diagrams#column-name)[data\_type](/stable/api/ysql/syntax_resources/grammar_diagrams#data-type))[unalterable\_fn\_attribute](#unalterable-fn-attribute)[alterable\_fn\_only\_attribute](#alterable-fn-only-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\_fn\_attribute WINDOWLANGUAGE[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 #### alterable\_fn\_only\_attribute [volatility](#volatility)[on\_null\_input](#on-null-input)PARALLEL[parallel\_mode](#parallel-mode)NOTLEAKPROOFCOST[int\_literal](/stable/api/ysql/syntax_resources/grammar_diagrams#int-literal)ROWS[int\_literal](/stable/api/ysql/syntax_resources/grammar_diagrams#int-literal) #### volatility IMMUTABLESTABLEVOLATILE #### on\_null\_input CALLEDONNULLINPUTRETURNSNULLONNULLINPUTSTRICT #### parallel\_mode UNSAFERESTRICTEDSAFE 'Regard the 'RETURNS' clause as mandatory. The [general introduction](/stable/api/ysql/user-defined-subprograms-and-anon-blocks/#user-defined-subprograms "general introduction") to the topic of user-defined subprograms explains that, for historical reasons, you can create a function, when a creating a procedure is the proper choice, by omitting the `RETURNS` clause and by giving it `OUT` or `INOUT` arguments. **Yugabyte recommends that you don't exploit this freedom.** For this reason, the `RETURNS` clause is shown as mandatory in the *create\_function* syntax rule (though the PostgreSQL documentation shows it as optional). **Yugabyte further recommends that you avoid declaring `out` or `inout` arguments for a function.** When the purpose is to return more than just a single scalar value, you should create a dedicated composite type for the purpose and use this to declare the function's return value. 'create function' and the 'subprogram\_signature' rule. When you write a `CREATE FUNCTION` 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 function, you must identify it. You do this, in [`ALTER FUNCTION`](/stable/api/ysql/the-sql-language/statements/ddl_alter_function/ "ALTER FUNCTION") and [`DROP FUNCTION`](/stable/api/ysql/the-sql-language/statements/ddl_drop_function/ "DROP FUNCTION"), 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 function. 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 function attributes are explained in the section [Subprogram attributes](/stable/api/ysql/user-defined-subprograms-and-anon-blocks/subprogram-attributes/ "Subprogram attributes"). - A function, like other schema objects such as a table, inevitably has an owner. You cannot specify the owner explicitly when a function is created. Rather, it's defined implicitly as what the built-in *current\_user* function returns when it's invoked in the session that creates the function. This user must have the *usage* privilege on the function's schema, its argument data types, and its return data type. You (optionally) specify the function'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 function with the given name, schema, and argument types already exists then `CREATE FUNCTION` will draw an error unless the `CREATE OR REPLACE FUNCTION` variant is used. - Functions 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 procedures.) See section [Subprogram overloading](/stable/api/ysql/user-defined-subprograms-and-anon-blocks/subprogram-overloading/ "Subprogram overloading"). - `CREATE OR REPLACE FUNCTION` doesn't change permissions that have been granted on an existing function. To use this statement, the current user must own the function, or be a member of the role that owns it. - In contrast, if you drop and then recreate a function, the new function is not the same entity as the old one. So you will have to drop existing objects that depend upon the old function. (Dropping the function `CASCADE` achieves this.) Alternatively, `ALTER FUNCTION` can be used to change most of the attributes of an existing function. - The languages supported by default are `SQL`, `PLPGSQL` and `C`. 'Create function' grants 'execute' to 'public'. *Execute* is granted automatically to *public* when you create a new function. 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 function. You cannot set the 'depends on extension' attribute with 'create function'. A function's *depends on extension* attribute cannot be set using `CREATE [OR REPLACE] FUNCTION`. You must use [`ALTER FUNCTION`](/stable/api/ysql/the-sql-language/statements/ddl_alter_function "ALTER FUNCTION") to set it. ### Scalar functions and table functions Use the appropriate variant of the `RETURN` clause to create either a scalar function or a table function. Notice that *scalar* can denote not just an atomic value of data types like *int*, *numeric*, *text*, and so on (and domains based on those data types); it can also denote a single composite value like that of a user-defined type. #### Scalar function example Try this: ```plpgsql create schema s; create type s.x as (i int, t text); create function s.f(i in int, t in text) returns x security invoker set search_path = pg_catalog, pg_temp language plpgsql as $body$ begin return (i*2, t||t)::s.x; end; $body$; WITH c as (select s.f(42, 'dog') as v) select (v).i, (v).t FROM c; ``` This is the result: ```output i | t ----+-------- 84 | dogdog ``` #### Table function example: Try this ```plpgsql create table s.t(k serial primary key, v text); insert into s.t(v) values ('dog'), ('cat'), ('frog'); create function s.f() returns table(z text) security definer set search_path = pg_catalog, pg_temp language plpgsql as $body$ begin z := 'Starting content of t '; return next; z := '----------------------'; return next; for z in (select v from s.t order by k) loop return next; end loop; begin insert into s.t(v) values ('mouse'); exception when string_data_right_truncation then z := ''; return next; z := 'string_data_right_truncation caught'; return next; end; insert into s.t(v) values ('bird'); z := ''; return next; z := 'Finishing content of t'; return next; z := '----------------------'; return next; for z in (select v from s.t order by k) loop return next; end loop; end; $body$; \t on select z from s.f(); \t off ``` This is the result: ```output Starting content of t ---------------------- dog cat frog string_data_right_truncation caught Finishing content of t ---------------------- dog cat frog bird ``` This kind of table function provides a convenient way to produce an arbitrarily formatted report that can easily be spooled to a file. This is because the *select* output is easily accessible (in *ysqlsh*) on *stdout*—and it's correspondingly easily accessible in client-side programming languages that do SQL like say, Python. In contrast, the output from *raise info* is tricky to capture (and definitely very hard to interleave in proper sequence with *select* results) because it comes on *stderr*. Always name the formal arguments and write the function'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 function s.f(i in int, t in text) returns s.x security invoker as $body$ begin return (i*2, t||t)::s.x; 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. For example, it helps readability to specify the language immediately before the body. (Try the \\*sf* meta-command for a function that you created. It always shows the source text last, no matter what order your *create \[or replace]* used.) Following this practice will allow you to review, and discuss, your code in a natural way by distinguishing, informally, between the function *header* (i.e. everything that comes before the body) and the *implementation* (i.e. the body). ## See also - [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") - [CREATE PROCEDURE](/stable/api/ysql/the-sql-language/statements/ddl_create_procedure "CREATE PROCEDURE") - [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")