Introduction to user-defined functions

User's Guide
PART 1. Working with Databases
CHAPTER 10. Using Procedures, Triggers, and Batches

Introduction to user-defined functions

User-defined functions are a class of procedures that return a single value to the calling environment. This section introduces creating, using, and dropping user-defined functions

Creating user-defined functions

User-defined functions are created using the CREATE FUNCTION statement. You must have RESOURCE authority in order to create a user-defined function.

The following simple example creates a function that concatenates two strings, together with a space, to form a full name from a first name and a last name.

You can create the example function fullname by connecting to the sample database from Interactive SQL as user ID DBA, using password SQL, and typing the statement in the command window.

If you are using a tool other than Interactive SQL or Sybase Central, you may need to change the command delimiter away from the semicolon before entering the CREATE FUNCTION statement.

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

For a complete description of the CREATE FUNCTION syntax, see CREATE FUNCTION statement.

The CREATE FUNCTION syntax differs slightly from that of the CREATE PROCEDURE statement. The following are distinctive differences:

No IN, OUT, or INOUT keywords are required, as all parameters are IN parameters.
The RETURNS clause is required to specify the data type being returned.
The RETURN statement is required to specify the value being returned.

Calling user-defined functions

A user-defined function can be used, subject to permissions, in any place that a built-in non-aggregate function is used.

The following statement in Interactive SQL returns a full name from two columns containing a first and last name:

SELECT fullname (emp_fname, emp_lname)
FROM employee;

fullname (emp_fname, emp_lname)

Fran Whitney

Matthew Cobb

Philip Chin

...

fullname (emp_fname, emp_lname)
Fran Whitney
Matthew Cobb
Philip Chin
...

The following statement in Interactive SQL returns a full name from a supplied first and last name:

SELECT fullname ('Jane', 'Smith');

fullname ('Jane','Smith')

Jane Smith

fullname ('Jane','Smith')
Jane Smith

The fullname function can be used by any user who has been granted EXECUTE permission for the function.

Dropping user-defined functions

Once a user-defined function is created, it remains in the database until it is explicitly removed. Only the owner of the function or a user with DBA authority can drop a function from the database.

The following statement removes the function fullname from the database:

DROP FUNCTION fullname

Permissions to execute user-defined functions

A user-defined function is owned by the user who created it, and that user can execute it without permission. Permission to execute it can be granted to other users using the GRANT EXECUTE command.

For example, the creator of the function fullname could allow another_user to use fullname with the statement:

GRANT EXECUTE ON fullname TO another_user

The following statement revokes permission to use the function:

REVOKE EXECUTE ON fullname FROM another_user

For Info For more information on managing user permissions on functions, see Granting permissions on procedures.