
Using a recent build (22.5) from CVS, if I create a set returning
function in SQL like this:

func_test=# CREATE TABLE foo (id INT, txt1 TEXT, txt2 TEXT);
CREATE TABLE
func_test=# INSERT INTO foo VALUES(1, 'Hello','World');
INSERT 24819 1
func_test=# 
func_test=# CREATE OR REPLACE FUNCTION bar(int)
func_test-# RETURNS SETOF foo
func_test-# AS 'SELECT * FROM foo WHERE id = $1'
func_test-# LANGUAGE 'sql';
CREATE FUNCTION

I can do this (expected result):

func_test=# SELECT txt1, txt2 FROM bar(1);
 txt1  | txt2  
-------+-------
 Hello | World
(1 row)

but also this:

func_test=# select bar(1);
    bar    
-----------
 139059784
(1 row)

What is this number? It often varies from query to query.
Possibly an error-in-disguise because of something to do
with the calling context?


Just curious ;-)


Ian Barwick


Sun, 26 May 2002 08:28:12 +0200
Ian Barwick <barwick@gmx.net>
Q: unexpected result from SRF in SQL
Ian Barwick wrote:
> but also this:
> 
> func_test=# select bar(1);
>     bar    
> -----------
>  139059784
> (1 row)
> 
> What is this number? It often varies from query to query.
> Possibly an error-in-disguise because of something to do
> with the calling context?

This is an illustration of why the expression SRF API isn't very useful 
for returning composite types ;)

The number is actually a pointer to the result row. There is no way 
under the expression API to get at the individual columns directly. If 
you're really curious, see contrib/dblink in 7.2.x for an example of a 
(ugly) workaround.

Joe



Sun, 26 May 2002 08:11:35 -0700
Joe Conway <mail@joeconway.com>
Re: Q: unexpected result from SRF in SQL
Joe Conway <mail@joeconway.com> writes:
> This is an illustration of why the expression SRF API isn't very useful 
> for returning composite types ;)
> The number is actually a pointer to the result row. There is no way 
> under the expression API to get at the individual columns directly.

You can get at one column --- as of 7.3 it is possible to do

	SELECT (bar(1)).field2;

(the parens are required to avoid syntax conflicts).  However SELECT is
not bright enough to do anything useful with a composite value directly.

Long ago (ie, in Postquel days) there seems to have been support for
breaking apart a composite result into multiple output columns.
(I *think* that was what the "fjoin" variant of targetlists was for.)
But it's been dead code for a long time --- probably Yu and Chen broke
it while converting the system to use SQL-spec syntax for SELECTs.

I am thinking that in 7.3 we might admit that that code's never gonna
get fixed, and alter SELECT so that a composite result appearing in a
SELECT targetlist draws an error.

If anyone does someday resurrect fjoin-like functionality, a reasonable
SQL-style syntax for invoking it would be

	SELECT (bar(1)).*;

which would still leave us wanting to raise an error if you just write
"SELECT bar(1)".

			regards, tom lane

Sun, 26 May 2002 11:58:51 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Q: unexpected result from SRF in SQL 
On Sunday 26 May 2002 17:58, Tom Lane wrote:
(...)
> If anyone does someday resurrect fjoin-like functionality, a reasonable
> SQL-style syntax for invoking it would be
>
> 	SELECT (bar(1)).*;
>
> which would still leave us wanting to raise an error if you just write
> "SELECT bar(1)".

The reason why I posted the question is that I had defined a function
that should have worked, but kept giving me back strange numbers,
so I spent a whole five minutes trying to debug the function before
I realised I was calling it in the wrong way (doh). An error here would
be a Good Idea, IMHO.

Ian Barwick



Sun, 26 May 2002 19:04:04 +0200
Ian Barwick <barwick@gmx.net>
Re: Q: unexpected result from SRF in SQL