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 beSELECT (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