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