Provided by: postgresql-client-8.3_8.3.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
           | COST execution_cost
           | ROWS result_rows
           | SET configuration_parameter { TO value | = value | FROM CURRENT }
           | 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 can share a name (this is called overloading).

       To replace the current 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 cannot 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.  Also, ALTER FUNCTION can be used to change most
       of the auxiliary properties of an existing 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 can be base, composite,
              or domain types, or can reference the type of a table column.

              Depending on  the  implementation  language  it  might  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  can be a base, composite, or domain type, or can reference
              the type of a table column.   Depending  on  the  implementation
              language  it  might  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 can
              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.
              Can  be  SQL,  C,  internal,  or  the  name  of  a  user-defined
              procedural language. For backward compatibility, the name can be
              enclosed by single quotes.

       IMMUTABLE

       STABLE

       VOLATILE
              These attributes inform the query optimizer about  the  behavior
              of the function. At most one choice can 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.

       execution_cost
              A  positive  number  giving the estimated execution cost for the
              function, in units of cpu_operator_cost. If the function returns
              a  set,  this  is  the cost per returned row. If the cost is not
              specified,  1  unit  is  assumed  for  C-language  and  internal
              functions,  and  100 units for functions in all other languages.
              Larger values cause the planner to try to avoid  evaluating  the
              function more often than necessary.

       result_rows
              A  positive  number giving the estimated number of rows that the
              planner should expect the  function  to  return.  This  is  only
              allowed  when  the  function  is  declared  to return a set. The
              default assumption is 1000 rows.

       configuration_parameter

       value  The SET clause causes the specified configuration  parameter  to
              be  set to the specified value when the function is entered, and
              then restored to its prior value when the function  exits.   SET
              FROM  CURRENT saves the session’s current value of the parameter
              as the value to be applied when the function is entered.

              See SET [set(7)] and in the documentation for  more  information
              about allowed parameter names and values.

       definition
              A  string constant defining the function; the meaning depends on
              the language. It can 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 can 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 per session.  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.

       If a SET clause is attached to a function, then the effects  of  a  SET
       LOCAL  command  executed  inside the function for the same variable are
       restricted to the function: the configuration parameter’s  prior  value
       is  still  restored at function exit.  However, an ordinary SET command
       (without LOCAL) overrides the SET clause, much as it  would  do  for  a
       previous  SET LOCAL command: the effects of such a command will persist
       after function exit, unless the current transaction is rolled back.

       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;
       BEGIN
               SELECT  (pwd = $2) INTO passed
               FROM    pwds
               WHERE   username = $1;

               RETURN passed;
       END;
       $$  LANGUAGE plpgsql
           SECURITY DEFINER
           -- Set a secure search_path: trusted schema(s), then ’pg_temp’.
           SET search_path = admin, pg_temp;

       Before PostgreSQL version 8.3, the SET option was not available, and so
       older  functions may contain rather complicated logic to save, set, and
       restore search_path. The SET option is  far  easier  to  use  for  this
       purpose.

       Another  point to keep in mind is that by default, execute privilege is
       granted to PUBLIC for newly created functions (see GRANT [grant(7)] for
       more  information).  Frequently  you  will  wish  to  restrict use of a
       security definer function to only some users.  To  do  that,  you  must
       revoke  the  default PUBLIC privileges and then grant execute privilege
       selectively. To avoid  having  a  window  where  the  new  function  is
       accessible  to  all,  create  it and set the privileges within a single
       transaction. For example:

       BEGIN;
       CREATE FUNCTION check_password(uname TEXT, pass TEXT) ... SECURITY DEFINER;
       REVOKE ALL ON FUNCTION check_password(uname TEXT, pass TEXT) FROM PUBLIC;
       GRANT EXECUTE ON FUNCTION check_password(uname TEXT, pass TEXT) TO admins;
       COMMIT;

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)]