Provided by: postgresql-client-8.2_8.2.7-1_i386 bug

NAME

       CREATE FUNCTION - define a new function

SYNOPSIS

       CREATE [ OR REPLACE ] FUNCTION
           name ( [ [ argmode ] [ 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. (When using OUT parameters, that means
       you can’t change the names or types of any  OUT  parameters  except  by
       dropping 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.

       argmode
              The mode of an argument: either IN, OUT, or INOUT.  If  omitted,
              the default is IN.

       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   name  of  an  input  argument  is  just  extra
              documentation.  But  the  name  of   an   output   argument   is
              significant,  since it defines the column name in the result row
              type. (If you omit the name for an output argument,  the  system
              will choose a default column name.)

       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.  If the function is not supposed to return a value,
              specify void as the return type.

              When there are OUT or INOUT parameters, the RETURNS  clause  may
              be  omitted.  If  present,  it  must  agree with the result type
              implied by the output parameters: RECORD if there  are  multiple
              output  parameters,  or  the  same  type  as  the  single output
              parameter.

              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 query optimizer about the behavior
              of the function. At most one choice may be specified. If none of
              these appear, VOLATILE is the default assumption.

              IMMUTABLE indicates that the function cannot modify the database
              and 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 the function cannot modify  the  database,
              and  that within a single table scan it 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(). But 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 in 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 allowed for SQL conformance, but it  is
              optional  since,  unlike  in  SQL,  this  feature applies to all
              functions not only external ones.

       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 in  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).

       Two  functions  are considered the same if they have the same names and
       input argument types, ignoring any OUT  parameters.  Thus  for  example
       these declarations conflict:

       CREATE FUNCTION foo(int) ...
       CREATE FUNCTION foo(int, out text) ...

       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 in 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 are some trivial examples  to  help  you  get  started.  For  more
       information and examples, see in 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;

       Return a record containing multiple output parameters:

       CREATE FUNCTION dup(in int, out f1 int, out f2 text)
           AS $$ SELECT $1, CAST($1 AS text) || ’ is text’ $$
           LANGUAGE SQL;

       SELECT * FROM dup(42);

       You  can  do  the  same  thing  more verbosely with an explicitly named
       composite type:

       CREATE TYPE dup_result AS (f1 int, f2 text);

       CREATE FUNCTION dup(int) RETURNS dup_result
           AS $$ SELECT $1, CAST($1 AS text) || ’ is text’ $$
           LANGUAGE SQL;

       SELECT * FROM dup(42);

WRITING SECURITY DEFINER FUNCTIONS SAFELY

       Because a SECURITY DEFINER function is executed with the privileges  of
       the  user  that  created it, care is needed to ensure that the function
       cannot be misused. For security, search_path should be set  to  exclude
       any  schemas writable by untrusted users. This prevents malicious users
       from  creating  objects  that  mask  objects  used  by  the   function.
       Particularly  important  in  this regard is the temporary-table schema,
       which is searched first by default, and is normally writable by anyone.
       A  secure  arrangement can be had by forcing the temporary schema to be
       searched last.  To  do  this,  write  pg_temp  as  the  last  entry  in
       search_path.  This function illustrates safe usage:

       CREATE FUNCTION check_password(uname TEXT, pass TEXT)
       RETURNS BOOLEAN AS $$
       DECLARE passed BOOLEAN;
               old_path TEXT;
       BEGIN
               -- Save old search_path; notice we must qualify current_setting
               -- to ensure we invoke the right function
               old_path := pg_catalog.current_setting(’search_path’);

               -- Set a secure search_path: trusted schemas, then ’pg_temp’.
               -- We set is_local = true so that the old value will be restored
               -- in event of an error before we reach the function end.
               PERFORM pg_catalog.set_config(’search_path’, ’admin, pg_temp’, true);

               -- Do whatever secure work we came for.
               SELECT  (pwd = $2) INTO passed
               FROM    pwds
               WHERE   username = $1;

               -- Restore caller’s search_path
               PERFORM pg_catalog.set_config(’search_path’, old_path, true);

               RETURN passed;
       END;
       $$ LANGUAGE plpgsql SECURITY DEFINER;

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.

       For  compatibility  with  some  other  database systems, argmode can be
       written either before or after argname.  But  only  the  first  way  is
       standard-compliant.

SEE ALSO

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