CREATE FUNCTION

Name

CREATE FUNCTION — Defines a new function.
   CREATE FUNCTION name ([ftype [, ...]])
             RETURNS rtype
             AS path
             LANGUAGE 'langname'
  

Inputs

name

The name of a function to create.

ftype

The data type of function arguments.

rtype

The return data type.

path

May be either an SQL-query or an absolute path to an object file.

langname

may be 'c', 'sql', 'internal' or 'plname'. (where 'plname' is the language name of a created procedural language. See CREATE LANGUAGE for details).

Outputs

CREATE

This is returned if the command completes successfully.

Description

With this command, a PostgreSQL user can register a function with PostgreSQL. Subsequently, this user is treated as the owner of the function.

Notes

Refer to PostgreSQL User's Guide chapter 6 for further information.

Refer to the DROP FUNCTION statement to drop functions.

Usage

To create a simple SQL function:

   CREATE FUNCTION one() RETURNS int4
            AS 'SELECT 1 AS RESULT'
                LANGUAGE 'sql';

   SELECT one() AS answer;

   answer 
   ------
   1   
  

To create a C function, calling a routine from a user-created shared library. This particular routine calculates a check digit and returns TRUE if the check digit in the function parameters is correct. It is intended for use in a CHECK contraint.

   CREATE FUNCTION ean_checkdigit(bpchar, bpchar) RETURNS bool
                AS '/usr1/proj/bray/sql/funcs.so' LANGUAGE 'c';

   CREATE TABLE product
   (
        id              char(8)         PRIMARY KEY,
        eanprefix       char(8)         CHECK (eanprefix ~ '[0-9]{2}-[0-9]{5}')
                                        REFERENCES brandname(ean_prefix),
        eancode         char(6)         CHECK (eancode ~ '[0-9]{6}'),
        CONSTRAINT ean CHECK (ean_checkdigit(eanprefix, eancode))
   );
  

Bugs

A C function cannot return a set of values.

Compatibility

The CREATE FUNCTION statement is a PostgreSQL language extension.

SQL/PSM[1]

The SQL/PSM CREATE FUNCTION statement has the following syntax:

     CREATE FUNCTION name
            ( [ [IN|OUT|INOUT] parm type [, ...] ])
            RETURNS rtype
            LANGUAGE 'langname'
            ESPECIFIC routine
            SQL-statement