Re: functions returning records

Karel Zak wrote:
> On Tue, Jun 26, 2001 at 05:11:47PM -0400, Alex Pilosov wrote:
> > I started thinking about Tom's idea to implement functions as table
> > source.
> >
> > To me, it seems that a very few changes are necessary:
> > a) parser must be changed to allow functioncall to be a table_ref
> > (easy)
> >
> > b) when a Query node is generated out of such a call "select * from foo()"
> > it should be almost identical to one generated out of "select * from
> > (select * from foo)" with one distinction: list of query attributes should
> > be completed based on return type of foo().
>
> For the result from foo() you must somewhere define attributes (names).
> Where? In CREATE FUNCTION statement? Possible must be:
>
> select name1, name2 from foo() where name1 > 10;
>
> What returns foo()? ...the pointer to HeapTuple or something like this or
> pointer to some temp table?
>
> > c) executor should support execution of such Query node, properly
> > extracting things out of function's return value and placing them into
> > result attributes.
>
> d) changes in fmgr
>
> e) SPI support for table building/filling inside foo()
>
>
> IMHO very cool and nice feature, but not easy for imlementation.

Good questions - must be because I asked them myself before.
:-)

My idea on that is as follows:

1. Adding a new relkind that means 'record'. So we use
pg_class, pg_attribute and pg_type as we do for tables
and views to describe a structure.

2. A function that RETURNS SETOF record/table/view is
expected to return a refcursor (which is basically a
portal name - SPI support already in 7.2), who's tupdesc
matches the structure.

3. The Func node for such a function invocation will call
the function with the appropriate arguments to get the
portal, receive the tuples with an internal fetch method
one per invocation (I think another destination is
basically enough) and close the portal at the end.

4. Enhancement of the portal capabilities. A new function
with a tuple descriptor as argument creates a special
portal that simply opens a tuple sink. Another function
stores a tuple there and a third one rewinds the sink and
switches the portal into read mode, so that fetches will
return the tuples again. One format of the tuple sink is
capable of backward moves too, so it'll be totally
transparent.

5. Enhancement of procedural languages that aren't
implemented as state machines (currently all of them) to
use the tuple-sink-portals and implement RETURN AND
RESUME.

This plan reuses alot of existing code and gains IMHO the
most functionality. All portals are implicitly closed at the
end of a transaction. This form of internal portal usage
doesn't require explicit transaction blocks (as of current
7.2 tree). All the neat buffering, segmenting of the tuple
sink code for materializing the result set comes into play.
From the executors POV there is no difference between a
function returning a portal that's a real SELECT, collecting
the data on the fly, or a function materializing the result
set first with RETURN AND RESUME. The tuple structure
returned by a function is not only known at parsetime, but
can be used in other places like for %ROWTYPE in PL/pgSQL.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com