Yolinux.com

create_function manpage

Search topic Section


CREATE FUNCTION()		 SQL Commands		     CREATE FUNCTION()



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 spec-
       ified  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 cre-
	      ate.

       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 documenta-
	      tion. 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	 indi-
	      cate that the actual argument type is either incompletely speci-
	      fied, or outside the set of ordinary SQL data types.

	      The type of a column is referenced by writing  tablename.column-
	      name%TYPE.   Using  this feature can sometimes help make a func-
	      tion 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.

	      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 parame-
	      ter.

	      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.column-
	      name%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 procedur-
	      al  language.  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 may be specified.
	      If none of these appear, VOLATILE is the default assumption.

	      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 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 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 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 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 allowed for SQL conformance, but it  is
	      optional since, unlike in SQL, this feature applies to all func-
	      tions 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  lan-
	      guage.

       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  dynami-
	      cally  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 the documentation for further  information  on	writing	 func-
       tions.

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

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

       When  CREATE  OR	 REPLACE FUNCTION is used to replace an existing func-
       tion, 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).

EXAMPLES
       Here are some trivial examples to help you get started. For more infor-
       mation and examples, see 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  com-
       posite 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. Particu-
       larly 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	 Post-
       greSQL  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 [cre-
       atelang(1)]



SQL - Language Statements	  2010-12-14		     CREATE FUNCTION()