diff options
Diffstat (limited to 'doc/src/sgml/ref/create_function.sgml')
| -rw-r--r-- | doc/src/sgml/ref/create_function.sgml | 570 |
1 files changed, 0 insertions, 570 deletions
diff --git a/doc/src/sgml/ref/create_function.sgml b/doc/src/sgml/ref/create_function.sgml deleted file mode 100644 index b2d2314a733..00000000000 --- a/doc/src/sgml/ref/create_function.sgml +++ /dev/null @@ -1,570 +0,0 @@ -<!-- -$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_function.sgml,v 1.39 2002/05/18 13:47:59 petere Exp $ ---> - -<refentry id="SQL-CREATEFUNCTION"> - <refmeta> - <refentrytitle id="SQL-CREATEFUNCTION-TITLE">CREATE FUNCTION</refentrytitle> - <refmiscinfo>SQL - Language Statements</refmiscinfo> - </refmeta> - - <refnamediv> - <refname>CREATE FUNCTION</refname> - <refpurpose>define a new function</refpurpose> - </refnamediv> - - <refsynopsisdiv> -<synopsis> -CREATE [ OR REPLACE ] FUNCTION <replaceable class="parameter">name</replaceable> ( [ <replaceable class="parameter">argtype</replaceable> [, ...] ] ) - RETURNS <replaceable class="parameter">rettype</replaceable> - { LANGUAGE <replaceable class="parameter">langname</replaceable> - | IMMUTABLE | STABLE | VOLATILE - | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT - | IMPLICIT CAST - | [EXTERNAL] SECURITY INVOKER | [EXTERNAL] SECURITY DEFINER - | AS '<replaceable class="parameter">definition</replaceable>' - | AS '<replaceable class="parameter">obj_file</replaceable>', '<replaceable class="parameter">link_symbol</replaceable>' - } ... - [ WITH ( <replaceable class="parameter">attribute</replaceable> [, ...] ) ] -</synopsis> - </refsynopsisdiv> - - <refsect1 id="sql-createfunction-description"> - <title>Description</title> - - <para> - <command>CREATE FUNCTION</command> defines a new function. - <command>CREATE OR REPLACE FUNCTION</command> will either create a - new function, or replace an existing definition. - </para> - - <para> - The user that creates the function becomes the owner of the function. - </para> - - <variablelist> - <title>Parameters</title> - - <varlistentry> - <term><replaceable class="parameter">name</replaceable></term> - - <listitem> - <para> - 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 <literal>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 - <firstterm>overloading</>). - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><replaceable class="parameter">argtype</replaceable></term> - - <listitem> - <para> - The data type(s) of the function's arguments, if any. The - input types may be base or complex types, - <literal>opaque</literal>, or the same as the type of an - existing column. <literal>Opaque</literal> indicates - that the function accepts arguments of a non-SQL type such as - <type>char *</type>. - The type of a column is indicated using <replaceable - class="parameter">tablename</replaceable>.<replaceable - class="parameter">columnname</replaceable><literal>%TYPE</literal>; - using this can sometimes help make a function independent from - changes to the definition of a table. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><replaceable class="parameter">rettype</replaceable></term> - - <listitem> - <para> - The return data type. The return type may be specified as a - base type, complex type, <literal>setof</literal> type, - <literal>opaque</literal>, or the same as the type of an - existing column. - The <literal>setof</literal> - modifier indicates that the function will return a set of - items, rather than a single item. Functions with a declared - return type of <literal>opaque</literal> do not return a value. - These cannot be called directly; trigger functions make use of - this feature. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><replaceable class="parameter">langname</replaceable></term> - - <listitem> - <para> - The name of the language that the function is implemented in. - May be <literal>SQL</literal>, <literal>C</literal>, - <literal>internal</literal>, or the name of a user-defined - procedural language. (See also <xref linkend="app-createlang" - endterm="app-createlang-title">.) For backward compatibility, - the name may be enclosed by single quotes. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term>IMMUTABLE</term> - <term>STABLE</term> - <term>VOLATILE</term> - - <listitem> - <para> - These attributes inform the system whether it is safe to - replace multiple evaluations of the function with a single - evaluation, for run-time optimization. At most one choice - should be specified. If none of these appear, - <literal>VOLATILE</literal> is the default assumption. - </para> - - <para> - <literal>IMMUTABLE</literal> 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. - </para> - - <para> - <literal>STABLE</literal> 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 <literal>CURRENT_TIMESTAMP</> family of functions qualify - as stable, since their values do not change within a transaction. - </para> - - <para> - <literal>VOLATILE</literal> 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 <literal>random()</>, <literal>currval()</>, - <literal>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 - <literal>setval()</>. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term>CALLED ON NULL INPUT</term> - <term>RETURNS NULL ON NULL INPUT</term> - <term>STRICT</term> - - <listitem> - <para> - <literal>CALLED ON NULL INPUT</literal> (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 NULLs if necessary and respond - appropriately. - </para> - - <para> - <literal>RETURNS NULL ON NULL INPUT</literal> or - <literal>STRICT</literal> 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. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><literal>IMPLICIT CAST</literal</term> - - <listitem> - <para> - Indicates that the function may be used for implicit type - conversions. See <xref linkend="sql-createfunction-cast-functions" - endterm="sql-createfunction-cast-functions-title"> for more detail. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><optional>EXTERNAL</optional> SECURITY INVOKER</term> - <term><optional>EXTERNAL</optional> SECURITY DEFINER</term> - - <listitem> - <para> - <literal>SECURITY INVOKER</literal> indicates that the function - is to be executed with the privileges of the user that calls it. - That is the default. <literal>SECURITY DEFINER</literal> - specifies that the function is to be executed with the - privileges of the user that created it. - </para> - - <para> - The key word <literal>EXTERNAL</literal> is present for SQL - compatibility but is optional since, unlike in SQL, this feature - does not only apply to external functions. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><replaceable class="parameter">definition</replaceable></term> - - <listitem> - <para> - 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 procedural language. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><replaceable class="parameter">obj_file</replaceable>, <replaceable class="parameter">link_symbol</replaceable></term> - - <listitem> - <para> - This form of the <literal>AS</literal> 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 <replaceable - class="parameter">obj_file</replaceable> is the name of the - file containing the dynamically loadable object, and - <replaceable class="parameter">link_symbol</replaceable> is the - object's link symbol, that is, the name of the function in the C - language source code. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><replaceable class="parameter">attribute</replaceable></term> - - <listitem> - <para> - The historical way to specify optional pieces of information - about the function. The following attributes may appear here: - - <variablelist> - <varlistentry> - <term>isStrict</term> - <listitem> - <para> - Equivalent to <literal>STRICT</literal> or <literal>RETURNS NULL ON NULL INPUT</literal> - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term>isImmutable</term> - <term>isCachable</term> - <term>isStable</term> - <term>isVolatile</term> - <listitem> - <para> - Equivalent to <literal>IMMUTABLE</literal>, - <literal>STABLE</literal>, <literal>VOLATILE</literal>. - <literal>isCachable</literal> is an obsolete equivalent of - <literal>isImmutable</literal>; it's still accepted for - backwards-compatibility reasons. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term>implicitCoercion</term> - <listitem> - <para> - Same as <literal>IMPLICIT CAST</literal> - </para> - </listitem> - </varlistentry> - </variablelist> - - Attribute names are not case-sensitive. - </para> - </listitem> - </varlistentry> - - </variablelist> - - </refsect1> - - <refsect1 id="sql-createfunction-notes"> - <title>Notes</title> - - <para> - Refer to the chapter in the - <citetitle>PostgreSQL Programmer's Guide</citetitle> - on the topic of extending - <productname>PostgreSQL</productname> via functions - for further information on writing external functions. - </para> - - <para> - The full <acronym>SQL</acronym> type syntax is allowed for - input arguments and return value. However, some details of the - type specification (e.g., the precision field for - <type>numeric</type> types) are the responsibility of the - underlying function implementation and are silently swallowed - (i.e., not recognized or - enforced) by the <command>CREATE FUNCTION</command> command. - </para> - - <para> - <productname>PostgreSQL</productname> allows function <firstterm>overloading</firstterm>; - 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. - </para> - - <para> - Two <literal>internal</literal> - 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 <command>CREATE FUNCTION</command>. - If the AS clause is left empty, then <command>CREATE FUNCTION</command> - assumes the C name of the function is the same as the SQL name. - </para> - - <para> - 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 <command>AS</command> clause in the - <command>CREATE FUNCTION</command> syntax to select the appropriate - C-language implementation of each overloaded SQL function. - </para> - - <para> - When repeated <command>CREATE FUNCTION</command> calls refer to - the same object file, the file is only loaded once. To unload and - reload the file (perhaps during development), use the <xref - linkend="sql-load" endterm="sql-load-title"> command. - </para> - - <para> - Use <command>DROP FUNCTION</command> - to remove user-defined functions. - </para> - - <para> - To update the definition of an existing function, use - <command>CREATE OR REPLACE FUNCTION</command>. 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, <command>CREATE OR REPLACE FUNCTION</command> - will not let you change the return type of an existing function. - To do that, you must drop and re-create the function. - </para> - - <para> - 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 function. Use - <command>CREATE OR REPLACE FUNCTION</command> to change a function - definition without breaking objects that refer to the function. - </para> - - <para> - To be able to define a function, the user must have the - <literal>USAGE</literal> privilege on the language. - </para> - - <para> - By default, only the owner (creator) of the function has the right - to execute it. Other users must be granted the - <literal>EXECUTE</literal> privilege on the function to be able to - use it. - </para> - </refsect1> - - <refsect1 id="sql-createfunction-cast-functions"> - <title id="sql-createfunction-cast-functions-title"> - Type Cast Functions - </title> - <para> - A function that has one argument and is named the same as its return - data type (including the schema name) is considered to be a <firstterm>type - casting function</>: it can be invoked to convert a value of its input - data type into a value - of its output datatype. For example, -<programlisting> -SELECT CAST(42 AS text); -</programlisting> - converts the integer constant 42 to text by invoking a function - <literal>text(int4)</>, if such a function exists and returns type - text. (If no suitable conversion function can be found, the cast fails.) - </para> - - <para> - If a potential cast function is marked <literal>IMPLICIT CAST</>, - then it can be invoked implicitly in any context where the - conversion it defines is required. Cast functions not so marked - can be invoked only by explicit <literal>CAST</>, - <replaceable>x</><literal>::</><replaceable>typename</>, or - <replaceable>typename</>(<replaceable>x</>) constructs. For - example, supposing that <literal>foo.f1</literal> is a column of - type <type>text</type>, then -<programlisting> -INSERT INTO foo(f1) VALUES(42); -</programlisting> - will be allowed if <literal>text(int4)</> is marked - <literal>IMPLICIT CAST</>, otherwise not. - </para> - - <para> - It is wise to be conservative about marking cast functions as - implicit casts. An overabundance of implicit casting paths can - cause <productname>PostgreSQL</productname> to choose surprising - interpretations of commands, or to be unable to resolve commands at - all because there are multiple possible interpretations. A good - rule of thumb is to make cast implicitly invokable only for - information-preserving transformations between types in the same - general type category. For example, <type>int2</type> to - <type>int4</type> casts can reasonably be implicit, but be wary of - marking <type>int4</type> to <type>text</type> or - <type>float8</type> to <type>int4</type> as implicit casts. - </para> - </refsect1> - - <refsect1 id="sql-createfunction-examples"> - <title>Examples</title> - - <para> - To create a simple SQL function: - -<programlisting> -CREATE FUNCTION one() RETURNS integer - AS 'SELECT 1 AS RESULT;' - LANGUAGE SQL; - -SELECT one() AS answer; -<computeroutput> - answer --------- - 1 -</computeroutput> -</programlisting> - </para> - - <para> - The next example creates a C function by calling a routine from a - user-created shared library named <filename>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. - -<programlisting> -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)) -); -</programlisting> - </para> - - <para> - 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 <productname>PostgreSQL</productname> 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 <command>AS</command> clause in the SQL definition: - -<programlisting> -CREATE FUNCTION point(complex) RETURNS point - AS '/home/bernie/pgsql/lib/complex.so', 'complex_to_point' - LANGUAGE C STRICT; -</programlisting> - - The C declaration of the function could be: - -<programlisting> -Point * complex_to_point (Complex *z) -{ - Point *p; - - p = (Point *) palloc(sizeof(Point)); - p->x = z->x; - p->y = z->y; - - return p; -} -</programlisting> - - Note that the function is marked <quote>strict</>; this allows us - to skip checking for NULL input in the function body. - </para> - </refsect1> - - - <refsect1 id="sql-createfunction-compat"> - <title>Compatibility</title> - - <para> - A <command>CREATE FUNCTION</command> command is defined in SQL99. - The <application>PostgreSQL</application> version is similar but - not fully compatible. The attributes are not portable, neither are the - different available languages. - </para> - </refsect1> - - - <refsect1 id="sql-createfunction-seealso"> - <title>See Also</title> - - <para> - <xref linkend="sql-dropfunction" endterm="sql-dropfunction-title">, - <xref linkend="sql-grant" endterm="sql-grant-title">, - <xref linkend="sql-load" endterm="sql-load-title">, - <xref linkend="sql-revoke" endterm="sql-revoke-title">, - <xref linkend="app-createlang">, - <citetitle>PostgreSQL Programmer's Guide</citetitle> - </para> - </refsect1> - -</refentry> - -<!-- Keep this comment at the end of the file -Local variables: -mode:sgml -sgml-omittag:nil -sgml-shorttag:t -sgml-minimize-attributes:nil -sgml-always-quote-attributes:t -sgml-indent-step:1 -sgml-indent-data:t -sgml-parent-document:nil -sgml-default-dtd-file:"../reference.ced" -sgml-exposed-tags:nil -sgml-local-catalogs:("/usr/lib/sgml/catalog") -sgml-local-ecat-files:nil -End: ---> |