Re: first cut at PL/PgSQL table functions

<Whew -- bad day to be away from my email for several hours!>

Jan Wieck wrote:
> 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?

Hmmm. Are you proposing that a plpgsql function which returns SETOF
might return a cursor *or* a tuplestore, or a cursor *in place of* a
tuplestore? I agree that in the long term (i.e. after 7.3 is released)
we should support passing cursors, but I don't see it getting done in
time to support a 7.3 release. But we ought not preclude passing cursors
in the future.

> 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).

Makes sense. We've discussed before three types of modes that we should
support:

1. materialized - that's the tuplestore method in place now.
2. stream - send everything straight on through (the big O calls this
PIPE I believe). I previously proposed targeting this for post 7.3.
3. query - this one I'd forgotten about, but the idea was to use
cursor support to allow start/stop/fetch etc, as you have described.
I think this should also be post 7.3

> 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.

I didn't mean it as an argument that your ideas should be rejected (I
don't consider myself qualified to reject your ideas, just qualified
enough to comment on them ;-)). It was just an appeal to keep the scope
down to something we can get out in 7.3

> 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 ;-)

And we count on everyone to have their own opinions, otherwise this list
would be much more boring (and less productive) than it usually is! The
diversity of ideas here is one of the strengths of the project.

> 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.
>

Sure. I can see that point now. We just need to figure out the best way
to pass that info from the function back to FunctionNext.

Joe