Thread: AW: AW: functions returning records
> >> 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. Andreas
On Wed, 27 Jun 2001, 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. Its a different problem. Functions returning tables do just that, return tables, they won't care just what from that table you need. Exposing pieces of optimizer to your function doesn't seem to me like a great idea...
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' ;
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
>
>