CREATE FUNCTION - Defines a new function


SYNOPSIS

       CREATE FUNCTION name ( [ ftype [, ...] ] )
           RETURNS rtype
           AS definition
           LANGUAGE 'langname'
           [ WITH ( attribute [, ...] ) ]
       CREATE FUNCTION name ( [ ftype [, ...] ] )
           RETURNS rtype
           AS obj_file , link_symbol
           LANGUAGE 'langname'
           [ WITH ( attribute [, ...] ) ]

   INPUTS
       name   The name of a function to create.

       ftype  The  data  type(s)  of the function's arguments, if
              any.  The input types may be base or complex types,
              or  opaque.   Opaque  indicates  that  the function
              accepts arguments of a non-SQL type such as char *.

       rtype  The return data type.  The output type may be spec­
              ified as a base type, complex type, setof type,  or
              opaque.   The  setof  modifier  indicates  that the
              function will return a set of items, rather than  a
              single item.

       attribute
              An  optional  piece  of information about the func­
              tion, used for optimization. See below for details.

       definition
              A string defining the function; the meaning depends
              on the language.  It may be  an  internal  function
              name,  the path to an object file, an SQL query, or
              text in a procedural language.

       obj_file , link_symbol
              This form of the AS clause is used for  dynamically
              linked, C language functions when the function name
              in the C language source code is not  the  same  as
              the  name  of the SQL function. The string obj_file
              is the name of the file containing the  dynamically
              loadable  object,  and  link_symbol is the object's
              link symbol, that is the name of  the  function  in
              the C language source code.

       langname
              May  be  'sql', 'C', 'internal', or 'plname', where
              'plname'  is  the  name  of  a  created  procedural

              for details.

   OUTPUTS
       CREATE This is returned if the command completes  success­
              fully.


DESCRIPTION

       CREATE FUNCTION allows a Postgres user to register a func­
       tion with the database. Subsequently, this user is consid­
       ered the owner of the function.

   FUNCTION ATTRIBUTES
       The following items may appear in the WITH clause:

       iscachable
              Iscachable   indicates  that  the  function  always
              returns the same result when given the  same  argu­
              ment  values (i.e., it does not do database lookups
              or otherwise use information not  directly  present
              in   its   parameter   list).  The  optimizer  uses
              iscachable to know whether it is safe to pre-evalu­
              ate a call of the function.

       isstrict
              isstrict indicates that the function always returns
              NULL whenever any of its  arguments  are  NULL.  If
              this  attribute  is  specified, the function is not
              executed when there are NULL arguments;  instead  a
              NULL   result   is   assumed  automatically.   When
              isstrict is not specified,  the  function  will  be
              called  for  NULL  inputs.  It is then the function
              author's responsibility to check for NULLs if  nec­
              essary and respond appropriately.

   NOTES
       Refer  to the chapter in the PostgreSQL Programmer's Guide
       on the topic of extending Postgres via functions for  fur­
       ther information on writing external functions.

       Use DROP FUNCTION to remove user-defined functions.

       The  full SQL92 type syntax is allowed for input arguments
       and return value. However, some details of the type speci­
       fication (e.g., the precision field for numeric types) are
       the responsibility of the underlying function  implementa­
       tion  and  are silently swallowed (i.e., not recognized or
       enforced) by the CREATE FUNCTION command.

       Postgres allows function "overloading"; that is, the  same
       name  can  be used for several different functions so long

       be  used  with  caution  for internal and C-language func­
       tions, however.

       Two internal functions cannot have the same C name without
       causing errors at link time. To get around that, give them
       different C names (for example, use the argument types  as
       part  of  the C names), then specify those names in the AS
       clause of CREATE FUNCTION.   If  the  AS  clause  is  left
       empty,  then  CREATE  FUNCTION  assumes  the C name of the
       function is the same as the SQL name.

       Similarly, when overloading SQL function names with multi­
       ple C-language functions, give each C-language instance of
       the function a distinct name,  then  use  the  alternative
       form  of  the  AS  clause in the CREATE FUNCTION syntax to
       select the appropriate C-language implementation  of  each
       overloaded SQL function.


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

       This  example  creates  a  C function by calling a routine
       from a user-created shared library. This  particular  rou­
       tine  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 boolean
           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))
       );

       This  example creates a function that does type conversion

       type  point.  The function is implemented by a dynamically
       loaded object that was compiled from C source.  For  Post­
       gres to find a type conversion function automatically, the
       sql function has to have the same name as the return type,
       and  so  overloading  is unavoidable. The function name is
       overloaded by using the second form of the  AS  clause  in
       the SQL definition:

       CREATE FUNCTION point(complex) RETURNS point
           AS '/home/bernie/pgsql/lib/complex.so', 'complex_to_point'
           LANGUAGE 'c';

       The C declaration of the function is:

       Point * complex_to_point (Complex *z)
       {
            Point *p;

            p = (Point *) palloc(sizeof(Point));
            p->x = z->x;
            p->y = z->y;

            return p;
       }


COMPATIBILITY

   SQL92
       CREATE FUNCTION is a Postgres language extension.

   SQL/PSM
              Note:  PSM stands for Persistent Stored Modules. It
              is a procedural  language  and  it  was  originally
              hoped  that  PSM  would  be ratified as an official
              standard by late 1996. As of mid-1998, this has not
              yet happened, but it is hoped that PSM will eventu­
              ally become a standard.

       SQL/PSM CREATE FUNCTION has the following syntax:

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


Man(1) output converted with man2html