CREATE FUNCTION

Creates a new stored user-defined function.

 

where function-definition is:

 

Usage

Embedded, Interactive, Module, ODBC, JDBC.

Description

The function-name should follow the normal rules for naming database objects, see Naming Objects.

If function-name is specified in its unqualified form, the function will be created in the schema which has the same name as the current ident.

If function-name is specified in its fully qualified form (i.e. schema-name.function-name) the function will be created in the named schema (in this case, the current ident must be the creator of the specified schema).

The fully qualified function name must be used by all idents except the ident that has the same name as the schema to which the function belongs.

It is possible to create multiple functions with the same name if they differ with regard to either the number of parameters or the data type for the parameter. It is not possible to have multiple functions that only differ with regard to the return data type. See Mimer SQL Programmer's Manual, Parameter Overloading for more information. Type precedence lists are found in Type Precedence Lists.

Each function can be given a specific name, which must be unique within a schema. If no specific name is given, the system will generate a unique name. The specific name for a function can be retrieved by using the INFORMATION_SCHEMA views.

A specific name can be used in DROP, GRANT and REVOKE statements. It is particularly useful when dealing with function with parameter overloading. Instead of having to specify a list of data types, in order to distinguish the function, the specific name can be used.

The parameter-name should follow the normal rules for naming SQL identifiers, see SQL Identifiers.

The permitted data types are pre-defined data types (described in Data Types in SQL Statements).

If neither DETERMINISTIC nor NOT DETERMINISTIC is specified, then NOT DETERMINISTIC is implicit.

If DETERMINISTIC is specified, then the function is guaranteed to produce the same result every time it is invoked with the same set of input values and repeated invocations of it can, therefore, be optimized.

The following access options may be specified:

•CONTAINS SQL

The function may not contain any data-manipulation-statements. All other procedural-sql-statements are permitted. The function may only invoke CONTAINS SQL functions and procedures. This option effectively prevents a routine from performing read or write operations on data in the database.

•READS SQL DATA

All procedural-sql-statements are permitted except those performing updates (i.e. DELETE, INSERT and UPDATE). The function may only invoke CONTAINS SQL or READ SQL DATA functions and procedures.

This option effectively prevents a routine from performing write operations on data in the database.

•MODIFIES SQL DATA

All procedural-sql-statements are permitted and any function or procedure may be invoked from this type of function.

This option allows a routine to perform read and write operations on data in the database.

If neither CONTAINS SQL, READS SQL DATA nor MODIFIES SQL DATA is specified, then CONTAINS SQL is implicit.

Restrictions

A function created this way cannot be added to a module.

It is possible to create multiple functions with the same name in a schema if the functions have a different number of parameters or parameters with different data types. It is not possible to have multiple functions that only differs with respect to the return data type.

It is not possible to create a synonym for a function name.

A parameter name must be unique within the function.

The parameter mode cannot be specified for a function parameter (as it is for a procedure parameter).

The ROW data type cannot be specified in data-type.

If DETERMINISTIC is specified, the procedural SQL statement of the function may not contain, or be, a reference to: SESSION_USER, CURRENT_PROGRAM, CURRENT_DATE, LOCALTIME, LOCALTIMESTAMP or BUILTIN.UTC_TIMESTAMP and the function may not invoke functions or procedures that are not deterministic.

If an invoked function attempts to execute a COMMIT or ROLLBACK statement in a context where this is not permitted, (i.e. after being invoked from within a result set procedure, from within an atomic compound statement or from a data manipulation statement in one of these contexts) an exception will be raised.

An ident must have EXECUTE privilege on the function in order to invoke it.

Notes

A function is invoked by specifying its name and parameter list where a value-expression would be used.

All function parameters have the default mode (which is IN). See CREATE PROCEDURE for details on the parameter modes.

A parameter-name can be the same as the name of the function.

If a parameter is defined as using a domain, any input value for this parameter will be verified to ensue that any check constraint is not violated.

Refer to the Mimer SQL User's Manual, Creating Functions, Procedures, Triggers and Modules for details on using the CREATE FUNCTION statement in BSQL, where the @ delimiter is required.

Examples

CREATE FUNCTION mimer_store_book.authors_name(p_name VARCHAR(48))

  RETURNS VARCHAR(48)

-- Formats a name into <surname>[,<initial>]

DETERMINISTIC

BEGIN

  DECLARE v_length, v_offset INTEGER;

  DECLARE v_fnm, v_name VARCHAR(48);

 

  SET v_length = POSITION(',' IN p_name);

  IF v_length = 0 THEN

     SET v_name = UPPER(TRIM(SUBSTRING(p_name FROM 1)));

  ELSE

     -- Append first initial to surname

     SET v_name = UPPER(TRIM(SUBSTRING(p_name FROM 1 FOR v_length)));

     SET v_fnm = UPPER(TRIM(SUBSTRING(p_name FROM v_length+1)));

     SET v_name = v_name || SUBSTRING(v_fnm FROM 1 FOR 1);

  END IF;

 

  RETURN v_name;

END  -- of routine mimer_store_book.authors_name

 

Example on how to create and use a simple function converting to Celsius degrees from Fahrenheit degrees:

CREATE FUNCTION C_from_F (Fdegrees integer) RETURNS integer

   RETURN CAST((Fdegrees - 32) * 5.0 / 9 + 0.5 AS integer);

 

SELECT C_from_F(temperature) AS Celsius_degrees

FROM US_Weather;

 

SET ? = C_from_F(451);

Standard Compliance