Re: cursors as table sources - Mailing list pgsql-general

From Tom Lane
Subject Re: cursors as table sources
Date
Msg-id 20550.1137005644@sss.pgh.pa.us
Whole thread Raw
In response to Re: cursors as table sources  (Will Glynn <wglynn@freedomhealthcare.org>)
List pgsql-general
Will Glynn <wglynn@freedomhealthcare.org> writes:
> Why can't I SELECT multi_column_function(t.a) FROM some_table t?

You can.  At least if you're running a recent release ;-)

regression=# create function foo(int, out f1 int, out f2 int) as $$
regression$# begin
regression$#   f1 := $1 + 1;
regression$#   f2 := $1 + 2;
regression$# end$$ language plpgsql;
CREATE FUNCTION
regression=# select unique1, foo(unique1) from tenk1;
 unique1 |    foo
---------+-------------
    8800 | (8801,8802)
    1891 | (1892,1893)
    3420 | (3421,3422)
    9850 | (9851,9852)
    7164 | (7165,7166)
...

> The other option is to make
> multi_column_function actually return a single column in some way that
> the application can split it apart again, but that's really ugly.

That takes a little more hacking, but:

regression=# select unique1,(foo).* from (select unique1, foo(unique1) from tenk1 offset 0) ss;
 unique1 |  f1  |  f2
---------+------+------
    8800 | 8801 | 8802
    1891 | 1892 | 1893
    3420 | 3421 | 3422
    9850 | 9851 | 9852
    7164 | 7165 | 7166
...

(The OFFSET hack is to ensure the query doesn't get flattened into a
form where foo() will be evaluated multiple times per row.)

            regards, tom lane

pgsql-general by date:

Previous
From: Jaime Casanova
Date:
Subject: Re: Create Produre for DROP row
Next
From: "Jim C. Nasby"
Date:
Subject: Re: cursors as table sources