MOVE FIRST does not work in PL/pgSQL on refcursor
От | Ingo Schellhammer |
---|---|
Тема | MOVE FIRST does not work in PL/pgSQL on refcursor |
Дата | |
Msg-id | 412D8EC5.20000@zbh.uni-hamburg.de обсуждение исходный текст |
Список | pgsql-bugs |
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
В списке pgsql-bugs по дате отправления: