From 47888fe84227aaf3decffc7204554bdec54d2b29 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Thu, 31 Mar 2005 22:46:33 +0000 Subject: First phase of OUT-parameters project. We can now define and use SQL functions with OUT parameters. The various PLs still need work, as does pg_dump. Rudimentary docs and regression tests included. --- doc/src/sgml/ref/create_function.sgml | 76 +++++++++++++++++++++--- doc/src/sgml/xfunc.sgml | 108 ++++++++++++++++++++++++++++++++-- 2 files changed, 171 insertions(+), 13 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/ref/create_function.sgml b/doc/src/sgml/ref/create_function.sgml index 0991e96a54a..768a42846b6 100644 --- a/doc/src/sgml/ref/create_function.sgml +++ b/doc/src/sgml/ref/create_function.sgml @@ -1,5 +1,5 @@ @@ -19,8 +19,9 @@ $PostgreSQL: pgsql/doc/src/sgml/ref/create_function.sgml,v 1.64 2005/01/04 00:39 -CREATE [ OR REPLACE ] FUNCTION name ( [ [ argname ] argtype [, ...] ] ) - RETURNS rettype +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 @@ -57,7 +58,9 @@ CREATE [ OR REPLACE ] FUNCTION name 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. + 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.) @@ -88,6 +91,17 @@ CREATE [ OR REPLACE ] FUNCTION name + + argmode + + + + The mode of an argument: either IN, OUT, + or INOUT. If omitted, the default is IN. + + + + argname @@ -95,7 +109,10 @@ CREATE [ OR REPLACE ] FUNCTION name The name of an argument. Some languages (currently only PL/pgSQL) let you use the name in the function body. For other languages the - argument name is just extra documentation. + name of an input argument is just extra documentation. 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.) @@ -137,6 +154,13 @@ CREATE [ OR REPLACE ] FUNCTION name Depending on the implementation language it may also be allowed to specify pseudotypes such as cstring. + + 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 parameter. + The SETOF modifier indicates that the function will return a set of @@ -361,6 +385,16 @@ CREATE [ OR REPLACE ] FUNCTION name 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 @@ -393,7 +427,7 @@ CREATE [ OR REPLACE ] FUNCTION name Examples - Here is a trivial example to help you get started. For more + Here are some trivial examples to help you get started. For more information and examples, see . CREATE FUNCTION add(integer, integer) RETURNS integer @@ -407,13 +441,34 @@ CREATE FUNCTION add(integer, integer) RETURNS integer 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 + composite 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); @@ -428,6 +483,13 @@ $$ LANGUAGE plpgsql; 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. + diff --git a/doc/src/sgml/xfunc.sgml b/doc/src/sgml/xfunc.sgml index 83af1f93f70..079773d0d46 100644 --- a/doc/src/sgml/xfunc.sgml +++ b/doc/src/sgml/xfunc.sgml @@ -1,5 +1,5 @@ @@ -172,7 +172,7 @@ INSERT INTO $1 VALUES (42); - + <acronym>SQL</acronym> Functions on Base Types @@ -484,7 +484,7 @@ SELECT emp.name, emp.double_salary FROM emp; - Another way to use a function returning a row result is to pass the + Another way to use a function returning a composite type is to pass the result to another function that accepts the correct row type as input: @@ -501,8 +501,89 @@ SELECT getname(new_emp()); - Another way to use a function that returns a composite type is to - call it as a table function, as described below. + Still another way to use a function that returns a composite type is to + call it as a table function, as described in . + + + + + Functions with Output Parameters + + + function + output parameter + + + + An alternative way of describing a function's results is to define it + with output parameters, as in this example: + + +CREATE FUNCTION add_em (IN x int, IN y int, OUT sum int) +AS 'SELECT $1 + $2' +LANGUAGE SQL; + +SELECT add_em(3,7); + add_em +-------- + 10 +(1 row) + + + This is not essentially different from the version of add_em + shown in . The real value of + output parameters is that they provide a convenient way of defining + functions that return several columns. For example, + + +CREATE FUNCTION sum_n_product (x int, y int, OUT sum int, OUT product int) +AS 'SELECT $1 + $2, $1 * $2' +LANGUAGE SQL; + + SELECT * FROM sum_n_product(11,42); + sum | product +-----+--------- + 53 | 462 +(1 row) + + + What has essentially happened here is that we have created an anonymous + composite type for the result of the function. The above example has + the same end result as + + +CREATE TYPE sum_prod AS (sum int, product int); + +CREATE FUNCTION sum_n_product (int, int) RETURNS sum_prod +AS 'SELECT $1 + $2, $1 * $2' +LANGUAGE SQL; + + + but not having to bother with the separate composite type definition + is often handy. + + + + Notice that output parameters are not included in the calling argument + list when invoking such a function from SQL. This is because + PostgreSQL considers only the input + parameters to define the function's calling signature. That means + also that only the input parameters matter when referencing the function + for purposes such as dropping it. We could drop the above function + with either of + + +DROP FUNCTION sum_n_product (x int, y int, OUT sum int, OUT product int); +DROP FUNCTION sum_n_product (int, int); + + + + + Parameters can be marked as IN (the default), + OUT, or INOUT. An INOUT + parameter serves as both an input parameter (part of the calling + argument list) and an output parameter (part of the result record type). @@ -692,6 +773,21 @@ CREATE FUNCTION invalid_func() RETURNS anyelement AS $$ $$ LANGUAGE SQL; ERROR: cannot determine result data type DETAIL: A function returning "anyarray" or "anyelement" must have at least one argument of either type. + + + + + Polymorphism can be used with functions that have output arguments. + For example: + +CREATE FUNCTION dup (f1 anyelement, OUT f2 anyelement, OUT f3 anyarray) +AS 'select $1, array[$1,$1]' LANGUAGE sql; + +SELECT * FROM dup(22); + f2 | f3 +----+--------- + 22 | {22,22} +(1 row) @@ -962,7 +1058,7 @@ CREATE FUNCTION square_root(double precision) RETURNS double precision C-Language Functions - + function user-defined in C -- cgit v1.2.3