Use the CREATE FUNCTION statement to create user-defined stored functions that are written in the solidDB proprietary SQL procedure language (internal functions) or written in C programming language (external functions).
You can use the stored SQL functions to expand the server functionality by writing functions that behave in the same way as built-in functions.
Stored functions accept literals, variables, variable markers ("?") or column names as input parameters. Output parameters are not supported.
External functions are used in the same way as built-in or internal functions. solidDB accesses the external functions by using the standard dynamic library interface (DLLs in Windows, shared libraries in Linux and UNIX). The control of the thread is passed to the external library routine until it returns. Furthermore, the database interface of the external functions is similar to that of an ODBC application, except that the connection handle (hdbc) is passed to the external routine, and it can immediately start using it without establishing the connection.
To enable use of external functions, you must link your application dynamically to solidDB by using Shared Memory Access (SMA) or Linked Library Access (LLA).
An external function is a scalar function, which returns a single value each time it is called.
Parameters, clauses, keywords, and variables
▪ function‑name: Identifies the function name. The function name can also include the catalog name and schema:
[catalog‑name[.schema].]function‑name
The name must be a valid SQL identifier. The function name must be unique within the schema. Although not prohibited, the user-defined function should not have the same name as a built-in function.
▪ param‑definition: Identifies the input parameters of the function, and specifies the name and data type, and optional default value of each parameter. Specify an entry in the list for each parameter that the function expects to receive. Up to 90 parameters can be specified.
Note You can create a function that has no parameters. For example: CREATE FUNCTION howdy() RETURNS VARCHAR20 …
– data‑type: A valid solidDB data type, see data-type.
– literal: Defines the default value of the parameter. The value must be a literal.
Stored functions accept literals, variables, variable markers ("?") or column names as input parameters. If a column name is used, the column value is retrieved from the current row variable instance (for example in the WHERE clause). Output parameters are not supported.
▪ RETURN[S]data‑type: Identifies the data type of for the output of the function.
– data‑type must be a valid solidDB data type, see data-type.
▪ SQL‑data‑access‑indication: Defines whether the function executes any SQL statements and, if so, what type.
– CONTAINS SQL: Indicates that SQL statements that do not read or modify SQL data can be executed by the function.
– READS SQL DATA: Indicates that only read-only SQL statements can be executed by the function.
– NO SQL: Indicates that the function cannot execute any SQL statements.
Note The validity of the contents of the function are not checked against the declared data access indication.
▪ int-function-definition
– LANGUAGE SQL: This optional clause indicates that the CREATE FUNCTION statement is being used to register a new function written in solidDB SQL procedure language.
– function‑body: The function body must be a valid solidDB SQL procedure statement. See CREATE PROCEDURE for more details.
Restrictions
▪ The returned value cannot be a complex expression; it can be only a variable or a literal.
▪ A COMMIT statement is not allowed within a function.
▪ RETURNS NULL ON NULL INPUT is not supported. If any actual parameter is NULL, a NULL function value is forced automatically.
▪ ext-function-definition
– LANGUAGE C: Indicates that the CREATE FUNCTION statement is being used to register a new function that is based on code that is written in the C programming language.
– EXTERNAL NAME'ext‑function‑lib‑identifier': Identifies the name of the user-written code that implements the function. The name must be enclosed by single quotation marks. Extraneous blanks are not permitted.
– [path]library‑name: Identifies the path (optional) and name of the function. If you specify an absolute path, you must append the file extension of the library file. If you do not specify the file extension, you can port and use the library on several platforms, however, you might need to add the location of the library to the LD_LIBRARY_PATH or LIBPATH (Linux and UNIX) or PATH (Windows) environment variable.
– !C‑routine‑name: Identifies the entry point name of the C routine (function) to be invoked. The exclamation point (!) serves as a delimiter between the library name and the routine name. For example, '!proc8' would direct solidDB to look for the library in the location specified by [path]library‑name, and to use entry point proc8 within that library.
Notes
▪ On Windows 32-bit systems, use the calling convention _cdecl for calling the C routines. This allows the checking and handling of varying argument counts. Also, ensure that your DLL exports the routines you want to call: for example, include the routines in the corresponding .def file in your DLL project.
▪ The library (and the function within the library) does not need to exist when the CREATE FUNCTION statement is executed. However, when the function is used, the library and function within the library must exist and be accessible.
Restrictions
▪ The call interface of the external functions is assumed to be non-fenced and thread-safe.
▪ All functions are assumed non-deterministic. The keyword [NOT] DETERMINISTIC is allowed in the syntax but the setting DETERMINISTIC is not effective.
▪ COMMIT [WORK] is not allowed within a function.
▪ Only one parameter passing convention is available; the return value is passed as an implicit output parameter that comes after the input parameters to the called C function.
▪ You should not start new threads that execute in parallel with the called function; unlike an ODBC handle, the provided connection handle is not multithread safe. If new threads are launched, they must create new connections. If the new thread tries to share the same connection as the function itself, the error, SQLException: Invalid Handle, is returned.
Return values
▪ SQL_ERROR
▪ SQL_SUCCESS
Examples
A simple internal function
Creating the function:
CREATE FUNCTION hello (s VARCHAR(20)) RETURNS VARCHAR(50) BEGIN RETURN 'Hello'; END
Using the function:
SELECT hello('world');
HELLO('world') ------------- Hello
An internal function that returns week numbers
Creating the function:
CREATE FUNCTION week_number (s TIMESTAMP) RETURNS INTEGER BEGIN DECLARE week INTEGER; week := floor(( dayofyear(s) - dayofweek(s) + 7)/7); RETURN week; END
Using the function:
SELECT quota, week_number(sales_date)AS week, week_number(now()) AS current_week FROM sales ORDER BY quota DESC LIMIT 1;
QUOTA WEEK CURRENT_WEEK ----------------------- 467 23 43 SELECT quota FROM sales WHERE current_week(sales_date) = 23 ORDER BY quota DESC LIMIT 1;
QUOTA ----- 467
INSERT INTO report VALUES (?, week_number(now()), week_number(?));
An external function that calculates the volume of a cube
The following example registers the external function EXTFUNCCUBEVOLUME, which calculates the volume of a cube by using the length of the edge of the cube as an input parameter.
C Program:
#include <solidodbc3.h>
int extfunccubevolume( SQLHDBC hdbc, /* not used in this function */ char sqlst[6], char qualName[], char diagMsg[71], SQLULEN nArgs, SQLLEN nullind[2], double* edge, /* input */ double* retval, /* return value */
{ if (nullind[0] == SQL_NULL_DATA) { /* NULL input implies NULL return value */ nullind[1] = SQL_NULL_DATA; return (SQL_SUCCESS); } if (*edge < 0.0) { /* error: edge cannot be < 0 */ strcpy(diagMsg, "extfunccubevolume: parameter 'edge' cannot be < 0"); strcpy(sqlst, "22003"); /* Numeric values out of range */ return (SQL_ERROR); } *retval = (*edge) * (*edge) * (*edge); nullind[1] = sizeof(double); return (SQL_SUCCESS); }
Creating the function:
CREATE FUNCTION EXTFUNCCUBEVOLUME (edge float) RETURN FLOAT LANGUAGE C EXTERNAL NAME 'examplelib1!extfunccubevolume'
Using the function:
SELECT extfunccubevolume(edge) FROM cubic_containers WHERE material = 'STEEL';