functions returning sets

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 executor node 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

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

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