Thread: functions returning sets
Well, I'm on my way to implement what was discussed on list before. I am doing it the way Karel and Jan suggested: creating a pg_class/pg_attribute tuple[s] for a function that returns a setof. I have a special RELKIND_FUNC for parser, and it seems to go through fine, and the final query plan has 'Seq Scan on rel####', which I think is a good sign, as the the function should pretend to be a relation. Now, more interesting question, what's the best way to interface ExecScan to function-executing machinery: Options are: 1) Create a special scan node type, T_FuncSeqScan and deal with it there. 2) Keep the T_SeqScan, explain to nodeSeqScan special logic when dealing with RELKIND_FUNC relations. (I prefer this one, but I would like a validation of it) 3) explain to heap_getnext special logic.
Alex Pilosov <alex@pilosoft.com> writes:
> Well, I'm on my way to implement what was discussed on list before.
> I am doing it the way Karel and Jan suggested: creating a
> pg_class/pg_attribute tuple[s] for a function that returns a setof.
What? You shouldn't need pg_class entries for functions unless they
return *tuples*. setof has nothing to do with that. Moreover, the
pg_class entry should be thought of as a record type independent of
the existence of any particular function returning it.
> I have a special RELKIND_FUNC for parser,
This seems totally wrong.
> Options are:
> 1) Create a special scan node type, T_FuncSeqScan and deal with it there.
> 2) Keep the T_SeqScan, explain to nodeSeqScan special logic when dealing
> with RELKIND_FUNC relations.
> (I prefer this one, but I would like a validation of it)
> 3) explain to heap_getnext special logic.
I prefer #1. #2 or #3 will imply slowing down normal execution paths
with extra clutter to deal with functions.
BTW, based on Jan's sketch, I'd say it should be more like
T_CursorSeqScan where the object being scanned is a cursor/portal.
regards, tom lane
Alex Pilosov wrote:
> Well, I'm on my way to implement what was discussed on list before.
>
> I am doing it the way Karel and Jan suggested: creating a
> pg_class/pg_attribute tuple[s] for a function that returns a setof.
That's not exactly what I suggested. I meant having a separate
CREATE TYPE <typname> IS RECORD OF (<atttyplist>);
and then
CREATE FUNCTION ... RETURNS SETOF <typname>|<tablename>|<viewname> ...
Note that we need a pg_type entry too as we currently do for tables and views. The only thing missing is a file
underneath and of course, the ability to use it directly for INSERT, UP... operations.
This way, you have the functions returned tuple structure available elsewhere too, like in PL/pgSQL for
%ROWTYPE, because it's a named type declaration.
> Now, more interesting question, what's the best way to interface ExecScan
> to function-executing machinery:
>
> Options are:
>
> 1) Create a special scan node type, T_FuncSeqScan and deal with it there.
>
> 2) Keep the T_SeqScan, explain to nodeSeqScan special logic when dealing
> with RELKIND_FUNC relations.
> (I prefer this one, but I would like a validation of it)
>
> 3) explain to heap_getnext special logic.
My idea was to change the expected return Datum of a function returning SETOF <rowtype> beeing a refcursor
or portal directly. Portals are an abstraction of a resultset and used in Postgres to implement cursors. So the
executornode would be T_PortalScan. Whatever a function needs (callback per tuple, tuple sink to stuff, an
executor like now) will be hidden in the portal.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
On Fri, 29 Jun 2001, Tom Lane wrote: > Alex Pilosov <alex@pilosoft.com> writes: > > Well, I'm on my way to implement what was discussed on list before. > > I am doing it the way Karel and Jan suggested: creating a > > pg_class/pg_attribute tuple[s] for a function that returns a setof. > > What? You shouldn't need pg_class entries for functions unless they > return *tuples*. setof has nothing to do with that. Moreover, the > pg_class entry should be thought of as a record type independent of > the existence of any particular function returning it. Well, a lot of things (planner for ex) need to know relid of the relation being returned. If a function returns setof int4, for example, what relid should be filled in? Variables (for example) have to be bound to relid and attno. If a function returns setof int4, what should be variables' varno be? Assigning 'fake' relids valid for length of query (from a low range) may be a solution if you agree? > > I have a special RELKIND_FUNC for parser, > > This seems totally wrong. Probably :) > > Options are: > > 1) Create a special scan node type, T_FuncSeqScan and deal with it there. > > 2) Keep the T_SeqScan, explain to nodeSeqScan special logic when dealing > > with RELKIND_FUNC relations. > > (I prefer this one, but I would like a validation of it) > > 3) explain to heap_getnext special logic. > > I prefer #1. #2 or #3 will imply slowing down normal execution paths > with extra clutter to deal with functions. > > BTW, based on Jan's sketch, I'd say it should be more like > T_CursorSeqScan where the object being scanned is a cursor/portal. Okay. So the logic should support 'select * from foo' where foo is portal, right? Then I _do_ have to deal with a problem of unknown relid to bind variables to... -alex
On Fri, 29 Jun 2001, Tom Lane wrote: > Alex Pilosov <alex@pilosoft.com> writes: > > Well, a lot of things (planner for ex) need to know relid of the relation > > being returned. > > Only if there *is* a relid. Check out the handling of > sub-SELECT-in-FROM for a more reasonable model. Thank you! > > It's quite likely that you'll need another variant of RangeTblEntry to > represent a function call. I've been thinking that RangeTblEntry should > have an explicit type code (plain rel, subselect, inheritance tree top, > and join were the variants I was thinking about at the time; add > "function returning tupleset" to that) and then there could be a union > for the fields that apply to only some of the variants. I don't think I've got the balls to do this one, cuz it'd need to be modified in many places. I'll just add another field there for my use and let someone clean it up later. :) > > Variables (for example) have to be bound to relid and attno. If a function > > returns setof int4, what should be variables' varno be? > > I'd say that such a function's output will probably be implicitly > converted to single-column tuples in order to store it in the portal > mechanism. So the varno is 1. Even if the execution-time mechanism > doesn't need to do that, the parser has to consider it that way to allow > a column name to be assigned to the result. Example: > > select x+1 from funcreturningsetofint4(); > > What can I write for "x" to make this work? There isn't anything. > I have to assign a column alias to make it legal: > > select x+1 from funcreturningsetofint4() as f(x); > > Here, x must clearly be regarded as the first (and only) column of the > rangetable entry for "f". more fun for grammar, but I'll try. > > Okay. So the logic should support 'select * from foo' where foo is portal, > > right? > > Yeah, that was what I had up my sleeve ... then > > select * from mycursor limit 1; > > would be more or less equivalent to > > fetch 1 from mycursor; Neat possibilities.
On Fri, 29 Jun 2001, Alex Pilosov wrote: > > > > Yeah, that was what I had up my sleeve ... then > > > > select * from mycursor limit 1; > > > > would be more or less equivalent to > > > > fetch 1 from mycursor; Hmm, how would this be resolved if there's a (for example) table foo and a cursor named foo? Warning? Error? Maybe syntax like 'select * from cursor foo' should be required syntax? -alex
Alex Pilosov <alex@pilosoft.com> writes:
> Well, a lot of things (planner for ex) need to know relid of the relation
> being returned.
Only if there *is* a relid. Check out the handling of
sub-SELECT-in-FROM for a more reasonable model.
It's quite likely that you'll need another variant of RangeTblEntry to
represent a function call. I've been thinking that RangeTblEntry should
have an explicit type code (plain rel, subselect, inheritance tree top,
and join were the variants I was thinking about at the time; add
"function returning tupleset" to that) and then there could be a union
for the fields that apply to only some of the variants.
> Variables (for example) have to be bound to relid and attno. If a function
> returns setof int4, what should be variables' varno be?
I'd say that such a function's output will probably be implicitly
converted to single-column tuples in order to store it in the portal
mechanism. So the varno is 1. Even if the execution-time mechanism
doesn't need to do that, the parser has to consider it that way to allow
a column name to be assigned to the result. Example:
select x+1 from funcreturningsetofint4();
What can I write for "x" to make this work? There isn't anything.
I have to assign a column alias to make it legal:
select x+1 from funcreturningsetofint4() as f(x);
Here, x must clearly be regarded as the first (and only) column of the
rangetable entry for "f".
> Okay. So the logic should support 'select * from foo' where foo is portal,
> right?
Yeah, that was what I had up my sleeve ... then
select * from mycursor limit 1;
would be more or less equivalent to
fetch 1 from mycursor;
regards, tom lane