Seth Woolley's Man Viewer

create_function(7) - CREATE FUNCTION - define a new function - man 7 create_function

([section] manual, -k keyword, -K [section] search, -f whatis)
man plain no title

CREATE FUNCTION(7)               SQL Commands               CREATE FUNCTION(7)



NAME
       CREATE FUNCTION - define a new function


SYNOPSIS
       CREATE [ OR REPLACE ] FUNCTION name ( [ 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(1,8) the spec-
       ified  schema. Otherwise it is created in(1,8) the current schema.  The name
       of the new function must not match any existing function with the  same
       argument  types  in(1,8)  the  same  schema. However, functions of different
       argument types may share a name (this is called overloading).

       To update(7,n) 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(3,n) you tried, you'd just 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 break existing rules, views, triggers,
       etc.  that referred 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 of a function to create.

       argtype
              The data type(s) of the function's arguments (optionally schema-
              qualified), if(3,n) any. The argument types may be base, complex,  or
              domain types, or copy the type of an existing column.

              The  type of a column is referenced by writing tablename.column-
              name%TYPE; using this can sometimes help make a  function  inde-
              pendent from changes to the definition of a table.

              Depending  on the implementation language it may also be allowed
              to specify ``pseudotypes'' such as cstring.   Pseudotypes  indi-
              cate that the actual argument type is either incompletely speci-
              fied, or outside the set(7,n,1 builtins) of ordinary SQL data types.

       rettype
              The return data type (optionally schema-qualified).  The  return
              type may be specified as a base, complex, or domain type, or may
              copy the type of an existing column. See the  description  under
              argtype  above  on how to reference the type of an existing col-
              umn.

              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(7,n,1 builtins) of  items,  rather
              than a single item.

       langname
              The  name  of  the language that the function is implemented in.
              May be SQL, C, internal, or the name of a user-defined procedur-
              al language. (See also createlang [createlang(1)].) 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 should  be  speci-
              fied.  If  none of these appear, VOLATILE is the default assump-
              tion.

              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(1,8) 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(1,2,n) 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(1,8)  this  sense;
              some  examples  are  random(3,4,6)(), currval(), timeofday(). Note that
              any function that has side-effects must be classified  volatile,
              even  if(3,n)  its result is quite predictable, to prevent calls from
              being optimized away; an example is setval().

       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(3,n) 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 auto-
              matically.

       [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(1,8) SQL, this feature does not only apply
              to external functions.

       definition
              A string(3,n) defining the function; the meaning depends on the  lan-
              guage.  It  may  be  an  internal  function name, the path to an
              object file(1,n), an SQL command, or text in(1,8) 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(1,8) the C language
              source code is not the same as the name of the SQL function. The
              string(3,n)  obj_file  is the name of the file(1,n) containing the dynami-
              cally loadable object, and link_symbol is  the  function's  link(1,2)
              symbol,  that  is,  the  name  of the function in(1,8) the C language
              source code. If the link(1,2) 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 section called ``User-Defined Functions'' in(1,8) the documen-
       tation 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 pre-
       cision field for type numeric) are the responsibility of the underlying
       function  implementation  and  are silently swallowed (i.e., not recog-
       nized 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  differ-
       ent,  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(1,n),  the
       file(1,n) is only loaded once. To unload and reload the file(1,n) (perhaps during
       development), use the LOAD [load(7,n)(7)] command.

       Use DROP FUNCTION to remove user-defined functions.

       Any single quotes or backslashes in(1,8) 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 section called ``User-Defined Functions'' in(1,8) the
       documentation.

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


COMPATIBILITY
       A CREATE FUNCTION command is defined in(1,8) SQL99.  The PostgreSQL  version(1,3,5)
       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(7)],
       GRANT [grant(7)], LOAD [load(7,n)(7)], REVOKE [revoke(7)], createlang(1)



SQL - Language Statements         2003-11-02                CREATE FUNCTION(7)

References for this manual (incoming links)