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