Re: functions returning records

Alex Pilosov wrote:
> On Wed, 27 Jun 2001, Jan Wieck wrote:
>
> > 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.
> Okay
>
> > 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.
> Okay, but that will break whatever currently written functions which
> return setof. Although it could be considered a good thing, as its too
> ugly now :)

Not necessarily. We could as well (as Tom mentioned already)
add another portal enhancement, so that the current "SETOF
tuple" function behaviour is wrapped by a portal. So if you
call a "SETOF tuple" function, the function pointer get's
stored in the portal and the function called on FETCH (or the
internal fetch methods). The distinction on the SQL level
could be done as "RETURNS CURSOR OF ...", don't know how to
layer that into pg_proc yet, but would make it even clearer.

> I'm not sure I understand this one correctly. Could you explain what
> you mean here by 'use'?
>
> What is "RETURN AND RESUME"? Do you mean a function that precomputes
> entire result set before stuffing it into portal?

On the PL/pgSQL level such a function could look like

...
FOR row IN SELECT * FROM mytab LOOP
RETURN (row.a, row.b + row.c) AND RESUME;
END LOOP;
RETURN;

Poor example and could be done better, but you get the idea.
The language handler opens a tuple sink portal for it. On
every loop invocation, one tuple is stuffed into it and on
the final return, the tuple sink is rewound and prepared to
return the tuples. The portal around it controls when to get
rid of the sink, wherever it resides.

These sinks are the place where the sorter for example piles
it's tuples. For small numbers of tuples, they are just held
in main memory. Bigger collections get stuffed into a
tempfile and huge ones even in segmented tempfiles. What's
considered "small" is controlled by the -S option (sort
buffer size). So it's already a runtime option.

> I think I once again got myself in over my head :) But I'm going to try to
> code this thing anyway, with great suggestions from Karel and you....

Hard training causes sore muscles, unfortunately it's the
only way to gain muscle power - but take a break before you
have a cramp :-)

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