O'Reilly logo

SQL in a Nutshell by Kevin Kline

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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, ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required