Re: Re: functions returning records

On Wed, 27 Jun 2001, mlw wrote:

> While functions returning rows would be cool, and something I'd like
> to see. I think the functionality, if not the syntax, you are looking
> for is already in postgres 7.1.x. Here is an example: (Actual code at
> bottom of message)
Yes, its already possible, but its extremely ugly and nontransparent. I
don't want to create 5 functions to return 5-row tuple, or have to deal
with C SPI to do that. It needs a minor cleanup which is all I'm trying to
do :)

> select n1, n2 from (select foo1(10) as n1, foo2() as n2) as fubar ;
>
> The trick seems to be, to have the first function return a 'setof' results.
> Have the foo2() function return the next column of foo1()'s current result.
>
> Here is the output:
>
> markw=# select foo1(10) as n1, foo2() as n2;
> n1 | n2
> ----+----
> 1 | 1
> 2 | 2
> 3 | 3
> 4 | 4
> 5 | 5
> 6 | 6
> 7 | 7
> 8 | 8
> 9 | 9
> 10 | 10
> (10 rows)
>
> Or you can create a synthetic table at query time, called fubar:
>
> markw=# select * from (select foo1(10) as n1, foo2() as n2) as fubar;
> n1 | n2
> ----+----
> 1 | 1
> 2 | 2
> 3 | 3
> 4 | 4
> 5 | 5
> 6 | 6
> 7 | 7
> 8 | 8
> 9 | 9
> 10 | 10
> (10 rows)
>
>
> Now, I'm not sure if it is documented that the first function gets called
> first, or that next functions get called after each result of a result "setof"
> but it seem logical that they should, and I would like to lobby that this
> becomes an "official" behavior of the function manager and the execution
> processing.
>
>
> <<<<<<<<<<<<< code >>>>>>>>>>>>>>
>
>
> static int count;
> static int curr;
>
> Datum foo1(PG_FUNCTION_ARGS);
> Datum foo2(PG_FUNCTION_ARGS);
>
> Datum foo1(PG_FUNCTION_ARGS)
> {
> if(!fcinfo->resultinfo)
> {
> elog(ERROR, "Not called with fcinfo");
> PG_RETURN_NULL();
> }
> if(!count)
> {
> count = PG_GETARG_INT32(0);
> curr = 1;
> }
> else
> curr++;
>
> if(curr <= count)
> {
> ReturnSetInfo *rsi = (ReturnSetInfo *)fcinfo->resultinfo;
> rsi->isDone = ExprMultipleResult;
> PG_RETURN_INT32(curr);
> }
> else
> {
> ReturnSetInfo *rsi ;
> curr=0;
> count=0;
> rsi = (ReturnSetInfo *)fcinfo->resultinfo;
> rsi->isDone = ExprEndResult ;
> }
> PG_RETURN_NULL();
> }
>
> Datum foo2(PG_FUNCTION_ARGS)
> {
> if(curr <= count)
> PG_RETURN_INT32(curr);
> else
> PG_RETURN_INT32(42);
> }
>
> SQL:
>
> create function foo1( int4)
> returns setof int4
> as '/usr/local/lib/templ.so', 'foo1'
> language 'c' ;
>
> create function foo2()
> returns int4
> as '/usr/local/lib/templ.so', 'foo2'
> language 'c' ;
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl
>
>