NexusDB

Define an SQL-invoked function.

Syntax

Usage

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

Notes

       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.

Examples

       CREATE FUNCTION getFullName

       (

firstName VARCHAR(30),

lastName VARCHAR(30)

       )

       RETURNS VARCHAR(61)

       /*

       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

       BEGIN

DECLARE name VARCHAR(61);

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

RETURN name;

       END

       CREATE FUNCTION getSystemTime ()

       RETURNS TIMESTAMP

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

       RETURN CURRENT_TIMESTAMP;

       CREATE FUNCTION getStudents ()

       RETURNS TABLE

       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

       BEGIN

RETURN SELECT * FROM students;

       END

Conformance