Re: functions returning records - Mailing list pgsql-hackers

Zeugswetter Andreas SB wrote:
> 
> > >>  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;
> > >
> > > Yes, optimal would imho also be if the foo() somehow had access to the
> > > where restriction, so it could only produce output, that the
> > > higher level is interested in, very cool. This would be extremely
> > > useful for me. Very hard to implement, or even find an appropriate
> > > interface for though.
> >
> > You could easily implement it *in* the function foo IMHO. Since the
> > function does some black magic to create the result set to begin with, you
> > can change it to use parameters:
> >
> > select name1, name2 from foo(10, NULL, NULL) where name1 > 10;
> 
> Yes, but this is only an answer to a limited scope of the problem at hand,
> and the user who types the select (or uses a warehouse tool) needs substantial
> additional knowledge on how to efficiently construct such a query.
> 
> In my setup the function would be hidden by a view.

I have done a lot of playing around with this sort of thing to get my search
engine working.

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)

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 |  910 | 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 |  910 | 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' ;