I started thinking about Tom's idea to implement functions as table
source.

To me, it seems that a very few changes are necessary:
a) parser must be changed to allow functioncall to be a table_ref
(easy)

b) when a Query node is generated out of such a call "select * from foo()"
it should be almost identical to one generated out of "select * from
(select * from foo)" with one distinction: list of query attributes should
be completed based on return type of foo().

c) executor should support execution of such Query node, properly
extracting things out of function's return value and placing them into
result attributes.


If I'm wrong, please correct me.

-alex


Tue, 26 Jun 2001 17:11:47 -0400 (EDT)
Alex Pilosov <alex@pilosoft.com>
functions returning records
On Tue, 26 Jun 2001 17:11:47 -0400 (EDT), you wrote:

>I started thinking about Tom's idea to implement functions as table
>source.
>
>To me, it seems that a very few changes are necessary:
>a) parser must be changed to allow functioncall to be a table_ref
>(easy)
>
>b) when a Query node is generated out of such a call "select * from foo()"
>it should be almost identical to one generated out of "select * from
>(select * from foo)" with one distinction: list of query attributes should
>be completed based on return type of foo().
>
>c) executor should support execution of such Query node, properly
>extracting things out of function's return value and placing them into
>result attributes.

Coming from a Sybase environment I would love to have functions return
a result set. A few things to think of:
1: will it be possible to return multiple result sets? (in Sybase any
select statement that is not redirected to variables or a table goes
to the client, so it is quite common to do multiple selects). Does the
postgresql client library support this?

2: will it be possible to put a single result set in a table.
Something like "resultfunction (argument) INTO TABLENAME" or "INSERT
INTO TABLENAME resultfunction(argument)

-- 
__________________________________________________
"Nothing is as subjective as reality"
Reinoud van Leeuwen       reinoud@xs4all.nl
http://www.xs4all.nl/~reinoud
__________________________________________________

Tue, 26 Jun 2001 23:25:23 GMT
reinoud@xs4all.nl (Reinoud van Leeuwen)
Re: functions returning records
On Tue, 26 Jun 2001, Reinoud van Leeuwen wrote:

> Coming from a Sybase environment I would love to have functions return
> a result set. A few things to think of:
> 1: will it be possible to return multiple result sets? (in Sybase any
> select statement that is not redirected to variables or a table goes
> to the client, so it is quite common to do multiple selects). Does the
> postgresql client library support this?
No, libpq protocol cannot support that. This is really a sybasism, as good
as it is, no other database supports anything like that.

> 2: will it be possible to put a single result set in a table.
> Something like "resultfunction (argument) INTO TABLENAME" or "INSERT
> INTO TABLENAME resultfunction(argument)

It will be, but syntax will be:
select * into tablename from resultfunction(arg)
insert into tablename select * from resultfunction(arg)

(I.E. resultfunction must be in the 'from' clause)

-alex


Tue, 26 Jun 2001 22:09:39 -0400 (EDT)
Alex Pilosov <alex@pilosoft.com>
Re: functions returning records
On Tue, Jun 26, 2001 at 05:11:47PM -0400, Alex Pilosov wrote:
> I started thinking about Tom's idea to implement functions as table
> source.
> 
> To me, it seems that a very few changes are necessary:
> a) parser must be changed to allow functioncall to be a table_ref
> (easy)
> 
> b) when a Query node is generated out of such a call "select * from foo()"
> it should be almost identical to one generated out of "select * from
> (select * from foo)" with one distinction: list of query attributes should
> be completed based on return type of foo().

 For the result from foo() you must somewhere define attributes (names). 
Where? In CREATE FUNCTION statement? Possible must be:

 select name1, name2 from foo() where name1 > 10;

 What returns foo()? ...the pointer to HeapTuple or something like this or
pointer to some temp table?

> c) executor should support execution of such Query node, properly
> extracting things out of function's return value and placing them into
> result attributes.

 d) changes in fmgr

 e) SPI support for table building/filling inside foo()


 IMHO very cool and nice feature, but not easy for imlementation.

			Karel 

-- 
 Karel Zak  <zakkr@zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/
 
 C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz

Wed, 27 Jun 2001 09:10:52 +0200
Karel Zak <zakkr@zf.jcu.cz>
Re: functions returning records
On Wed, 27 Jun 2001, Karel Zak wrote:

> On Tue, Jun 26, 2001 at 05:11:47PM -0400, Alex Pilosov wrote:
> > I started thinking about Tom's idea to implement functions as table
> > source.
> > 
> > To me, it seems that a very few changes are necessary:
> > a) parser must be changed to allow functioncall to be a table_ref
> > (easy)
> > 
> > b) when a Query node is generated out of such a call "select * from foo()"
> > it should be almost identical to one generated out of "select * from
> > (select * from foo)" with one distinction: list of query attributes should
> > be completed based on return type of foo().
> 
>  For the result from foo() you must somewhere define attributes (names). 
> Where? In CREATE FUNCTION statement? Possible must be:
Function must be returning an existing reltype. I understand its a major
restriction, but I can't think of a better way. 

>  select name1, name2 from foo() where name1 > 10;
> 
>  What returns foo()? ...the pointer to HeapTuple or something like this or
> pointer to some temp table?
Pointer to heaptuple. We can get to tupdesc for that tuple by looking up
its prorettype.

> > c) executor should support execution of such Query node, properly
> > extracting things out of function's return value and placing them into
> > result attributes.
> 
>  d) changes in fmgr
Don't think that's necessary, but I guess I'll find out when I try it :)

>  e) SPI support for table building/filling inside foo()

As far as SPI is concerned, its the same as current: function returning
records must return pointer to HeapTuple containing the record.


Wed, 27 Jun 2001 06:29:32 -0400 (EDT)
Alex Pilosov <alex@pilosoft.com>
Re: functions returning records
Alex Pilosov wrote:
> 
> On Tue, 26 Jun 2001, Reinoud van Leeuwen wrote:
> 
> > Coming from a Sybase environment I would love to have functions return
> > a result set. A few things to think of:
> > 1: will it be possible to return multiple result sets? (in Sybase any
> > select statement that is not redirected to variables or a table goes
> > to the client, so it is quite common to do multiple selects). Does the
> > postgresql client library support this?
> No, libpq protocol cannot support that. This is really a sybasism, as good
> as it is, no other database supports anything like that.

IIRC the _protocol_ should support it all right, but the current libpq 
implementation does not (and the sql queries in functions are not sent
to 
client either)

---------------
Hannu

Wed, 27 Jun 2001 15:24:33 +0200
Hannu Krosing <hannu@tm.ee>
Re: functions returning records
Hannu Krosing <hannu@tm.ee> writes:
> Alex Pilosov wrote:
>> On Tue, 26 Jun 2001, Reinoud van Leeuwen wrote:
> 1: will it be possible to return multiple result sets? (in Sybase any
> select statement that is not redirected to variables or a table goes
> to the client, so it is quite common to do multiple selects). Does the
> postgresql client library support this?

>> No, libpq protocol cannot support that. This is really a sybasism, as good
>> as it is, no other database supports anything like that.

> IIRC the _protocol_ should support it all right, but the current libpq 
> implementation does not (and the sql queries in functions are not sent
> to  client either)

Actually, libpq supports it just fine too, but most clients don't.
You have to use PQsendQuery() and a PQgetResult() loop to deal with
multiple resultsets out of one query.  It is possible to see this
happening even today:

	PQsendQuery(conn, "SELECT * FROM foo ; SELECT * FROM bar");

	while ((res = PQgetResult(conn)))
	{
		...

Whether it would be a *good idea* to allow standalone SELECTs in
functions to be handled that way is another question.  I've got strong
doubts about it.  The main problem is that the function call would be
nested inside another SELECT, which means you'd have the problem of
suspending a resultset transmission already in progress.  That's *not*
in the protocol, much less libpq, and you wouldn't really want clients
forced to buffer incomplete resultsets anyway.  But it could be
supported in procedures (not functions) that are called by some kind of
PERFORM statement, so that there's not a SELECT already in progress when
they are invoked.

			regards, tom lane

Wed, 27 Jun 2001 10:35:56 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: functions returning records 
Karel Zak wrote:
> On Tue, Jun 26, 2001 at 05:11:47PM -0400, Alex Pilosov wrote:
> > I started thinking about Tom's idea to implement functions as table
> > source.
> >
> > To me, it seems that a very few changes are necessary:
> > a) parser must be changed to allow functioncall to be a table_ref
> > (easy)
> >
> > b) when a Query node is generated out of such a call "select * from foo()"
> > it should be almost identical to one generated out of "select * from
> > (select * from foo)" with one distinction: list of query attributes should
> > be completed based on return type of foo().
>
>  For the result from foo() you must somewhere define attributes (names).
> Where? In CREATE FUNCTION statement? Possible must be:
>
>  select name1, name2 from foo() where name1 > 10;
>
>  What returns foo()? ...the pointer to HeapTuple or something like this or
> pointer to some temp table?
>
> > c) executor should support execution of such Query node, properly
> > extracting things out of function's return value and placing them into
> > result attributes.
>
>  d) changes in fmgr
>
>  e) SPI support for table building/filling inside foo()
>
>
>  IMHO very cool and nice feature, but not easy for imlementation.

    Good  questions - must be because I asked them myself before.
    :-)

    My idea on that is as follows:

    1.  Adding a new relkind  that  means  'record'.  So  we  use
        pg_class,  pg_attribute  and  pg_type as we do for tables
        and views to describe a structure.

    2.  A  function  that  RETURNS  SETOF  record/table/view   is
        expected  to  return  a  refcursor  (which is basically a
        portal name - SPI support already in 7.2), who's  tupdesc
        matches the structure.

    3.  The  Func  node  for such a function invocation will call
        the function with the appropriate arguments  to  get  the
        portal,  receive the tuples with an internal fetch method
        one  per  invocation  (I  think  another  destination  is
        basically enough) and close the portal at the end.

    4.  Enhancement  of  the  portal capabilities. A new function
        with a tuple descriptor as  argument  creates  a  special
        portal  that  simply opens a tuple sink. Another function
        stores a tuple there and a third one rewinds the sink and
        switches  the portal into read mode, so that fetches will
        return the tuples again. One format of the tuple sink  is
        capable  of  backward  moves  too,  so  it'll  be totally
        transparent.

    5.  Enhancement   of   procedural   languages   that   aren't
        implemented  as state machines (currently all of them) to
        use  the  tuple-sink-portals  and  implement  RETURN  AND
        RESUME.

    This  plan  reuses  alot  of existing code and gains IMHO the
    most functionality.  All portals are implicitly closed at the
    end  of  a  transaction.  This  form of internal portal usage
    doesn't require explicit transaction blocks  (as  of  current
    7.2  tree).  All  the neat buffering, segmenting of the tuple
    sink code for materializing the result set comes  into  play.
    From  the  executors  POV  there  is  no difference between a
    function returning a portal that's a real SELECT,  collecting
    the  data  on the fly, or a function materializing the result
    set  first  with  RETURN  AND  RESUME.  The  tuple  structure
    returned  by  a  function is not only known at parsetime, but
    can be used in other places like for %ROWTYPE in PL/pgSQL.


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


Wed, 27 Jun 2001 11:05:58 -0400 (EDT)
Jan Wieck <JanWieck@Yahoo.com>
Re: functions returning records
Jan Wieck <JanWieck@Yahoo.com> writes:
>     1.  Adding a new relkind  that  means  'record'.  So  we  use
>         pg_class,  pg_attribute  and  pg_type as we do for tables
>         and views to describe a structure.

It seems fairly ugly to have a pg_class entry for something that
isn't a table or even a table-like entity.  It would be nice if
we could describe a record type with only pg_type and pg_attribute
entries.  I haven't thought about it in detail, but seems like it
could be done if pg_attribute entries are changed to reference
pg_type, not pg_class, rows as their parent.  However, this would
break so many existing queries in psql and other clients that it'd
probably be unacceptable :-(

>     2.  A  function  that  RETURNS  SETOF  record/table/view   is
>         expected  to  return  a  refcursor  (which is basically a
>         portal name - SPI support already in 7.2), who's  tupdesc
>         matches the structure.

Otherwise this proposal sounds good.  Jan and I talked about it earlier;
one point I recall is that the portal/cursor based approach can
internally support the existing multiple-call implementation of
functions returning sets.  That is, when you call the portal to get the
next tuple, it might hand you back a tuple saved from a previous
function call, or it might turn around and call the function again to
get the next tuple.

BTW, once we've had this for a release or two, I'd like to rip out the
existing support for calling functions-returning-sets during SELECT list
evaluation, so that expression evaluation could be simplified and sped
up.  But we can wait for people to change over their existing uses
before we do that.

			regards, tom lane

Wed, 27 Jun 2001 11:26:40 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: functions returning records 
On Wed, 27 Jun 2001, Jan Wieck wrote:

>     My idea on that is as follows:
> 
>     1.  Adding a new relkind  that  means  'record'.  So  we  use
>         pg_class,  pg_attribute  and  pg_type as we do for tables
>         and views to describe a structure.
Okay

>     2.  A  function  that  RETURNS  SETOF  record/table/view   is
>         expected  to  return  a  refcursor  (which is basically a
>         portal name - SPI support already in 7.2), who's  tupdesc
>         matches the structure.
Okay, but that will break whatever currently written functions which
return setof. Although it could be considered a good thing, as its too
ugly now :)

>     3.  The  Func  node  for such a function invocation will call
>         the function with the appropriate arguments  to  get  the
>         portal,  receive the tuples with an internal fetch method
>         one  per  invocation  (I  think  another  destination  is
>         basically enough) and close the portal at the end.
OK

>     4.  Enhancement  of  the  portal capabilities. A new function
>         with a tuple descriptor as  argument  creates  a  special
>         portal  that  simply opens a tuple sink. Another function
>         stores a tuple there and a third one rewinds the sink and
>         switches  the portal into read mode, so that fetches will
>         return the tuples again. One format of the tuple sink  is
>         capable  of  backward  moves  too,  so  it'll  be totally
>         transparent.
OK

>     5.  Enhancement   of   procedural   languages   that   aren't
>         implemented  as state machines (currently all of them) to
>         use  the  tuple-sink-portals  and  implement  RETURN  AND
>         RESUME.
I'm not sure I understand this one correctly. Could you explain what 
you mean here by 'use'?

What is "RETURN AND RESUME"? Do you mean a function that precomputes
entire result set before stuffing it into portal?

>     This  plan  reuses  alot  of existing code and gains IMHO the
>     most functionality.  All portals are implicitly closed at the
>     end  of  a  transaction.  This  form of internal portal usage
>     doesn't require explicit transaction blocks  (as  of  current
>     7.2  tree).  All  the neat buffering, segmenting of the tuple
>     sink code for materializing the result set comes  into  play.
>     From  the  executors  POV  there  is  no difference between a
>     function returning a portal that's a real SELECT,  collecting
>     the  data  on the fly, or a function materializing the result
>     set  first  with  RETURN  AND  RESUME.  The  tuple  structure
>     returned  by  a  function is not only known at parsetime, but
>     can be used in other places like for %ROWTYPE in PL/pgSQL.

I think I once again got myself in over my head :) But I'm going to try to
code this thing anyway, with great suggestions from Karel and you....

-alex


Wed, 27 Jun 2001 11:31:09 -0400 (EDT)
Alex Pilosov <alex@pilosoft.com>
Re: functions returning records
Tom Lane wrote:
> Jan Wieck <JanWieck@Yahoo.com> writes:
> >     1.  Adding a new relkind  that  means  'record'.  So  we  use
> >         pg_class,  pg_attribute  and  pg_type as we do for tables
> >         and views to describe a structure.
>
> It seems fairly ugly to have a pg_class entry for something that
> isn't a table or even a table-like entity.  It would be nice if
> we could describe a record type with only pg_type and pg_attribute
> entries.  I haven't thought about it in detail, but seems like it
> could be done if pg_attribute entries are changed to reference
> pg_type, not pg_class, rows as their parent.  However, this would
> break so many existing queries in psql and other clients that it'd
> probably be unacceptable :-(

    It's  not  THAT  ugly  for  me,  and the fact that it's named
    "pg_class" instead of "pg_relation" makes some sense  all  of
    the sudden.


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


Wed, 27 Jun 2001 12:14:54 -0400 (EDT)
Jan Wieck <JanWieck@Yahoo.com>
Re: functions returning records
Alex Pilosov wrote:
> On Wed, 27 Jun 2001, Jan Wieck wrote:
>
> >     My idea on that is as follows:
> >
> >     1.  Adding a new relkind  that  means  'record'.  So  we  use
> >         pg_class,  pg_attribute  and  pg_type as we do for tables
> >         and views to describe a structure.
> Okay
>
> >     2.  A  function  that  RETURNS  SETOF  record/table/view   is
> >         expected  to  return  a  refcursor  (which is basically a
> >         portal name - SPI support already in 7.2), who's  tupdesc
> >         matches the structure.
> Okay, but that will break whatever currently written functions which
> return setof. Although it could be considered a good thing, as its too
> ugly now :)

    Not  necessarily. We could as well (as Tom mentioned already)
    add another portal enhancement, so that  the  current  "SETOF
    tuple"  function  behaviour is wrapped by a portal. So if you
    call a "SETOF tuple" function,  the  function  pointer  get's
    stored in the portal and the function called on FETCH (or the
    internal fetch methods). The distinction  on  the  SQL  level
    could  be  done as "RETURNS CURSOR OF ...", don't know how to
    layer that into pg_proc yet, but would make it even  clearer.

> I'm not sure I understand this one correctly. Could you explain what
> you mean here by 'use'?
>
> What is "RETURN AND RESUME"? Do you mean a function that precomputes
> entire result set before stuffing it into portal?

    On the PL/pgSQL level such a function could look like

         ...
        FOR row IN SELECT * FROM mytab LOOP
            RETURN (row.a, row.b + row.c) AND RESUME;
        END LOOP;
        RETURN;

    Poor  example and could be done better, but you get the idea.
    The language handler opens a tuple sink  portal  for  it.  On
    every  loop  invocation,  one tuple is stuffed into it and on
    the final return, the tuple sink is rewound and  prepared  to
    return  the tuples. The portal around it controls when to get
    rid of the sink, wherever it resides.

    These sinks are the place where the sorter for example  piles
    it's tuples.  For small numbers of tuples, they are just held
    in  main  memory.  Bigger  collections  get  stuffed  into  a
    tempfile  and  huge  ones even in segmented tempfiles. What's
    considered "small" is  controlled  by  the  -S  option  (sort
    buffer size). So it's already a runtime option.

> I think I once again got myself in over my head :) But I'm going to try to
> code this thing anyway, with great suggestions from Karel and you....

    Hard  training  causes  sore  muscles, unfortunately it's the
    only way to gain muscle power - but take a break  before  you
    have a cramp :-)


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


Wed, 27 Jun 2001 12:40:46 -0400 (EDT)
Jan Wieck <JanWieck@Yahoo.com>
Re: functions returning records
Tom Lane wrote:
> 
> Jan Wieck <JanWieck@Yahoo.com> writes:
> >     1.  Adding a new relkind  that  means  'record'.  So  we  use
> >         pg_class,  pg_attribute  and  pg_type as we do for tables
> >         and views to describe a structure.
> 
> It seems fairly ugly to have a pg_class entry for something that
> isn't a table or even a table-like entity. 

I dont think that sequence is any more table-like than record.

And difference between type and class ia also quite debatable in 
most languages ;)

Also there seems to be more existing creative use of pg_class - what 
does relkind='s' record for pg_variable stand for ?

> Otherwise this proposal sounds good.  Jan and I talked about it earlier;
> one point I recall is that the portal/cursor based approach can
> internally support the existing multiple-call implementation of
> functions returning sets.  That is, when you call the portal to get the
> next tuple, it might hand you back a tuple saved from a previous
> function call, or it might turn around and call the function again to
> get the next tuple.
> 
> BTW, once we've had this for a release or two, I'd like to rip out the
> existing support for calling functions-returning-sets during SELECT list
> evaluation, so that expression evaluation could be simplified and sped
> up.  But we can wait for people to change over their existing uses
> before we do that.

How hard would it be to turn this around and implement RETURN AND
CONTINUE
for at least PL/PGSQL, and possibly C/Perl/Python ... ?

---------------
Hannu

Thu, 28 Jun 2001 00:47:19 +0500
Hannu Krosing <hannu@tm.ee>
Re: functions returning records
Hannu Krosing <hannu@tm.ee> writes:
> Tom Lane wrote:
>> It seems fairly ugly to have a pg_class entry for something that
>> isn't a table or even a table-like entity. 

> I dont think that sequence is any more table-like than record.

Oh?  It's got storage, it's got columns, you can select from it.

test71=# create sequence myseq;
CREATE
test71=# select * from myseq;
 sequence_name | last_value | increment_by | max_value  | min_value | cache_value | log_cnt | is_cycled | is_called
---------------+------------+--------------+------------+-----------+-------------+---------+-----------+-----------
 myseq         |          1 |            1 | 2147483647 |         1 |           1 |       1 | f         | f
(1 row)

Looks pretty table-ish to me.

> Also there seems to be more existing creative use of pg_class - what 
> does relkind='s' record for pg_variable stand for ?

Special system relation.  Again, there's storage behind it (at least for
pg_log, I suppose pg_xactlock is a bit of a cheat... but there doesn't
really need to be a pg_class entry for pg_xactlock anyway, and I'm not
sure pg_log needs one either).

However, this is fairly academic considering the backwards-compatibility
downside of changing pg_attribute.attrelid to pg_attribute.atttypid :-(

			regards, tom lane

Wed, 27 Jun 2001 19:03:02 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: functions returning records 
On Thu, 28 Jun 2001, Hannu Krosing wrote:

> Tom Lane wrote:
> > 
> > Jan Wieck <JanWieck@Yahoo.com> writes:
> > >     1.  Adding a new relkind  that  means  'record'.  So  we  use
> > >         pg_class,  pg_attribute  and  pg_type as we do for tables
> > >         and views to describe a structure.
> > 
> > It seems fairly ugly to have a pg_class entry for something that
> > isn't a table or even a table-like entity. 
> 
> I dont think that sequence is any more table-like than record.
> 
> And difference between type and class ia also quite debatable in 
> most languages ;)
> 
> Also there seems to be more existing creative use of pg_class - what 
> does relkind='s' record for pg_variable stand for ?
> 
> > Otherwise this proposal sounds good.  Jan and I talked about it earlier;
> > one point I recall is that the portal/cursor based approach can
> > internally support the existing multiple-call implementation of
> > functions returning sets.  That is, when you call the portal to get the
> > next tuple, it might hand you back a tuple saved from a previous
> > function call, or it might turn around and call the function again to
> > get the next tuple.
> > 
> > BTW, once we've had this for a release or two, I'd like to rip out the
> > existing support for calling functions-returning-sets during SELECT list
> > evaluation, so that expression evaluation could be simplified and sped
> > up.  But we can wait for people to change over their existing uses
> > before we do that.
> 
> How hard would it be to turn this around and implement RETURN AND
> CONTINUE
> for at least PL/PGSQL, and possibly C/Perl/Python ... ?
Cannot talk about plpgsql, but for c this would be probably implemented
with setjmp and with perl with goto. Probably not very complex.

-alex


Wed, 27 Jun 2001 20:10:12 -0400 (EDT)
Alex Pilosov <alex@pilosoft.com>
Re: functions returning records
Alex Pilosov wrote:
> On Thu, 28 Jun 2001, Hannu Krosing wrote:
> >
> > How hard would it be to turn this around and implement RETURN AND
> > CONTINUE
> > for at least PL/PGSQL, and possibly C/Perl/Python ... ?
> Cannot talk about plpgsql, but for c this would be probably implemented
> with setjmp and with perl with goto. Probably not very complex.

    Don't  think  so.  When  the function returns, the call stack
    get's destroyed. Jumping back to there - er - the  core  dump
    is not even useful any more. Or did I miss something?


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


Wed, 27 Jun 2001 21:08:38 -0400 (EDT)
Jan Wieck <JanWieck@Yahoo.com>
Re: functions returning records
On Wed, 27 Jun 2001, Jan Wieck wrote:

> Alex Pilosov wrote:
> > On Thu, 28 Jun 2001, Hannu Krosing wrote:
> > >
> > > How hard would it be to turn this around and implement RETURN AND
> > > CONTINUE
> > > for at least PL/PGSQL, and possibly C/Perl/Python ... ?
> > Cannot talk about plpgsql, but for c this would be probably implemented
> > with setjmp and with perl with goto. Probably not very complex.
> 
>     Don't  think  so.  When  the function returns, the call stack
>     get's destroyed. Jumping back to there - er - the  core  dump
>     is not even useful any more. Or did I miss something?

Well, it shouldn't return, but instead save the location and longjmp to
SPI_RESUME_jmp location. On a next call, instead of a function call, it
should longjmp back to saved location. I have to admit its more complex
than I originally thought, but probably doable.

-alex


Wed, 27 Jun 2001 21:24:09 -0400 (EDT)
Alex Pilosov <alex@pilosoft.com>
Re: functions returning records

 The other thing:

 1/	SELECT a, b, c FROM foo();
 2/	SELECT a FROM foo();

 How result (build and) returns function foo() in example 1/ and 2/ ?

 It's bad functions if returns same result for both queries -- because 
 in example 2/ is wanted only one columns. IMHO function returning 
 records needs information about wanted result (number of columns, etc).

 For example trigger functions has specific information by
 "CurrentTriggerData" struct. For functions returning records we can 
 create special struct too. What?

				Karel

-- 
 Karel Zak  <zakkr@zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/
 
 C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz

Thu, 28 Jun 2001 09:41:11 +0200
Karel Zak <zakkr@zf.jcu.cz>
Re: functions returning records
Alex Pilosov wrote:
> On Wed, 27 Jun 2001, Jan Wieck wrote:
>
> > Alex Pilosov wrote:
> > > On Thu, 28 Jun 2001, Hannu Krosing wrote:
> > > >
> > > > How hard would it be to turn this around and implement RETURN AND
> > > > CONTINUE
> > > > for at least PL/PGSQL, and possibly C/Perl/Python ... ?
> > > Cannot talk about plpgsql, but for c this would be probably implemented
> > > with setjmp and with perl with goto. Probably not very complex.
> >
> >     Don't  think  so.  When  the function returns, the call stack
> >     get's destroyed. Jumping back to there - er - the  core  dump
> >     is not even useful any more. Or did I miss something?
>
> Well, it shouldn't return, but instead save the location and longjmp to
> SPI_RESUME_jmp location. On a next call, instead of a function call, it
> should longjmp back to saved location. I have to admit its more complex
> than I originally thought, but probably doable.

    OK, let's screw it up some more:

        SELECT F.a, B.b FROM foo() F, bar() B
            WHERE F.a = B.a;

    This should normally result in a merge join, so you might get
    away with longjmp's. But you get the idea.


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


Thu, 28 Jun 2001 07:50:26 -0400 (EDT)
Jan Wieck <JanWieck@Yahoo.com>
Re: functions returning records

alex@pilosoft.com (Alex Pilosov) writes:

: [...]
: Well, it shouldn't return, but instead save the location and longjmp to
: SPI_RESUME_jmp location. On a next call, instead of a function call, it
: should longjmp back to saved location. I have to admit its more complex
: than I originally thought, but probably doable.

Implementing (what are in effect) co-routines or continuations by
setjmp/longjmp is an inherently non-portable practice.  (Think about
how at all SPI_RESUME_jmp *and* the user-defined-function's saved
location could both be valid places to longjmp to at, the same time.)
At the least, you would need some assembly language code, and
heap-allocated stacks.  Take a look into what user-level threading
libraries do.

If you went down this avenue, you might decide that a reasonable way
to do this is in fact to rely on first-class threads to contain the
execution context of user-defined functions.  You wouldn't have the
concurrency problems normally associated with threads (since the
server would still only activate one thread at a time).

- FChE

28 Jun 2001 08:27:26 -0400
fche@redhat.com (Frank Ch. Eigler)
Re: functions returning records
Jan Wieck wrote:
> Alex Pilosov wrote:
> > On Wed, 27 Jun 2001, Jan Wieck wrote:
> >
> > > Alex Pilosov wrote:
> > > > On Thu, 28 Jun 2001, Hannu Krosing wrote:
> > > > >
> > > > > How hard would it be to turn this around and implement RETURN AND
> > > > > CONTINUE
> > > > > for at least PL/PGSQL, and possibly C/Perl/Python ... ?
> > > > Cannot talk about plpgsql, but for c this would be probably implemented
> > > > with setjmp and with perl with goto. Probably not very complex.
> > >
> > >     Don't  think  so.  When  the function returns, the call stack
> > >     get's destroyed. Jumping back to there - er - the  core  dump
> > >     is not even useful any more. Or did I miss something?
> >
> > Well, it shouldn't return, but instead save the location and longjmp to
> > SPI_RESUME_jmp location. On a next call, instead of a function call, it
> > should longjmp back to saved location. I have to admit its more complex
> > than I originally thought, but probably doable.
>
>     OK, let's screw it up some more:
>
>         SELECT F.a, B.b FROM foo() F, bar() B
>             WHERE F.a = B.a;
>
>     This should normally result in a merge join, so you might get
>     away with longjmp's. But you get the idea.

    On  a  third  thought, you don't get anywhere with longjmp's.
    You have a  call  stack,  do  a  setjmp()  saving  the  stack
    pointer. Then you call the function, do another setjmp() here
    and do the longjmp() to #1. This  restores  the  saved  stack
    pointer,  so at the very first time you do any other function
    call (lib calls included), you corrupt the stack frame at the
    current  stack  pointer  position.  If you later jump back to
    setjmp() #2 location, you'll not be able to return.

    You can only drop stack frames safely,  you  can't  add  them
    back, they aren't saved.


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


Thu, 28 Jun 2001 08:40:29 -0400 (EDT)
Jan Wieck <JanWieck@Yahoo.com>
Re: functions returning records
On Thu, 28 Jun 2001, Jan Wieck wrote:

> 
>     On  a  third  thought, you don't get anywhere with longjmp's.
>     You have a  call  stack,  do  a  setjmp()  saving  the  stack
>     pointer. Then you call the function, do another setjmp() here
>     and do the longjmp() to #1. This  restores  the  saved  stack
>     pointer,  so at the very first time you do any other function
>     call (lib calls included), you corrupt the stack frame at the
>     current  stack  pointer  position.  If you later jump back to
>     setjmp() #2 location, you'll not be able to return.
> 
>     You can only drop stack frames safely,  you  can't  add  them
>     back, they aren't saved.
True. I withdraw the idea. 

See this for a s[l]ick implementation of coroutines in C:

http://www.chiark.greenend.org.uk/~sgtatham/coroutines.html

(essentially a replacement for set of gotos)

Tis ugly, but it should work (tm).



Thu, 28 Jun 2001 13:24:32 -0400 (EDT)
Alex Pilosov <alex@pilosoft.com>
Re: functions returning records