Unix/Linux Go Back    

RedHat 9 (Linux i386) - man page for create_function (redhat section 7)

Linux & Unix Commands - Search Man Pages
Man Page or Keyword Search:   man
Select Man Page Set:       apropos Keyword Search (sections above)

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

       CREATE FUNCTION - define a new function

       CREATE [ OR REPLACE ] FUNCTION name ( [ argtype [, ...] ] )
	   RETURNS rettype
	 { LANGUAGE langname
	   | AS 'definition'
	   | AS 'obj_file', 'link_symbol'
	 } ...
	   [ WITH ( attribute [, ...] ) ]

       CREATE  FUNCTION  defines a new function.  CREATE OR REPLACE FUNCTION will either create a
       new function, or replace an existing definition.

       The user that creates the function becomes the owner of the function.  "PARAMETERS"

       name   The name of a function to create. If a schema name is included, then  the  function
	      is  created  in the specified schema. Otherwise it is created in the current schema
	      (the one at the front of the search path; see 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).

	      The  data type(s) of the function's arguments, if any. The input types may be base,
	      complex, or domain types, or the same as the type of an existing column.	The  type
	      of  a  column  is  referenced  by writing tablename.columnname%TYPE; using this can
	      sometimes help make a function independent from changes to the definition of a  ta-
	      ble.   Depending	on  the implementation language it may also be allowed to specify
	      ``pseudo-types'' such as cstring.  Pseudo-types indicate that the  actual  argument
	      type  is	either	incompletely  specified,  or outside the set of ordinary SQL data

	      The return data type. The return type may be  specified  as  a  base,  complex,  or
	      domain  type,  or  the  same  as	the type of an existing column.  Depending on the
	      implementation language it may also be allowed to specify ``pseudo-types'' such  as
	      cstring.	 The  setof  modifier  indicates  that	the function will return a set of
	      items, rather than a single item.

	      The name of the language that the function is  implemented  in.	May  be  SQL,  C,
	      internal,  or  the name of a user-defined procedural language. (See also createlang
	      [createlang(1)].) For backward compatibility, the name may be  enclosed  by  single



	      These  attributes  inform the system whether it is safe to replace multiple evalua-
	      tions of the function with a single evaluation, for run-time optimization. At  most
	      one  choice  should  be specified. If none of these appear, VOLATILE is the default

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



       STRICT CALLED ON NULL INPUT (the default) indicates that the function will be called  nor-
	      mally when some of its arguments are null. It is then the function author's respon-
	      sibility 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 automatically.


	      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 compatibility but is optional since,
	      unlike in SQL, this feature does not only apply to external functions.

	      A string defining the function; the meaning depends on the language. It may  be  an
	      internal function name, the path to an object file, an SQL query, or text in a pro-
	      cedural language.

       obj_file, link_symbol
	      This form of the AS clause is used for dynamically linked 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 object's link symbol, that is, the
	      name of the function in the C language source code.

	      The historical way to specify optional pieces of information  about  the	function.
	      The following attributes may appear here:


		     isCachable  is  an obsolete equivalent of IMMUTABLE; it's still accepted for
		     backwards-compatibility reasons.

       Attribute names are not case-sensitive.

       Refer to the chapter in the PostgreSQL Programmer's Guide on the topic of extending  Post-
       greSQL via functions for further information on writing external functions.

       The  full  SQL  type syntax is allowed for input arguments and return value. However, some
       details of the type specification (e.g., the precision field for numeric  types)  are  the
       responsibility of the underlying function implementation and are silently swallowed (i.e.,
       not recognized 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. This facility must be
       used with caution for internal and C-language functions, however.

       Two internal functions cannot have the same C name without causing errors at link time. To
       get  around that, give them different C names (for example, use the argument types as part
       of the C names), then specify those names in the AS clause of CREATE FUNCTION.  If the  AS
       clause  is left empty, then CREATE FUNCTION assumes the C name of the function is the same
       as the SQL name.

       Similarly, when overloading SQL function names with multiple  C-language  functions,  give
       each C-language instance of the function a distinct name, then use the alternative form of
       the AS clause in the CREATE FUNCTION syntax to select the appropriate C-language implemen-
       tation of each overloaded SQL function.

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

       Use DROP FUNCTION to remove user-defined functions.

       To  update  the	definition  of an existing function, use CREATE OR REPLACE FUNCTION. Note
       that it is not possible to change the name or argument types of a function  this  way  (if
       you tried, you'd just be creating a new, distinct function). Also, CREATE OR REPLACE FUNC-
       TION will not let you change the return type of an existing function.   To  do  that,  you
       must drop and re-create the function.

       If  you drop and then re-create 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  func-
       tion.  Use  CREATE  OR  REPLACE	FUNCTION to change a function definition without breaking
       objects that refer to the function.

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

       By default, only the owner (creator) of the function has the right to  execute  it.  Other
       users must be granted the EXECUTE privilege on the function to be able to use it.

       To create a simple SQL function:

       CREATE FUNCTION one() RETURNS integer

       SELECT one() AS answer;

       The  next  example  creates  a  C function by calling a routine from a user-created shared
       library named funcs.so (the extension may vary across platforms). The shared library  file
       is  sought in the server's dynamic library search path. This particular routine calculates
       a check digit and returns true if the check digit in the function parameters  is  correct.
       It is intended for use in a CHECK constraint.

       CREATE FUNCTION ean_checkdigit(char, char) RETURNS boolean
	   AS 'funcs' LANGUAGE C;

       CREATE TABLE product (
	   id	     char(8) PRIMARY KEY,
	   eanprefix char(8) CHECK (eanprefix ~ '[0-9]{2}-[0-9]{5}')
			     REFERENCES brandname(ean_prefix),
	   eancode   char(6) CHECK (eancode ~ '[0-9]{6}'),
	   CONSTRAINT ean    CHECK (ean_checkdigit(eanprefix, eancode))

       The  next  example creates a function that does type conversion from the user-defined type
       complex to the built-in type point. The function is implemented by  a  dynamically  loaded
       object  that  was  compiled from C source (we illustrate the now-deprecated alternative of
       specifying the absolute file name to the shared object file).  For PostgreSQL  to  find	a
       type  conversion function automatically, the SQL function has to have the same name as the
       return type, and so overloading is unavoidable. The function name is overloaded	by  using
       the second form of the AS clause in the SQL definition:

       CREATE FUNCTION point(complex) RETURNS point
	   AS '/home/bernie/pgsql/lib/complex.so', 'complex_to_point'

       The C declaration of the function could be:

       Point * complex_to_point (Complex *z)
	    Point *p;

	    p = (Point *) palloc(sizeof(Point));
	    p->x = z->x;
	    p->y = z->y;

	    return p;

       Note  that  the	function  is  marked ``strict''; this allows us to skip checking for NULL
       input in the function body.

       A CREATE FUNCTION command is defined in SQL99.  The PostgreSQL version is similar but  not
       fully  compatible.  The	attributes  are not portable, neither are the different available

       DROP FUNCTION [drop_function(7)], GRANT [grant(7)], LOAD  [load(7)],  REVOKE  [revoke(7)],
       createlang(1), PostgreSQL Programmer's Guide

SQL - Language Statements		    2002-11-22			       CREATE FUNCTION(7)
Unix & Linux Commands & Man Pages : ©2000 - 2018 Unix and Linux Forums

All times are GMT -4. The time now is 10:42 PM.