Provided by: postgresql-client-8.0_8.0.7-2build1_i386 bug

NAME

       CREATE FUNCTION - define a new function

SYNOPSIS

       CREATE [ OR REPLACE ] FUNCTION name ( [ [ argname ] argtype [, ...] ] )
           RETURNS rettype
         { LANGUAGE langname
           | IMMUTABLE | STABLE | VOLATILE
           | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
           | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
           | AS ’definition’
           | AS ’obj_file’, ’link_symbol’
         } ...
           [ WITH ( attribute [, ...] ) ]

DESCRIPTION

       CREATE  FUNCTION  defines  a  new function.  CREATE OR REPLACE FUNCTION
       will either create a new function, or replace an existing definition.

       If a schema name is included, then  the  function  is  created  in  the
       specified  schema.  Otherwise it is created in the current schema.  The
       name of the new function must not match any existing function with  the
       same argument types in the same schema. However, functions of different
       argument types may share a name (this is called overloading).

       To update the definition of an existing function, use CREATE OR REPLACE
       FUNCTION.  It is not possible to change the name or argument types of a
       function this way (if you tried, you would actually be creating a  new,
       distinct  function).  Also, CREATE OR REPLACE FUNCTION will not let you
       change the return type of an existing function. To do  that,  you  must
       drop and recreate the function.

       If  you  drop and then recreate a function, the new function is not the
       same entity as the old; you will have to drop  existing  rules,  views,
       triggers,  etc.  that  refer to the old function. Use CREATE OR REPLACE
       FUNCTION to change a function definition without breaking objects  that
       refer to the function.

       The user that creates the function becomes the owner of the function.

PARAMETERS

       name   The  name  (optionally  schema-qualified)  of  the  function  to
              create.

       argname
              The  name  of  an  argument.  Some  languages  (currently   only
              PL/pgSQL)  let  you use the name in the function body. For other
              languages the argument name is just extra documentation.

       argtype
              The data type(s) of the function’s arguments (optionally schema-
              qualified),  if  any. The argument types may be base, composite,
              or domain types, or may reference the type of a table column.

              Depending on the implementation language it may also be  allowed
              to   specify   ‘‘pseudotypes’’  such  as  cstring.   Pseudotypes
              indicate that the actual argument type  is  either  incompletely
              specified, or outside the set of ordinary SQL data types.

              The    type    of    a   column   is   referenced   by   writing
              tablename.columnname%TYPE.  Using  this  feature  can  sometimes
              help make a function independent of changes to the definition of
              a table.

       rettype
              The return data type (optionally schema-qualified).  The  return
              type  may be a base, composite, or domain type, or may reference
              the type of a table column.   Depending  on  the  implementation
              language  it may also be allowed to specify ‘‘pseudotypes’’ such
              as cstring.

              The SETOF modifier indicates that the function will return a set
              of items, rather than a single item.

              The    type    of    a   column   is   referenced   by   writing
              tablename.columnname%TYPE.

       langname
              The name of the language that the function  is  implemented  in.
              May  be  SQL,  C,  internal,  or  the  name  of  a  user-defined
              procedural language. For backward compatibility, the name may be
              enclosed by single quotes.

       IMMUTABLE

       STABLE

       VOLATILE
              These attributes inform the system whether it is safe to replace
              multiple evaluations of the function with a  single  evaluation,
              for  run-time optimization. At most one choice may be specified.
              If none of these appear, VOLATILE is the default assumption.

              IMMUTABLE indicates that the function always  returns  the  same
              result when given the same argument values; that is, it does not
              do database lookups or otherwise use  information  not  directly
              present  in its argument list. If this option is given, any call
              of the function with all-constant arguments can  be  immediately
              replaced with the function value.

              STABLE  indicates  that  within a single table scan the function
              will consistently return the same result for the  same  argument
              values,  but that its result could change across SQL statements.
              This is the appropriate selection for  functions  whose  results
              depend  on  database  lookups,  parameter variables (such as the
              current time zone), etc. Also note  that  the  current_timestamp
              family of functions qualify as stable, since their values do not
              change within a transaction.

              VOLATILE indicates that  the  function  value  can  change  even
              within  a  single  table  scan, so no optimizations can be made.
              Relatively few database functions are volatile  in  this  sense;
              some  examples  are  random(), currval(), timeofday(). Note that
              any function that has side-effects must be classified  volatile,
              even  if  its result is quite predictable, to prevent calls from
              being optimized away; an example is setval().

              For additional details see the documentation.

       CALLED ON NULL INPUT

       RETURNS NULL ON NULL INPUT

       STRICT CALLED ON NULL INPUT (the default) indicates that  the  function
              will  be called normally when some of its arguments are null. It
              is then the function author’s responsibility to check  for  null
              values if necessary and respond appropriately.

              RETURNS NULL ON NULL INPUT or STRICT indicates that the function
              always returns null whenever any of its arguments are  null.  If
              this  parameter  is specified, the function is not executed when
              there are null arguments;  instead  a  null  result  is  assumed
              automatically.

       [EXTERNAL] SECURITY INVOKER

       [EXTERNAL] SECURITY DEFINER
              SECURITY  INVOKER  indicates that the function is to be executed
              with the privileges of the user that  calls  it.   That  is  the
              default.  SECURITY  DEFINER specifies that the function is to be
              executed with the privileges of the user that created it.

              The key word EXTERNAL is present  for  SQL  conformance  but  is
              optional  since, unlike in SQL, this feature does not only apply
              to external functions.

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

       obj_file, link_symbol
              This  form  of  the AS clause is used for dynamically loadable 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 function’s
              link symbol, that is, the name of the function in the C language
              source  code. If the link symbol is omitted, it is assumed to be
              the same as the name of the SQL function being defined.

       attribute
              The historical way to specify  optional  pieces  of  information
              about the function. The following attributes may appear here:

              isStrict
                     Equivalent to STRICT or RETURNS NULL ON NULL INPUT.

              isCachable
                     isCachable  is  an obsolete equivalent of IMMUTABLE; it’s
                     still accepted for backwards-compatibility reasons.

       Attribute names are not case-sensitive.

NOTES

       Refer  to  the  documentation  for  further  information   on   writing
       functions.

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

       PostgreSQL  allows  function overloading; that is, the same name can be
       used for several different functions so  long  as  they  have  distinct
       argument  types.  However,  the  C  names  of  all  functions  must  be
       different, so you must give overloaded C functions  different  C  names
       (for example, use the argument types as part of the C names).

       When  repeated CREATE FUNCTION calls refer to the same object file, the
       file is only loaded once. To unload and reload the file (perhaps during
       development), use the LOAD [load(7)] command.

       Use  DROP FUNCTION [drop_function(7)] to remove user-defined functions.

       It is often helpful to use dollar quoting (see  the  documentation)  to
       write  the  function  definition  string, rather than the normal single
       quote syntax. Without dollar quoting, any single quotes or  backslashes
       in the function definition must be escaped by doubling them.

       To be able to define a function, the user must have the USAGE privilege
       on the language.

EXAMPLES

       Here is a trivial example to help you get started. For more information
       and examples, see the documentation.

       CREATE FUNCTION add(integer, integer) RETURNS integer
           AS ’select $1 + $2;’
           LANGUAGE SQL
           IMMUTABLE
           RETURNS NULL ON NULL INPUT;

       Increment an integer, making use of an argument name, in PL/pgSQL:

       CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS $$
               BEGIN
                       RETURN i + 1;
               END;
       $$ LANGUAGE plpgsql;

COMPATIBILITY

       A  CREATE  FUNCTION  command  is  defined  in  SQL:1999 and later.  The
       PostgreSQL version is similar but not fully compatible. The  attributes
       are not portable, neither are the different available languages.

SEE ALSO

       ALTER  FUNCTION  [alter_function(7)], DROP FUNCTION [drop_function(l)],
       GRANT  [grant(l)],  LOAD  [load(l)],  REVOKE  [revoke(l)],   createlang
       [createlang(1)]