CREATE FUNCTION statement

Use this statement to create a user-defined SQL function in the database. To create external function interfaces, see CREATE FUNCTION statement (external procedures). To create web services functions, see CREATE FUNCTION statement (web services).

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

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

tsql-compound-statement:
sql-statement
sql-statement
 ...

Parameters

OR REPLACE clause Specifying CREATE OR REPLACE FUNCTION creates a new function, or replaces an existing function with the same name. In the case of replacing, this clause changes the definition of the function, but preserves existing permissions.
You cannot use the OR REPLACE clause with temporary functions.
TEMPORARY keyword Specifying 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 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) or specified owner. You can specify an owner for a temporary function when:
- the temporary function is created within a permanent stored procedure
- the owner of the temporary function and permanent stored procedure is the same
To drop the owner of a temporary function, you must drop the temporary function first.

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

You cannot use the OR REPLACE clause with temporary functions.
SQL SECURITY clause The SQL SECURITY clause 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.
compound-statement A set of SQL statements bracketed by BEGIN and END, and separated by semicolons. See BEGIN statement.
tsql-compound-statement A batch of Transact-SQL statements. See Transact-SQL batch overview, and CREATE PROCEDURE statement [T-SQL].
ON EXCEPTION RESUME clause Use Transact-SQL-like error handling. See CREATE PROCEDURE statement.

[ NOT ] DETERMINISTIC clause Use this clause to indicate whether functions are deterministic or non-deterministic. If this clause is omitted, then the deterministic behavior of the function is unspecified (the default).

If a function is declared as DETERMINISTIC, it should return the same value every time it is invoked with the same set of parameters.

If a function is declared as NOT DETERMINISTIC, then it is not guaranteed to return the same value for the same set of parameters. A function declared as NOT DETERMINISTIC is re-evaluated each time it is called in a query. This clause must be used when it is known that the function result for a given set of parameters can vary.

Also, 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 can be declared as DETERMINISTIC if they always return the same value for given input parameters.

Remarks

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

Parameter names must conform to the rules for database identifiers. They must have a valid SQL data type, and must be prefixed by the keyword IN, signifying that the argument is an expression that provides a value to the function.

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.

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, care should be taken to qualify all object names (tables, procedures, and so on) with their appropriate owner.

All 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 returns the same result, and does not have any unwanted side-effects on the query's semantics.

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

Permissions

Must have RESOURCE authority, unless creating a temporary function.

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

Side effects

Automatic commit.

Standards and compatibility

SQL/2003 Persistent Stored Module feature.

Examples

The following function 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 example replaces the fullname function created in the first example. After replacing the function, the local variable name is removed:

CREATE OR REPLACE FUNCTION fullname(
   firstname CHAR(30),
   lastname CHAR(30) )
RETURNS CHAR(61)
BEGIN
   RETURN = firstname || ' ' || lastname;
END;

The following examples illustrate 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
...

The following function uses Transact-SQL syntax:

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

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

Discuss this page in DocCommentXchange.
Send feedback about this page using email.