Re: functions returning records

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