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 по дате отправления:

Предыдущее
От: "John R Pierce"
Дата:
Сообщение: Re: BUG #1232: Singapore Timezone missing
Следующее
От: Gaetano Mendola
Дата:
Сообщение: Re: BUG #1231: Probelm with transactions in stored code.