Re: select from function

Darren Ferguson <darren@crystalballinc.com> writes:
> So this leads me to believe that it is not supported this way

You forgot to open the cursor.

regression=# CREATE OR REPLACE FUNCTION test(integer,integer) RETURNS REFCURSOR AS '
regression'# DECLARE
regression'# test cursor for SELECT * from tenk1;
regression'# begin
regression'# open test;
regression'# RETURN test;
regression'# END;' LANGUAGE 'plpgsql';
CREATE
regression=# begin;
BEGIN
regression=# select test(4,5);
 test
------
 test
(1 row)

regression=# fetch forward 1 from test;
 unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even |
stringu1| stringu2 | string4 

---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
    8800 |       0 |   0 |    0 |   0 |      0 |       0 |      800 |         800 |      3800 |     8800 |   0 |    1 |
MAAAAA  | AAAAAA   | AAAAxx 
(1 row)

regression=#

I dunno why plpgsql is defined to need an OPEN for a cursor, but it is.

            regards, tom lane