Reference Manual
   CHAPTER 9. SQL Statements     

CREATE FUNCTION statement

Function 

To create a new function in the database.

Syntax 

CREATE FUNCTION [ owner.]function-name ( [ parameter , ... ] )
... RETURNS data-type
... { EXTERNAL NAME library-call |
... [ ON EXCEPTION RESUME ]
... compound-statement }

Parameters 

parameter:
IN parameter-name data-type

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

operating-system:
OS2
| Windows3X
| Windows95
| WindowsNT
| NetWare

Permissions 

Must have RESOURCE authority.

For external functions, must have DBA authority.

Side effects 

Automatic commit.

See also 

DROP statement

BEGIN... END statement

CREATE PROCEDURE statement

RETURN statement

Using Procedures, Triggers, and Batches

Description 

The CREATE FUNCTION statement creates (stores) 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 nonaggregate functions.

Parameter names must conform to the rules for other database identifiers such as column names. They must have a valid SQL data type (see SQL Data Types), and must be prefixed by the keyword IN, signifying that the argument is an expression that provides a value to the procedure.

A function using the EXTERNAL NAME clause is a wrapper around a call to an external dynamic link library, and is called an external stored procedure. An external stored procedure can have no clauses other than the EXTERNAL NAME clause following the RETURNS clause. For a description of external procedures, see Calling external libraries from procedures.

Standards and compatibility 

• SQL/92     Persistent Stored Module feature.

• Sybase     Not supported by Adaptive Server Enterprise.

Example 

• 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 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 (emp_fname, emp_lname)
  FROM employee

  fullname (emp_fname, emp_lname)
  Fran Whitney
  Matthew Cobb
  Philip Chin
  Julie Jordan
  Robert Breault
  ...