Re: Struggling with set-returning functions, seeking advice

Jason Topaz <topaz@panix.com> writes:
> 1) Note that my first attempt (view "ranges_setview_broken", referring
>    to a pgplsql set function "make_rows") fails.  But when I make a
>    second function with identical signature, but in language 'sql'
>    (it's just a passthrough to my original pgplsql function), suddenly
>    the server error goes away.  This seems strange to me.

It's an implementation artifact: plpgsql uses a different implementation
method to return sets than sql does.  sql's method works both in SELECT
lists and in FROM, plpgsql's only works in FROM.

The trouble with a set function in FROM is that it can't take any
parameters that are extracted from other tables in the query.  There
has been some talk of fixing that by implementing SQL99's LATERAL()
syntax, but we haven't yet wrapped our heads around exactly what would
be involved there.  In the meantime, the way you're doing it is probably
as good as you're going to get.

            regards, tom lane