Re: select from function

Tom Lane wrote:
> 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.

Yes, I find the refcursor stuff confusing because there are so many
syntaxes supported. I documented them in the current CVS docs. It
shows a version with no DECLARE:

CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS '
BEGIN
OPEN $1 FOR SELECT col FROM test;
RETURN $1;
END;
' LANGUAGE 'plpgsql';

and one with a DECLARE:

CREATE FUNCTION reffunc2() RETURNS refcursor AS '
DECLARE
ref refcursor;
BEGIN
OPEN ref FOR SELECT col FROM test;
RETURN ref;
END;
' LANGUAGE 'plpgsql';

The second uses a dynamic cursor name. Seems there is even a third
syntax you showed where the query is in the DECLARE section and not in
the OPEN.

Not sure which syntax to promote. Your syntax looks good with the
DECLARE defining the cursor, but it is a variable DECLARE rather than a
cursor declare, so that could be confusing.

It seems the big difference is that you declare a 'cursor', which
creates its own refcursor and associates the query with the refcursor,
if I am reading plpgsql gram.y correctly.

For clarity purposes, I will probably keep our documentation unchanged:

http://developer.postgresql.org/docs/postgres/plpgsql-cursors.html

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026