Define an SQL-invoked function.


<user-defined function> ::=

CREATE FUNCTION [ schema-name. ] function-name

<SQL parameter declaration list>

RETURNS { <data type> | TABLE }

<routine characteristics>

[ DESCRIPTION <character string literal> ]

[ AS ] <routine body>


The CREATE FUNCTION statement creates a user-defined function that is stored in the database.


The schema and procedure names shall conform to the format rules for identifiers and schema-qualified names in NexusDB SQL. The procedure is created in the database specified by schema-name. The current database is implicit if a schema name is not specified.
A parameter mode shall not be specified. All function parameters are implicitly IN parameters.
SQL parameter names shall conform to the rules for identifiers in NexusDB SQL.
The RETURNS clause can either specify the data type of a scalar-valued function result, or TABLE to define a table-valued function that returns a cursor.
LANGUAGE SQL is implicit if not specified. LANGUAGE CLR defines a CLR routine.
NOT DETERMINISTIC is implicit if neither DETERMINISTIC nor NOT DETERMINISTIC is specified.
If an SQL-data access indication is not specified, then CONTAINS SQL is implicit for SQL routines and NO SQL is implicit for CLR routines. The function cannot be called by another routine with a lower SQL-data access level.
CALLED ON NULL INPUT is implicit if the null-call clause is not specified.
DESCRIPTION is a free text attribute used to a store comment in the function descriptor.

       SQL functions shall specify a SQL routine body, and CLR functions shall specify an external body reference.

       The RETURN statement is required in an SQL routine body to specify the result of the function.

       The RETURN clause of a table-valued SQL function shall specify a SELECT statement.


1) The following example creates a function that concatenates two character string values, separated by a space, to form a full name:

       CREATE FUNCTION getFullName


firstName VARCHAR(30),

lastName VARCHAR(30)




       LANGUAGE SQL is implicit

       NOT DETERMINISTIC is implicit

       CONTAINS SQL is implicit


       RETURNS NULL ON NULL INPUT -- We don't invoke the function if any of the arguments are null

       // Multiple statements in the routine body must appear inside a compound statement



SET name = firstName || ' ' || lastName;

RETURN name;


2) The following example creates a function that returns the system date and time:

       CREATE FUNCTION getSystemTime ()


       // A single statement in the routine body doesn't need to appear inside a compound statement


3) The following example creates a function that returns a cursor derived from the students table:

       CREATE FUNCTION getStudents ()


       READS SQL DATA -- We need read-access to execute the SELECT statement

       //  We prefer to use a compound statement in the routine body, even with a single statement





SQL:2003 standard

NexusDB extensions






Core SQL

Feature B128 "Routine language SQL"

Feature T326 "Table functions"


AS clause before the routine body

Home | Site Contents | Documentation | NexusDB Manual V4 | SQL Reference | SQL Statements | Schema Statements