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
