Hi folks,
I want to scroll through the results of the same query inside a PL/pgSQL function several times and learned that cursors can help me doing that. However, the command "MOVE FIRST FROM <refcursor>" is not accepted by the parser. Likewise, I get a similar error message when using the FETCH command together with BACKWARD or FIRST. Is that intended? Are refcursor variables only usable for forward scrolling? If yes, is there another way of looping through the results of the same query/cursor several times?
I attached the script in question below. It would be great if anybody could help me with this bug/problem!
Cheers,
Ingo
--------------------------------------------------------------
>>> My PL/pgSQL script is as follows:
CREATE OR REPLACE FUNCTION get_matching_triangles_by_range()
RETURNS SETOF matchresulttype AS '
DECLARE
[...]
ltri_cursor refcursor;
BEGIN
last_code := -1;
FOR stri IN SELECT * FROM stri_geoms
LOOP
IF (stri.scode <> last_code) THEN
IF (last_code <> -1) THEN
CLOSE ltri_cursor;
END IF;
last_code := stri.scode;
OPEN ltri_cursor FOR SELECT * FROM direct_codes WHERE
(iac_types = stri.iac_types);
ELSE
MOVE FIRST FROM ltri_cursor;
END IF;
FETCH ltri_cursor INTO ltri;
WHILE FOUND LOOP
[...]
FETCH ltri_cursor INTO ltri;
END LOOP;
END LOOP;
RETURN; END; ' LANGUAGE plpgsql;
>>> I execute the function as follows, causing the following error message:
new_test_lib=# SELECT * FROM get_matching_triangles_by_range();
WARNING: Error occurred while executing PL/pgSQL function get_matching_triangles_by_range
WARNING: line 36 at SQL statement
ERROR: parser: parse error at or near "FROM" at character 12
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
