create_function man page on Mandriva

Man page or keyword search:  
man Server   17060 pages
apropos Keyword Search (all sections)
Output format
Mandriva logo
[printable version]

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

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 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
       input  argument types in the same schema. However, functions of differ‐
       ent 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  cre‐
       ating 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  cre‐
	      ate.

       argmode
	      The  mode of an argument: IN, OUT, INOUT, or VARIADIC.  If omit‐
	      ted, the default is IN.  Only OUT arguments can follow  a	 VARI‐
	      ADIC 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 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 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.column‐
	      name%TYPE.  Using this feature can sometimes help make  a	 func‐
	      tion 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	 argu‐
	      ment type of the parameter.  Only input (including INOUT) param‐
	      eters 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 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.

       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 procedur‐
	      al  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	 func‐
	      tions 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 appropri‐
	      ate  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 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.

       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	 func‐
	      tions,  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 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 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 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
       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  consid‐
       ered  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 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).

       If  a function is declared STRICT with a VARIADIC argument, the strict‐
       ness 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 infor‐
       mation 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  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);

       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.  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;
       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 pur‐
       pose.

       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	 secu‐
       rity  definer  function to only some users. To do that, you must revoke
       the default PUBLIC privileges and then grant execute  privilege	selec‐
       tively.	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 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.

       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 syn‐
       tax 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 [cre‐
       atelang(1)]

SQL - Language Statements	  2010-10-01		    CREATE FUNCTION(7)
[top]

List of man pages available for Mandriva

Copyright (c) for man pages and the logo by the respective OS vendor.

For those who want to learn more, the polarhome community provides shell access and support.

[legal] [privacy] [GNU] [policy] [cookies] [netiquette] [sponsors] [FAQ]
Tweet
Polarhome, production since 1999.
Member of Polarhome portal.
Based on Fawad Halim's script.
....................................................................
Vote for polarhome
Free Shell Accounts :: the biggest list on the net