Re: Re: functions returning records - Mailing list pgsql-hackers

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