Man Linux: Main Page and Category List


       CREATE FUNCTION - define a new function


           name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } defexpr ] [, ...] ] )
           [ RETURNS rettype
             | RETURNS TABLE ( colname coltype [, ...] ) ]
         { LANGUAGE langname
           | WINDOW
           | COST execution_cost
           | ROWS result_rows
           | SET configuration_parameter { TO value | = value | FROM CURRENT }
           | AS ’definition’
           | AS ’obj_file’, ’link_symbol’
         } ...
           [ WITH ( attribute [, ...] ) ]


       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.


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

              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.

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

              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.

              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.

              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

              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

              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.

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

              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.



              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.



       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


              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.

              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.

              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.


       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.

              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

       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.

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

                     Equivalent to STRICT or RETURNS NULL ON NULL INPUT.

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

       Attribute names are not case-sensitive.


       Refer to in  the  documentation  for  further  information  on  writing

       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.


       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

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

       CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS $$
                       RETURN i + 1;
       $$ 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.


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

               RETURN passed;
       $$  LANGUAGE plpgsql
           -- 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

       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:

       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;


       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

       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.


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