CREATE FUNCTION Statement

Syntax

Syntax 1

CREATE [ OR REPLACE ] [ TEMPORARY ] FUNCTION [ owner.]function-name  ( [ parameter, … ] )
   [ SQL SECURITY { INVOKER | DEFINER } ]
   RETURNS data-type ON EXCEPTION RESUME 
   | [ NOT ] DETERMINISTIC
   { compound-statement | AS tsql-compound-statement
   | EXTERNAL NAME library-call 
   | EXTERNAL NAME java-call LANGUAGE JAVA }

parameter:
   IN parameter-name data-type [ DEFAULT expression ]

routine-characteristics:
   ON EXCEPTION RESUME | [ NOT ] DETERMINISTIC

tsql-compound-statement:
   sql-statement 
    sql-statement …

library-call:
   '[ operating-system:]function-name@library; …'

operating-system:
   UNIX

java-call:
   '[ package-name.]class-name.method-name method-signature'

method-signature:
   ( [ field-descriptor, ….] ) return-descriptor

field-descriptor and return-descriptor:
   Z | B | S | I | J | F | D | C | V | [ descriptor | L class-name;

Syntax 2

CREATE FUNCTION [ owner.]function-name ( [ parameter, … ] )
   RETURNS data-type
   URL url-string
   [ HEADER header-string ]
   [ SOAPHEADER soap-header-string ]
   [ TYPE { 'HTTP[:{ GET | POST } ] ' | 'SOAP[:{ RPC | DOC } ]' } ]
   [ NAMESPACE namespace-string ]
   [ CERTIFICATE certificate-string ]
   [ CLIENTPORT clientport-string ]
   [ PROXY proxy-string ]

url-string:
   ' { HTTP | HTTPS | HTTPS_FIPS }://[user:password@]hostname[:port][/path] '

parameter:
   IN parameter-name data-type [ DEFAULT expression ]

Parameters

CREATE [ OR REPLACE ] – parameter names must conform to the rules for database identifiers. They must have a valid SQL data type and be prefixed by the keyword IN, signifying that the argument is an expression that provides a value to the function.
The CREATE clause creates a new function, while the OR REPLACE clause replaces an existing function with the same name. When a function is replaced, the definition of the function is changed but the existing permissions are preserved. You cannot use the OR REPLACE clause with temporary functions.

TEMPORARY – the function is visible only by the connection that created it, and that it is automatically dropped when the connection is dropped. Temporary functions can also be explicitly dropped. You cannot perform ALTER, GRANT, or REVOKE operations on them, and unlike other functions, temporary functions are not recorded in the catalog or transaction log.
Temporary functions execute with the permissions of their creator (current user), and can only be owned by their creator. Therefore, do not specify owner when creating a temporary function. They can be created and dropped when connected to a read-only database.

SQL SECURITY – defines whether the function is executed as the INVOKER, the user who is calling the function, or as the DEFINER, the user who owns the function. The default is DEFINER.
When INVOKER is specified, more memory is used because annotation must be done for each user that calls the procedure. Also, name resolution is done as the invoker as well. Therefore, take care to qualify all object names (tables, procedures, and so on) with their appropriate owner.

data-type – LONG BINARY and LONG VARCHAR are not permitted as return-value data types.

compound-statement – a set of SQL statements bracketed by BEGIN and END, and separated by semicolons. See BEGIN … END Statement.

tsql-compound-statement – a batch of Transact-SQL statements.

external-name – a wrapper around a call to a function in an external library and can have no other clauses following the RETURNS clause. The library name may include the file extension, which is typically .dll on Windows and .so on UNIX. In the absence of the extension, the software appends the platform-specific default file extension for libraries.
The external-name clause is not supported for temporary functions.

LANGUAGE JAVA – a wrapper around a Java method. For information on calling Java procedures, see CREATE PROCEDURE Statement.

ON EXCEPTION RESUME – uses Transact-SQL-like error handling. See CREATE PROCEDURE Statement.

[NOT] DETERMINISTIC – function is re-evaluated each time it is called in a query. The results of functions not specified in this manner may be cached for better performance, and re-used each time the function is called with the same parameters during query evaluation.
Functions that have side effects, such as modifying the underlying data, should be declared as NOT DETERMINISTIC. For example, a function that generates primary key values and is used in an INSERT … SELECT statement should be declared NOT DETERMINISTIC:
```
CREATE FUNCTION keygen( increment INTEGER ) 
RETURNS INTEGER 
NOT DETERMINISTIC 
BEGIN   
  DECLARE keyval INTEGER;  
  UPDATE counter SET x = x + increment;  
  SELECT counter.x INTO keyval FROM counter;   
  RETURN keyval 
END 
INSERT INTO new_table 
SELECT keygen(1), ... 
FROM old_table
```
```
CREATE FUNCTION keygen( increment INTEGER ) 
RETURNS INTEGER 
NOT DETERMINISTIC 
BEGIN   
  DECLARE keyval INTEGER;  
  UPDATE counter SET x = x + increment;  
  SELECT counter.x INTO keyval FROM counter;   
  RETURN keyval 
END 
INSERT INTO new_table 
SELECT keygen(1), ... 
FROM old_table
```
Functions may be declared as DETERMINISTIC if they always return the same value for given input parameters. All user-defined functions are treated as deterministic unless they are declared NOT DETERMINISTIC. Deterministic functions return a consistent result for the same parameters and are free of side effects. That is, the database server assumes that two successive calls to the same function with the same parameters will return the same result without unwanted side-effects on the semantics of the query.

URL – for use only when defining an HTTP or SOAP web services client function. Specifies the URL of the web service. The optional user name and password parameters provide a means of supplying the credentials needed for HTTP basic authentication. HTTP basic authentication base-64 encodes the user and password information and passes it in the “Authentication” header of the HTTP request.
For web service client functions, the return type of SOAP and HTTP functions must one of the character data types, such as VARCHAR. The value returned is the body of the HTTP response. No HTTP header information is included. If more information is required, such as status information, use a procedure instead of a function.

Parameter values are passed as part of the request. The syntax used depends on the type of request. For HTTP:GET, the parameters are passed as part of the URL; for HTTP:POST requests, the values are placed in the body of the request. Parameters to SOAP requests are always bundled in the request body.

HEADER – when creating HTTP web service client functions, use this clause to add or modify HTTP request header entries. Only printable ASCII characters can be specified for HTTP headers, and they are case-insensitive. For more information about how to use this clause, see the HEADER clause of the CREATE PROCEDURE Statement.

SOAPHEADER – when declaring a SOAP Web service as a function, use this clause to specify one or more SOAP request header entries. A SOAP header can be declared as a static constant, or can be dynamically set using the parameter substitution mechanism (declaring IN, OUT, or INOUT parameters for hd1, hd2, and so on). A web service function can define one or more IN mode substitution parameters, but cannot define an INOUT or OUT substitution parameter.

TYPE – specifies the format used when making the web service request. If SOAP is specified or no type clause is included, the default type SOAP:RPC is used. HTTP implies HTTP:POST. Since SOAP requests are always sent as XML documents, HTTP:POST is always used to send SOAP requests.

NAMESPACE – applies to SOAP client functions only and identifies the method namespace usually required for both SOAP:RPC and SOAP:DOC requests. The SOAP server handling the request uses this namespace to interpret the names of the entities in the SOAP request message body. The namespace can be obtained from the WSDL description of the SOAP service available from the web service server. The default value is the procedure's URL, up to but not including the optional path component.

CERTIFICATE – to make a secure (HTTPS) request, a client must have access to the certificate used by the HTTPS server. The necessary information is specified in a string of semicolon-separated key/value pairs. The certificate can be placed in a file and the name of the file provided using the file key, or the whole certificate can be placed in a string, but not both. These keys are available:

Key	Abbreviation	Description
file		File name of certificate
certificate	cert	The certificate
company	co	Company specified in the certificate
unit		Company unit specified in the certificate
name		Common name specified in the certificate

Certificates are required only for requests that are either directed to an HTTPS server or can be redirected from an insecure to a secure server.

CLIENTPORT – identifies the port number on which the HTTP client procedure communicates using TCP/IP. It is provided for and recommended only for connections across firewalls, as firewalls filter according to the TCP/UDP port. You can specify a single port number, ranges of port numbers, or a combination of both; for example, CLIENTPORT '85,90-97'.

PROXY – specifies the URI of a proxy server. For use when the client must access the network through a proxy. Indicates that the procedure is to connect to the proxy server and send the request to the web service through it.

Examples

Example 1 – concatenates a firstname string and a lastname string:

CREATE FUNCTION fullname (
  firstname CHAR(30),
  lastname CHAR(30) )
RETURNS CHAR(61)
BEGIN
  DECLARE name CHAR(61);
  SET name = firstname || ' ' || lastname;
  RETURN (name);
END

This example illustrates the use of the fullname function.

Return a full name from two supplied strings:

SELECT fullname ('joe','smith')

fullname('joe', 'smith')
joe smith

List the names of all employees:

SELECT fullname (givenname, surname)
FROM Employees

fullname (givenname, surname)
Fran Whitney
Matthew Cobb
Philip Chin
Julie Jordan
Robert Breault
...

Example 2 – uses Transact-SQL syntax:

CREATE FUNCTION DoubleIt ( @Input INT )
RETURNS INT 
AS 
DECLARE @Result INT  
SELECT @Result = @Input * 2 
RETURN @Result

The statement SELECT DoubleIt( 5 ) returns a value of 10.

Example 3 – creates an external function written in Java:

CREATE FUNCTION dba.encrypt( IN name char(254) )
RETURNS VARCHAR 
EXTERNAL NAME
'Scramble.encrypt (Ljava/lang/String;)Ljava/lang/String;'
LANGUAGE JAVA

Usage

To modify a user-defined function, or to hide the contents of a function by scrambling its definition, use the ALTER FUNCTION statement.

When functions are executed, not all parameters need to be specified. If a default value is provided in the CREATE FUNCTION statement, missing parameters are assigned the default values. If an argument is not provided by the caller and no default is set, an error is given.

Side Effects

Automatic commit

Standards

SQL—ISO/ANSI SQL compliant.
Sybase—Not supported by Adaptive Server Enterprise.

Permissions

For function to be owned by self – Requires the CREATE PROCEDURE system privilege.

For function to be owned by any user – Requires one of:

CREATE ANY PROCEDURE system privilege.
CREATE ANY OBJECT system privilege.

To create a function containing an external reference, regardless of whether or not they are the owner of the function, also requires the CREATE EXTERNAL REFERENCE system privilege.