Thread: Q: unexpected result from SRF in SQL

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

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

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

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