Define an SQL-invoked function.

 

Syntax

 

<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>

 

 

Usage

 

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

 

 

Notes

 

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.



 

 

Examples

 

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)




)


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



 

2)

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



 


CREATE FUNCTION getSystemTime ()


RETURNS TIMESTAMP


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


RETURN CURRENT_TIMESTAMP;



 

3)

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



 


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

 

SQL:2003 standard

 

 

 

NexusDB extensions

-

-

-

 

-

-

Core SQL

Feature B128 "Routine language SQL"

Feature T326 "Table functions"

 

DESCRIPTION

AS clause before the routine body