create_function man page on SuSE

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

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

       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.

       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.

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

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

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.

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	  2013-02-04		     CREATE FUNCTION()
[top]

List of man pages available for SuSE

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