Re: first cut at PL/PgSQL table functions

Joe Conway wrote:
>
> Jan Wieck wrote:
> > A PL/pgSQL function might (in the future) want to return a refcursor on
> > one call, but use RETURN ... AND RESUME on another. So this has to be
> > done for every SET.
>
> How? They are going to be two different datatypes.

It might be that the function declaration once says that the function
returns a refcursor, another time that it returns SETOF something.
That's no good reason for me that a function declared SETOF something
cannot internally open a cursor and return that, as long as the cursors
result structure matches the return type. Also why shouldn't a function
returning a refcursor internally say RETURN ... AND RESUME?

It is an efficiency question when dealing with bigger result sets. A
function that returns a tuplestore has to materialize the entire result
set allways before it gets returned. A cursor as of today is an executor
on hold, that if it's a simple scan or join generates the requested
result tuples on the fly (it might materialize them as well because of a
final sort step).

> > My original idea was to make a tuplestore part of the cursor (Portal
> > structure). This way the tuplestore access would be hidden behind the
> > fetching and the caller doesn't have to care what the function really
> > returns. Also it'd avoid the memory context problem, because the
> > tuplestore would be part of the Portal memory context and go away when
> > the cursor is closed.
>
> I don't understand your concern. In the current implementation, table
> functions really have very little to do with portals. That was why
> pretty early on (7 May) Tom asked me to "s/portal/function/ throughout
> the patch".

And who cares about the current implementation? Joe, if "the current
implementation" would ever become a good point, I will consider leaving
the project because new features aren't welcome any more thereafter. It
is an excuse why things aren't implemented yet. But it is no good
argument to reject ideas.

>
> In any case, what Neil has proposed does hide the tuplestore behind the
> fetching. The user only declares the tuple return type like they would
> have to anyway. If you're referring to the functionmode being added to
> the grammar, I think Tom has talked us out of that already ;-)

Maybe Tom can talk some people into or out of something, but I allways
try to have my own opinion. That it is often similar to Tom's doesn't
surprise me, because he is a very gifted and talented engineer ;-)

I meant that the detail if one particular result set is a tuplestore,
one tuple at a time or a cursor should be expected to change from one
invocation (initial invocation, not repeated for fetching single tuples
for one result set) to another. If that should be allowed, and it should
be, then the type of result has to be part of the functions return.

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 #