create_function man page on OpenServer

Man page or keyword search:  
man Server   5388 pages
apropos Keyword Search (all sections)
Output format
OpenServer 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
	   | 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.  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 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 query optimizer about	 the  behavior
	      of the function. At most one choice may 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.

       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 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. To unload and reload the file (perhaps during
       development), use the LOAD [load(5)] command.

       Use DROP FUNCTION [drop_function(5)] 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.

       To be able to define a function, the user must have the USAGE privilege
       on the language.

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;
	       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(5)], DROP FUNCTION [drop_function(l)],
       GRANT [grant(l)], LOAD [load(l)], REVOKE [revoke(l)], createlang	 [cre‐
       atelang(1)]

SQL - Language Statements	  2008-06-08		     CREATE FUNCTION()
[top]
                             _         _         _ 
                            | |       | |       | |     
                            | |       | |       | |     
                         __ | | __ __ | | __ __ | | __  
                         \ \| |/ / \ \| |/ / \ \| |/ /  
                          \ \ / /   \ \ / /   \ \ / /   
                           \   /     \   /     \   /    
                            \_/       \_/       \_/ 
More information is available in HTML format for server OpenServer

List of man pages available for OpenServer

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