From e107f3a7e3feb7eaef8853ba117465f4f3f8ceed Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Fri, 30 Aug 2002 00:28:41 +0000 Subject: PL/pgSQL functions can return sets. Neil Conway's patch, modified so that the functionality is available to anyone via ReturnSetInfo, rather than hard-wiring it to PL/pgSQL. --- doc/src/sgml/plpgsql.sgml | 86 ++++++++++++++++++++++++++++++++++------------- doc/src/sgml/release.sgml | 3 +- doc/src/sgml/runtime.sgml | 5 +-- doc/src/sgml/xfunc.sgml | 6 ++-- 4 files changed, 70 insertions(+), 30 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index acdd8a4f4dc..4da3f3c0405 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -1,5 +1,5 @@ @@ -1142,11 +1142,20 @@ GET DIAGNOSTICS variable = itemexpression; + RETURN with an expression is used to return from a + PL/pgSQL function that does not return a set. The function terminates and the value of - expression will be returned to the - upper executor. + expression is returned to the caller. + + + + To return a composite (row) value, you must write a record or row + variable as the expression. When + returning a scalar type, any expression can be used. The expression's result will be automatically cast into the function's return type as described for assignments. + (If you have declared the function to return void, + then the expression can be omitted, and will be ignored in any case.) @@ -1155,6 +1164,28 @@ RETURN expression; the function without hitting a RETURN statement, a run-time error will occur. + + + When a PL/pgSQL function is declared to return + SETOF sometype, the procedure + to follow is slightly different. The individual items to be returned + are specified in RETURN NEXT commands, and then a final RETURN with + no argument is given to indicate that the function is done generating + items. + + +RETURN NEXT expression; + + + RETURN NEXT does not actually return from the function; it simply + saves away the value of the expression (or record or row variable, + as appropriate for the datatype being returned). + Execution then continues with the next statement in the + PL/pgSQL function. As successive RETURN NEXT + commands are executed, the result set is built up. A final + RETURN, which need have no argument, causes control to exit + the function. + @@ -1531,8 +1562,8 @@ END LOOP; to worry about that, since FOR loops automatically use a cursor internally to avoid memory problems.) A more interesting usage is to return a reference to a cursor that it has created, allowing the - caller to read the rows. This provides a way to return row sets - from functions. + caller to read the rows. This provides an efficient way to return + large row sets from functions. @@ -1794,19 +1825,27 @@ COMMIT; RAISE level 'format' , variable ...; - Possible levels are DEBUG (write the message into the postmaster log), - NOTICE (write the message into the postmaster log and forward it to - the client application) and EXCEPTION (raise an error, - aborting the transaction). + Possible levels are DEBUG (write the message to + the server log), LOG (write the message to the + server log with a higher priority), INFO, + NOTICE and WARNING (write + the message to the server log and send it to the client, with + respectively higher priorities), and EXCEPTION + (raise an error and abort the current transaction). Whether error + messages of a particular priority are reported to the client, + written to the server log, or both is controlled by the + and + configuration variables. See + the PostgreSQL Administrator's Guide for more + information. - Inside the format string, % is replaced by the next - optional argument's external representation. - Write %% to emit a literal %. - Note that the optional arguments must presently - be simple variables, not expressions, and the format must be a simple - string literal. + Inside the format string, % is replaced by the + next optional argument's external representation. Write + %% to emit a literal %. Note + that the optional arguments must presently be simple variables, + not expressions, and the format must be a simple string literal. %'',user_id; - Thus, the only thing PL/pgSQL currently does when it encounters - an abort during execution of a function or trigger - procedure is to write some additional NOTICE level log messages - telling in which function and where (line number and type of - statement) this happened. The error always stops execution of - the function. + Thus, the only thing PL/pgSQL + currently does when it encounters an abort during execution of a + function or trigger procedure is to write some additional + NOTICE level log messages telling in which + function and where (line number and type of statement) this + happened. The error always stops execution of the function. diff --git a/doc/src/sgml/release.sgml b/doc/src/sgml/release.sgml index 50df580404c..78606b68e94 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. --> @@ -921,7 +921,8 @@ env PGOPTIONS='-c geqo=off' psql built (see the configure option --enable-cassert). Note that DEBUG_ASSERTIONS defaults to on if - PostgreSQL has been built this way. + PostgreSQL has been built with + assertions enabled. diff --git a/doc/src/sgml/xfunc.sgml b/doc/src/sgml/xfunc.sgml index fad7ad888d8..9a7b79f0ddd 100644 --- a/doc/src/sgml/xfunc.sgml +++ b/doc/src/sgml/xfunc.sgml @@ -1,5 +1,5 @@ @@ -315,9 +315,7 @@ ERROR: function declared to return emp returns varchar instead of text at colum function, as described below. It can also be called in the context of an SQL expression, but only when you extract a single attribute out of the row or pass the entire row into - another function that accepts the same composite type. (Trying to - display the entire row value will yield - a meaningless number.) For example, + another function that accepts the same composite type. For example, SELECT (new_emp()).name; -- cgit v1.2.3