select from function

What would you do with the REFCURSOR from what i see you would not be able
to fetch the next row anyway and i do not believe from my knowledge.

You would have to have the cursor in a transaction but returning it from a
function does not seem to work yet.

Check below

dev=> begin
dev-> ;
BEGIN
dev=> declare test cursor for SELECT * from inv_locations;
DECLARE

dev=> fetch forward 1 from test;
inv_loc_id | inv_loc_disp_id | inv_loc_type | inv_loc_full_description |
sort
------------+-----------------+--------------+--------------------------+--------------
1368 | Section 4 | 20 | This is a test |
0/1006/1368/
(1 row)

dev=> fetch forward 1 from test;
inv_loc_id | inv_loc_disp_id | inv_loc_type |
inv_loc_full_description | sort
------------+-----------------+--------------+------------------------------------------------------------+--------------
1053 | Section 1 | 20 | This is the first section
of the Lansdowne housing project | 0/1006/1053/
(1 row)

dev=> fetch forward 1 from test;
inv_loc_id | inv_loc_disp_id | inv_loc_type |
inv_loc_full_description | sort
------------+-----------------+--------------+-------------------------------------------------+---------
1006 | Lansdowne | 16 | This is the primary SN for
the Openband company | 0/1006/
(1 row)

dev=> fetch backward 1 from test;
inv_loc_id | inv_loc_disp_id | inv_loc_type |
inv_loc_full_description | sort
------------+-----------------+--------------+------------------------------------------------------------+--------------
1053 | Section 1 | 20 | This is the first section
of the Lansdowne housing project | 0/1006/1053/
(1 row)

dev=> commit;

The above worked no problems

Now tried function

CREATE OR REPLACE FUNCTION test(integer,integer) RETURNS REFCURSOR AS '
DECLARE
one ALIAS FOR $1;
two ALIAS FOR $2;
test cursor for SELECT * from inv_locations;
BEGIN
RETURN test;
END;' LANGUAGE 'plpgsql';

dev=> begin;
BEGIN
dev=> select test(4,5);
test
------
test
(1 row)

dev=> fetch forward 1 from test;
NOTICE: PerformPortalFetch: portal "test" not found
FETCH 0
dev=> rollback;
ROLLBACK
dev=>

So this leads me to believe that it is not supported this way

HTH

Darren Ferguson

On Thu, 2 May 2002, Anna Dorofiyenko wrote:

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

Tom Lane wrote:

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.

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@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026