Provided by: postgresql-client-8.4_8.4.11-1_amd64 bug

NAME

       CREATE FUNCTION - define a new function

SYNOPSIS

       CREATE [ OR REPLACE ] FUNCTION
           name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } defexpr ] [, ...] ] )
           [ RETURNS rettype
             | RETURNS TABLE ( colname coltype [, ...] ) ]
         { LANGUAGE langname
           | WINDOW
           | 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  input  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: IN, OUT, INOUT, or VARIADIC.  If omitted, the default is
              IN.  Only OUT arguments can follow a VARIADIC one.  Also, OUT and  INOUT  arguments
              cannot be used together with the RETURNS TABLE notation.

       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.

       defexpr
              An expression to be used as default value if the parameter is  not  specified.  The
              expression  has  to be coercible to the argument type of the parameter.  Only input
              (including INOUT) parameters  can  have  a  default  value.  All  input  parameters
              following a parameter with a default value must have default values as well.

       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.

       colname
              The  name  of  an  output  column  in the RETURNS TABLE syntax. This is effectively
              another way of declaring a named OUT parameter,  except  that  RETURNS  TABLE  also
              implies RETURNS SETOF.

       coltype
              The data type of an output column in the RETURNS TABLE syntax.

       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.

       WINDOW WINDOW  indicates  that  the  function  is  a  window  function rather than a plain
              function.  This is currently only useful for functions written in  C.   The  WINDOW
              attribute cannot be changed when replacing an existing function definition.

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

       Functions  that  have  different argument type lists will not be considered to conflict at
       creation time, but if defaults are provided they  might  conflict  in  use.  For  example,
       consider

       CREATE FUNCTION foo(int) ...
       CREATE FUNCTION foo(int, int default 42) ...

       A call foo(10) will fail due to the ambiguity about which function should be called.

       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), start a new
       session.

       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.

       When CREATE OR REPLACE FUNCTION is used to replace an existing function, the ownership and
       permissions  of the function do not change. All other function properties are assigned the
       values specified or implied in the command. You must own the function to replace it  (this
       includes being a member of the owning role).

       If a function is declared STRICT with a VARIADIC argument, the strictness check tests that
       the variadic array as a whole is non-null. The function will still be called if the  array
       has null elements.

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

       Another way to return multiple columns is to use a TABLE function:

       CREATE FUNCTION dup(int) RETURNS TABLE(f1 int, f2 text)
           AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
           LANGUAGE SQL;

       SELECT * FROM dup(42);

       However, a TABLE function is different from the preceding examples,  because  it  actually
       returns a set of records, not just one record.

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.

       The SQL standard does not specify parameter defaults. The syntax with the DEFAULT key word
       is  from  Oracle,  and  it  is somewhat in the spirit of the standard: SQL/PSM uses it for
       variable default values. The syntax with = is used in T-SQL and Firebird.

SEE ALSO

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