Thread

Re: [HACKERS] Progress report: buffer refcount bugs and SQL functions

Zeugswetter Andreas <andreas.zeugswetter@telecom.at> — 1999-09-23T08:07:24Z

> Is the regression test's expected output wrong, or am I 
> misunderstanding
> what this query is supposed to do?  Is there any 
> documentation anywhere
> about how SQL functions returning multiple tuples are supposed to
> behave?

They are supposed to behave somewhat like a view.
Not all rows are necessarily fetched.
If used in a context that needs a single row answer,
and the answer has multiple rows it is supposed to 
runtime elog. Like in:

select * from tbl where col=funcreturningmultipleresults();
-- this must elog

while this is ok:
select * from tbl where col in (select funcreturningmultipleresults());

But the caller could only fetch the first row if he wanted.

The nested notation is supposed to call the function passing it the tuple
as the first argument. This is what can be used to "fake" a column
onto a table (computed column). 
That is what I use it for. I have never used it with a 
returns setof function, but reading the comments in the regression test,
-- mike needs advil and peet's coffee,
-- joe and sally need hightops, and
-- everyone else is fine.
it looks like the results you expected are correct, and currently the 
wrong result is given.

But I think this query could also elog whithout removing substantial
functionality. 

SELECT p.name, p.hobbies.name, p.hobbies.equipment.name FROM person p;

Actually for me it would be intuitive, that this query return one row per 
person, but elog on those that have more than one hobbie or a hobbie that 
needs more than one equipment. Those that don't have a hobbie should 
return name|NULL|NULL. A hobbie that does'nt need equipment name|hobbie|NULL.

Andreas