Re: select from function

Darren Ferguson <darren(at)crystalballinc(dot)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