- publishing free software manuals
The PostgreSQL 9.0 Reference Manual - Volume 2 - Programming Guide
by The PostgreSQL Global Development Group
Paperback (6"x9"), 478 pages
ISBN 9781906966065
RRP £14.95 ($19.95)

Sales of this book support the PostgreSQL project! Get a printed copy>>>

5.4.4 SQL Functions with Output Parameters

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'

SELECT add_em(3,7);
(1 row)

This is not essentially different from the version of add_em shown in section 5.4.1 SQL Functions on Base Types. 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'

 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'

but not having to bother with the separate composite type definition is often handy. Notice that the names attached to the output parameters are not just decoration, but determine the column names of the anonymous composite type. (If you omit a name for an output parameter, the system will choose a name on its own.)

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, INOUT, or VARIADIC. 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). VARIADIC parameters are input parameters, but are treated specially as described next.

ISBN 9781906966065The PostgreSQL 9.0 Reference Manual - Volume 2 - Programming GuideSee the print edition