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

> 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

Andreas Zeugswetter <andreas.zeugswetter@telecom.at> writes:
> 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.

Yes, I have concluded the same (and partially fixed it, per my previous
message).

> Those that don't have a hobbie should return name|NULL|NULL. A hobbie
> that does'nt need equipment name|hobbie|NULL.

That's a good point.  Currently (both with and without my uncommitted
fix) you get *no* rows out from ExecTargetList if there are any Iters
that return empty result sets.  It might be more reasonable to treat an
empty result set as if it were NULL, which would give the behavior you
suggest.

This would be an easy change to my current patch, and I'm prepared to
make it before committing what I have, if people agree that that's a
more reasonable definition.  Comments?
        regards, tom lane