Skip site navigation (1) Skip section navigation (2)

Re: functions returning records

From: mlw <markw(at)mohawksoft(dot)com>
To: Zeugswetter Andreas SB <ZeugswetterA(at)wien(dot)spardat(dot)at>
Cc: "'Reinoud van Leeuwen'" <reinoud(at)xs4all(dot)nl>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: functions returning records
Date: 2001-06-27 11:22:48
Message-ID: 3B39C208.C5B8BBEF@mohawksoft.com (view raw or flat)
Thread:
Lists: 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 |  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' ;

In response to

Responses

pgsql-hackers by date

Next:From: Karel ZakDate: 2001-06-27 12:09:38
Subject: Re: AW: functions returning records
Previous:From: Alex PilosovDate: 2001-06-27 11:21:17
Subject: Re: AW: AW: AW: functions returning records

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group