SQL UDF Create Function ​

CREATE FUNCTION defines a new function. CREATE OR REPLACE FUNCTION will either create a new function, or replace an existing definition. The user that creates the function becomes the owner of the function.

Enabling SQL UDF ​

Prerequisites for enabling SQL UDF feature are:

• The configuration parameter ybd_allow_udf_creation must be set.
• The current user should have EXECUTE privilege on the function. To know how to grant permission using GRANT EXECUTE statement, Click Here.

Syntax ​

CREATE [ OR REPLACE ] FUNCTION
    name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
    [ RETURNS rettype ]
LANGUAGE SQL
{
    | IMMUTABLE | STABLE | VOLATILE
    | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
    | AS 'definition'
}

name

The name of the function to create.

argmode

The mode of an argument: IN, OUT, INOUT, or VARIADIC. If omitted, the default is IN. Only an OUT argument can follow a VARIADIC one.

argname

The name of an argument. The name of an output argument is significant because it defines the column name in the result row type. If the output argument’s name is omitted, the system will assign a default column name.

argtype

The data type of the argument. The argument types can be base data types.

default_expr

An expression to be used as default value if the parameter is not specified. The expression has to be coercible to the argument type of the parameter. Only input (including INOUT) parameters can have a default value. All input parameters following a parameter with a default value must have default values as well.

rettype

The return data type (optionally with a schema). It can be an integer or reference a table column’s type. If the function doesn’t return a value, use void as the return type.

When there are OUT or INOUT parameters, the RETURNS clause can be omitted. If present, it must agree with the result type implied by the output parameters.

IMMUTABLE
STABLE
VOLATILE
These attributes inform the query optimizer about the behavior of the function. At most one choice can be specified. If none of these appear, VOLATILE is the default assumption.

IMMUTABLE indicates that the function cannot modify the database and always returns the same result when given the same argument values; that is, it does not do database lookups or otherwise use information not directly present in its argument list. If this option is given, any call of the function with all-constant arguments can be immediately replaced with the function value.

STABLE indicates that the function does not change the database. It will always give the same result for the same inputs during a single table scan. However, the result could vary between different SQL statements. This is the appropriate selection for functions whose results depend on database lookups, parameter variables (such as the current time zone), etc. Also note that the current_timestamp family of functions qualify as stable, since their values do not change within a transaction.

VOLATILE indicates that the function value can change even within a single table scan, so no optimizations can be made. Relatively few database functions are volatile in this sense; some examples are random(), timeofday().

CALLED ON NULL INPUT
RETURNS NULL ON NULL INPUT
STRICT
CALLED ON NULL INPUT (the default) indicates that the function will be called normally when some of its arguments are null. It is then the function author's responsibility to check for null values if necessary and respond appropriately.

RETURNS NULL ON NULL INPUT or STRICT indicates that the function always returns null whenever any of its arguments are null. If this parameter is specified, the function is not executed when there are null arguments; instead a null result is assumed automatically.

Definition ​

A string constant defining the function. It can be an internal function name or an SQL command.

It is often helpful to use dollar quoting ($$) to write the function definition string, rather than the normal single quote syntax. Without dollar quoting, any single quotes or backslashes in the function definition must be escaped by doubling them.

For SQL UDF examples, Click Here.

Function Overloading ​

Function overloading is a feature in Yellowbrick that allows you to define multiple functions with the same name but different parameters. For more details, refer SQL UDF Function Overloading.