Thread: 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:

> Here is what I need to do:
> select from table1,myFunction(parameter1,parameter2)
> where...
> assuming that myFunction returns refcursor.
>
> Can this be done? If yes, then how?
>
> Anna.
>
>

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