Cc: pgsql-docs@postgresql.org
Message-Id: <B292CCAB-124A-475D-8075-D7511395E465@numericable.fr>
From: John Gage <jsmgage@numericable.fr>
To: Peter Eisentraut <peter_e@gmx.net>
In-Reply-To: <1280046663.28006.0.camel@vanquo.pezone.net>
Content-Type: text/plain; charset=US-ASCII; format=flowed; delsp=yes
Content-Transfer-Encoding: 7bit
Mime-Version: 1.0 (Apple Message framework v936)
Subject: Re: Another change to the same section once again to facilitate
	copying and pasting
Date: Sun, 25 Jul 2010 11:34:07 +0200
References: <9A88E54D-2CF7-403F-A0D8-35B9ECDC7103@numericable.fr>
	<1279974089.22066.3.camel@vanquo.pezone.net>
	<DE129122-F02D-4985-A32A-A18DE7A81F09@numericable.fr>
	<1280046663.28006.0.camel@vanquo.pezone.net>

In   34.4.3. SQL Functions with Output Parameters   we have:

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)

Then in     34.4.7. SQL Functions Returning Sets     we have (without  
an example SELECT statement):

CREATE FUNCTION sum_n_product_with_tab (x int, OUT sum int, OUT  
product int) RETURNS SETOF record AS $$
SELECT $1 + tab.y, $1 * tab.y FROM tab;
$$ LANGUAGE SQL;

The problem is that if the reader copies and pastes the last function  
definition and then attempts to run it, quite obviously he gets an  
error because there is no table   "tab".
I would add a table definition and fill it with dummy data and then  
give two example SELECT statements after the function definition:

CREATE TABLE tab ( y integer, z integer );
INSERT INTO tab VALUES (1, 2), (3,4), (5,6), (7,8);

CREATE FUNCTION sum_n_product_with_tab (x int, OUT sum int, OUT  
product int) RETURNS SETOF record AS $$
SELECT $1 + tab.y, $1 * tab.y FROM tab;
$$ LANGUAGE SQL;

SELECT sum_n_product_with_tab(10);
SELECT * from sum_n_product_with_tab(10);

I think one of the things this presentation accomplishes is to  
emphasize that a table definition is really not much more than a type  
definition or object definition or what you will.  That is not said  
derogatorily at all.  Rather, I believe it emphasizes the  
extraordinary power of Postgresql.

John