create_function man page on BSDOS

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

CREATE FUNCTION()		 SQL Commands		     CREATE FUNCTION()

NAME
       CREATE FUNCTION - Defines a new function

SYNOPSIS
       CREATE FUNCTION name ( [ ftype [, ...] ] )
	   RETURNS rtype
	   AS definition
	   LANGUAGE 'langname'
	   [ WITH ( attribute [, ...] ) ]
       CREATE FUNCTION name ( [ ftype [, ...] ] )
	   RETURNS rtype
	   AS obj_file , link_symbol
	   LANGUAGE 'langname'
	   [ WITH ( attribute [, ...] ) ]

   INPUTS
       name   The name of a function to create.

       ftype  The data type(s) of the function's arguments, if any.  The input
	      types may be base or complex types, or opaque.  Opaque indicates
	      that  the	 function  accepts arguments of a non-SQL type such as
	      char *.

       rtype  The return data type.  The output type may  be  specified	 as  a
	      base type, complex type, setof type, or opaque.  The setof modi‐
	      fier indicates that the function will return  a  set  of	items,
	      rather than a single item.

       attribute
	      An  optional  piece  of information about the function, used for
	      optimization. See below for details.

       definition
	      A string defining the function; the meaning depends on the  lan‐
	      guage.   It  may	be  an	internal function name, the path to an
	      object file, an SQL query, or text in a procedural 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 sym‐
	      bol,  that  is the name of the function in the C language source
	      code.

       langname
	      May be 'sql', 'C', 'internal', or 'plname',  where  'plname'  is
	      the  name	 of a created procedural language. See CREATE LANGUAGE
	      [create_language(l)] for details.

   OUTPUTS
       CREATE This is returned if the command completes successfully.

DESCRIPTION
       CREATE FUNCTION allows a Postgres user to register a function with  the
       database.  Subsequently, this user is considered the owner of the func‐
       tion.

   FUNCTION ATTRIBUTES
       The following items may appear in the WITH clause:

       iscachable
	      Iscachable indicates that the function always returns  the  same
	      result when given the same argument values (i.e., it does not do
	      database lookups	or  otherwise  use  information	 not  directly
	      present in its parameter list). The optimizer uses iscachable to
	      know whether it is safe to pre-evaluate a call of the function.

       isstrict
	      isstrict indicates that the function always returns  NULL	 when‐
	      ever  any of its arguments are NULL. If this attribute is speci‐
	      fied, the function is not executed when  there  are  NULL	 argu‐
	      ments;  instead  a  NULL	result is assumed automatically.  When
	      isstrict is not specified, the function will be called for  NULL
	      inputs. It is then the function author's responsibility to check
	      for NULLs if necessary and respond appropriately.

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

       Use DROP FUNCTION to remove user-defined functions.

       The full SQL92 type syntax is allowed for input	arguments  and	return
       value.  However, some details of the type specification (e.g., the pre‐
       cision field for numeric types) are the responsibility of the  underly‐
       ing  function implementation and are silently swallowed (i.e., not rec‐
       ognized or enforced) by the CREATE FUNCTION command.

       Postgres 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 implementation of
       each overloaded SQL function.

USAGE
       To create a simple SQL function:

       CREATE FUNCTION one() RETURNS int4
	   AS 'SELECT 1 AS RESULT'
	   LANGUAGE 'sql';
       SELECT one() AS answer;

	    answer
       --------
	     1

       This example creates a C function by calling a routine from a user-cre‐
       ated  shared  library. This particular routine calculates a check digit
       and returns TRUE if the check digit in the function parameters is  cor‐
       rect. It is intended for use in a CHECK contraint.

       CREATE FUNCTION ean_checkdigit(bpchar, bpchar) RETURNS boolean
	   AS '/usr1/proj/bray/sql/funcs.so' 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))
       );

       This  example  creates a function that does type conversion between the
       user-defined type complex, and the internal type point. The function is
       implemented  by	a  dynamically	loaded object that was compiled from C
       source. For Postgres 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'
	   LANGUAGE 'c';

       The C declaration of the function is:

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

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

	    return p;
       }

COMPATIBILITY
   SQL92
       CREATE FUNCTION is a Postgres language extension.

   SQL/PSM
	      Note:  PSM  stands for Persistent Stored Modules. It is a proce‐
	      dural language and it was originally hoped  that	PSM  would  be
	      ratified	as  an official standard by late 1996. As of mid-1998,
	      this has not yet happened, but it is hoped that PSM will eventu‐
	      ally become a standard.

       SQL/PSM CREATE FUNCTION has the following syntax:

       CREATE FUNCTION name
	   ( [ [ IN | OUT | INOUT ] type [, ...] ] )
	    RETURNS rtype
	    LANGUAGE 'langname'
	    ESPECIFIC routine
	    SQL-statement

SQL - Language Statements	 29 March 2001		     CREATE FUNCTION()
[top]
                             _         _         _ 
                            | |       | |       | |     
                            | |       | |       | |     
                         __ | | __ __ | | __ __ | | __  
                         \ \| |/ / \ \| |/ / \ \| |/ /  
                          \ \ / /   \ \ / /   \ \ / /   
                           \   /     \   /     \   /    
                            \_/       \_/       \_/ 
More information is available in HTML format for server BSDOS

List of man pages available for BSDOS

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