Обсуждение: functions returning sets

Поиск
Список
Период
Сортировка

functions returning sets

От
Alex Pilosov
Дата:
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. 




Re: functions returning sets

От
Tom Lane
Дата:
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


Re: functions returning sets

От
Jan Wieck
Дата:
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



Re: functions returning sets

От
Alex Pilosov
Дата:
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



Re: functions returning sets

От
Alex Pilosov
Дата:
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.




Re: functions returning sets

От
Alex Pilosov
Дата:
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



Re: functions returning sets

От
Tom Lane
Дата:
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