CREATE FUNCTION statement

Description

Creates a new function in the database.

Syntax

Syntax 1

CREATE [ TEMPORARY ] FUNCTION [ owner.]function-name  ( [ parameter, … ] )
 RETURNS data-type routine-characteristics
 [ SQL SECURITY { INVOKER | DEFINER } ]
 { compound-statement
  | AS tsql-compound-statement
  | external-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 ]

Parameters

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

The following examples illustrate the use of the fullname function.

• To return a full name from two supplied strings, enter:

  SELECT fullname ('joe','smith')
  

• To list the names of all employees, enter:

  SELECT fullname (givenname, surname)
  FROM Employees
  

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

The CREATE FUNCTION statement creates a user-defined function in the database. A function can be created for another user by specifying an owner name. Subject to permissions, a user-defined function can be used in exactly the same way as other non-aggregate functions.

The following sections describe each clause of the CREATE 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.

Specifying TEMPORARY (CREATE TEMPORARY FUNCTION) means that 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.

Temporary functions can be created and dropped when connected to a read-only database.

When SQL SECURITY INVOKER is specified, more memory is used because annotation must be done for each user that calls the procedure. Also, when SQL SECURITY INVOKER is specified, 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.

The EXTERNAL NAME clause is not supported for temporary functions. For information about external library calls, see “Calling external libraries from procedures” in SQL Anywhere Server – Programming > SQL Anywhere Data Access APIs > SQL Anywhere External Function API.

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

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.

If a function returns a result set, it cannot also set output parameters or return a return value.

To modify a user-defined function, or to hide the contents of a function by scrambling its definition, use the ALTER FUNCTION statement. For more information, see “ALTER FUNCTION statement” in SQL Anywhere Server – SQL Reference > Using SQL > SQL statements > SQL statements (A-D).

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.

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

Automatic commit.

Standards

Permissions

Must have RESOURCE authority.

External functions, including Java functions, must have DBA authority.

See also