From 94bdc4855cf8db8a2df83a19d18ce72fd5eb2691 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Fri, 17 May 2002 18:32:52 +0000 Subject: Extend syntax of CREATE FUNCTION to resemble SQL99. --- doc/src/sgml/ref/create_function.sgml | 348 +++++++++++++++++++--------------- doc/src/sgml/release.sgml | 3 +- 2 files changed, 198 insertions(+), 153 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/ref/create_function.sgml b/doc/src/sgml/ref/create_function.sgml index 18961336531..495a1331464 100644 --- a/doc/src/sgml/ref/create_function.sgml +++ b/doc/src/sgml/ref/create_function.sgml @@ -1,5 +1,5 @@ @@ -17,13 +17,13 @@ $Header: /cvsroot/pgsql/doc/src/sgml/ref/create_function.sgml,v 1.37 2002/04/23 CREATE [ OR REPLACE ] FUNCTION name ( [ argtype [, ...] ] ) RETURNS rettype - AS 'definition' - LANGUAGE langname - [ WITH ( attribute [, ...] ) ] -CREATE [ OR REPLACE ] FUNCTION name ( [ argtype [, ...] ] ) - RETURNS rettype - AS 'obj_file', 'link_symbol' - LANGUAGE langname + { LANGUAGE langname + | IMMUTABLE | STABLE | VOLATILE + | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT + | IMPLICIT CAST + | AS 'definition' + | AS 'obj_file', 'link_symbol' + } ... [ WITH ( attribute [, ...] ) ] @@ -33,8 +33,13 @@ CREATE [ OR REPLACE ] FUNCTION name CREATE FUNCTION defines a new function. - CREATE OR REPLACE FUNCTION will either create - a new function, or replace an existing definition. + 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 @@ -81,7 +86,7 @@ CREATE [ OR REPLACE ] FUNCTION name - The return data type. The output type may be specified as a + The return data type. The return type may be specified as a base type, complex type, setof type, opaque, or the same as the type of an existing column. @@ -95,6 +100,105 @@ CREATE [ OR REPLACE ] FUNCTION name + + langname + + + + 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 .) For backward compatibility, + the name may be enclosed by single quotes. + + + + + + IMMUTABLE + STABLE + VOLATILE + + + + 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, + VOLATILE is the default assumption. + + + + 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(). + + + + + + 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 NULLs 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. + + + + + + IMPLICIT CAST + + + + Indicates that the function may be used for implicit type + conversions. See for more detail. + + + + definition @@ -125,116 +229,56 @@ CREATE [ OR REPLACE ] FUNCTION name - - langname - - - - May be SQL, C, - internal, or plname, where plname is the name of a - created procedural language. See - - for details. For backward compatibility, the name may be - enclosed by single quotes. - - - - attribute - An optional piece of information about the function, used for - optimization. See below for details. - - - + 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 + + + + + + isImmutable + isCachable + isStable + isVolatile + + + Equivalent to IMMUTABLE, + STABLE, VOLATILE. + isCachable is an obsolete equivalent of + isImmutable; it's still accepted for + backwards-compatibility reasons. + + + + + + implicitCoercion + + + Same as IMPLICIT CAST + + + + + + Attribute names are not case-sensitive. + + + - - - - The user that creates the function becomes the owner of the function. - - - - The following attributes may appear in the WITH clause: - - - - isStrict - - - indicates that the function always - returns NULL whenever any of its arguments are NULL. If this - attribute is specified, the function is not executed when there - are NULL arguments; instead a NULL result is assumed automatically. - When 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. - - - - - - isImmutable - isCachable - isStable - isVolatile - - - These attributes inform the system whether it is safe to replace - multiple evaluations of the function with a single evaluation. - At most one choice should be specified. (If none of these appear, - is the default assumption.) - 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. - is an - obsolete equivalent of ; it's still - accepted for backwards-compatibility reasons. - 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 timezone), etc. Also note - that the CURRENT_TIMESTAMP family of functions qualify - as stable, since their values do not change within a transaction. - 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(). - - - - - - implicitCoercion - - - indicates that the function - may be used for implicit type conversions. - See for more detail. - - - - - - Attribute names are not case-sensitive. - @@ -328,21 +372,18 @@ CREATE [ OR REPLACE ] FUNCTION name - - - 2002-04-11 - - - Type Coercion Functions + <refsect1 id="sql-createfunction-cast-function"> + <title id="sql-createfunction-cast-functions-title"> + Type Cast Functions - A function that has one parameter and is named the same as its output - datatype (including the schema name) is considered to be a type - coercion function: it can be invoked to convert a value of its input - datatype into a value + 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 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, - SELECT CAST(42 AS text); +SELECT CAST(42 AS text); converts the integer constant 42 to text by invoking a function text(int4), if such a function exists and returns type @@ -350,31 +391,33 @@ CREATE [ OR REPLACE ] FUNCTION name - If a potential coercion function is marked implicitCoercion, - then it can be invoked in any context where the conversion it defines - is required. Coercion functions not so marked can be invoked only by - explicit CAST, - x::typename, - or typename(x) constructs. - For example, supposing that foo.f1 is a column of type text, then + If a potential cast function is marked 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 CAST, + x::typename, or + typename(x) constructs. For + example, supposing that foo.f1 is a column of + type text, then - INSERT INTO foo(f1) VALUES(42); +INSERT INTO foo(f1) VALUES(42); will be allowed if text(int4) is marked - implicitCoercion, otherwise not. + IMPLICIT CAST, otherwise not. - It is wise to be conservative about marking coercion functions as - implicit coercions. An overabundance of implicit coercion paths - can cause PostgreSQL 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 coercions - implicitly invokable only for information-preserving transformations - between types in the same general type category. For example, int2 - to int4 coercion can reasonably be implicit, but be wary of marking - int4 to text or float8 to int4 as implicit coercions. + It is wise to be conservative about marking cast functions as + implicit casts. An overabundance of implicit casting paths can + cause PostgreSQL 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, int2 to + int4 casts can reasonably be implicit, but be wary of + marking int4 to text or + float8 to int4 as implicit casts. @@ -403,7 +446,7 @@ SELECT one() AS answer; 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 + 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. @@ -422,7 +465,7 @@ CREATE TABLE product ( - This example creates a function that does type conversion from the + 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 @@ -436,7 +479,7 @@ CREATE TABLE product ( CREATE FUNCTION point(complex) RETURNS point AS '/home/bernie/pgsql/lib/complex.so', 'complex_to_point' - LANGUAGE C WITH (isStrict); + LANGUAGE C STRICT; The C declaration of the function could be: @@ -466,7 +509,7 @@ Point * complex_to_point (Complex *z) A CREATE FUNCTION command is defined in SQL99. The PostgreSQL version is similar but - not compatible. The attributes are not portable, neither are the + not fully compatible. The attributes are not portable, neither are the different available languages. @@ -476,10 +519,11 @@ Point * complex_to_point (Complex *z) See Also - , - , - , - , + , + , + , + , + , PostgreSQL Programmer's Guide diff --git a/doc/src/sgml/release.sgml b/doc/src/sgml/release.sgml index 889f2203f69..9b75049938a 100644 --- a/doc/src/sgml/release.sgml +++ b/doc/src/sgml/release.sgml @@ -1,5 +1,5 @@ @@ -24,6 +24,7 @@ CDATA means the content is "SGML-free", so you can write without worries about funny characters. -->