FUNCTION

A stored function is a user-defined function stored in the metadata of a database, and running on the server.Stored functions can be called by stored procedures, stored functions (including the function itself), triggers and DSQL.When a stored function calls itself, such a stored function is called a recursive function.

Unlike stored procedures, stored functions always return a single scalar value.To return a value from a stored functions, use the RETURN statement, which immediately ends the function.

`CREATE FUNCTION`

Creates a stored function

Available in

DSQL

Syntax

CREATE FUNCTION funcname [ ( [ <in_params> ] ) ]
  RETURNS <domain_or_non_array_type> [COLLATE collation]
  [DETERMINISTIC]
  {<psql_function> | <external-module-body>}

<in_params> ::= <inparam> [, <inparam> ... ]

<inparam> ::= <param-decl> [ { = | DEFAULT } <value> ]

<value> ::= { <literal> | NULL | <context-var> }

<param-decl> ::= paramname <domain_or_non_array_type> [NOT NULL]
  [COLLATE collation]

<domain_or_non_array_type> ::=
  !! See Scalar Data Types Syntax !!

<psql_function> ::=
  [SQL SECURITY {INVOKER | DEFINER}]
  <psql-module-body>

<psql-module-body> ::=
  !! See Syntax of Module Body !!

<external-module-body> ::=
  !! See Syntax of Module Body !!

Table 1. `CREATE FUNCTION` Statement Parameters
Parameter	Description
funcname	Stored function name.The maximum length is 63 characters.Must be unique among all function names in the database.
inparam	Input parameter description
collation	Collation
literal	A literal value that is assignment-compatible with the data type of the parameter
context-var	Any context variable whose type is compatible with the data type of the parameter
paramname	The name of an input parameter of the function.The maximum length is 63 characters.The name of the parameter must be unique among input parameters of the function and its local variables.

The CREATE FUNCTION statement creates a new stored function.The stored function name must be unique among the names of all stored and external (legacy) functions, excluding sub-functions or functions in packages.For sub-functions or functions in packages, the name must be unique within its module (package, stored procedure, stored function, trigger).

Note

It is advisable to not reuse function names between global stored functions and stored functions in packages, although this is legal.At the moment, it is not possible to call a function or procedure from the global namespace from inside a package, if that package defines a function or procedure with the same name.In that situation, the function or procedure of the package will be called.

CREATE FUNCTION is a compound statement with a header and a body.The header defines the name of the stored function, and declares input parameters and return type.

The function body consists of optional declarations of local variables, named cursors, and subroutines (sub-functions and sub-procedures), and one or more statements or statement blocks, enclosed in an outer block that starts with the keyword BEGIN and ends with the keyword END.Declarations and statements inside the function body must be terminated with a semicolon (‘;’).