Re: 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.
>
>