SQL in a Nutshell

Name

CREATE FUNCTION

Synopsis

The CREATE FUNCTION statement creates a user-defined function (UDF), which takes input arguments and returns a single value in the same way as CAST( ) . A UDF can be called in a query just like any other system function.

See Chapter 4, for a full description of SQL functions and the individual vendor implementations.

Vendor	Command
SQL Server	Supported, with variations
MySQL	Supported, with variations
Oracle	Supported, with variations
PostgreSQL	Supported, with variations

The CREATE FUNCTION statement allows database programmers to create user-defined functions. These functions, once created, can be called in queries and data-manipulation operations, such as INSERT, UPDATE, and the WHERE clause of DELETE statements. Although the basic syntax for the statement was shown before, there is so much variety in how vendors have implemented the command that they are each described later in this section.

SQL99 Syntax and Description

CREATE FUNCTION function_name 
[(parameter datatype attributes [,...n])]
RETURNS datatype

  [LANGUAGE {ADA | C | FORTRAN | MUMPS | PASCAL | PLI | SQL}]
  [PARAMETER STYLE {SQL | GENERAL}]
  [SPECIFIC specific_name]
  [DETERMINISTIC | NOT DETERMINISTIC]
  [NO SQL | CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA]
  [RETURNS NULL ON NULL INPUT | CALL ON NULL INPUT]
  [STATIC DISPATCH]

code block

The SQL99 standard for the CREATE FUNCTION statement has a primary component and a more advanced component that is used less often. In most UDFs, ...