Объявление курсорных переменных и открытие курсора

Курсорная переменная - это понятие PL/pgSQL, с помощью которого организована работа с курсорами.

Создадим таблицу: 

=> CREATE TABLE t(id integer, s text);

CREATE TABLE

=> INSERT INTO t VALUES (1, 'Раз'), (2, 'Два'), (3, 'Три');

INSERT 0 3

Несвязанная переменная cur: 

=> DO $$
DECLARE
    -- объявление переменной
    cur refcursor; 
BEGIN
    -- связывание с запросом и открытие курсора
    OPEN cur FOR SELECT * FROM t;
END;
$$;

DO

Связанная переменная связывается с запросом уже при объявлении. 

=> DO $$
DECLARE
    -- объявление и связывание переменной
    cur CURSOR FOR SELECT * FROM t;
BEGIN
    -- открытие курсора
    OPEN cur; 
END;
$$;

DO

При этом переменная cur имеет тот же тип refcursor.

Связанная переменная может иметь параметры: 

=> DO $$
DECLARE
    -- объявление и связывание переменной
    cur CURSOR(id integer) FOR SELECT * FROM t WHERE t.id = cur.id;
BEGIN
    -- открытие курсора с указанием фактических параметров
    OPEN cur(1);
END;
$$;

DO

Обратите внимание на устранение неоднозначности имен в этом и следующем примерах.

Переменные PL/pgSQL также являются (неявными) параметрами курсора. 

=> DO $$
<<local>>
DECLARE
    id integer := 3;
    -- объявление и связывание переменной
    cur CURSOR FOR SELECT * FROM t WHERE t.id = local.id;
BEGIN
    id := 1;
    -- открытие курсора (значение id берется на этот момент)
    OPEN cur;
END;
$$;

DO

Чтение данных из курсора

Чтение выполняется командой FETCH. Если нужно только сдвинуть "окно" курсора, то можно воспользоваться другой командой - MOVE. 

=> DO $$
DECLARE
    cur refcursor;
    rec record;
BEGIN
    OPEN cur FOR SELECT * FROM t ORDER BY id;
    MOVE cur;
    FETCH cur INTO rec;
    RAISE NOTICE '%', rec;
    CLOSE cur;
END;
$$;

Что будет выведено на экран? 

NOTICE:  (2,Два)
DO

Обычный способ организации цикла: 

=> DO $$
DECLARE
    cur refcursor;
    rec record;
BEGIN
    OPEN cur FOR SELECT * FROM t;
    LOOP
        FETCH cur INTO rec;
        EXIT WHEN NOT FOUND;
        RAISE NOTICE '%', rec;
    END LOOP;
    CLOSE cur;
END;
$$;

NOTICE:  (1,Раз)
NOTICE:  (2,Два)
NOTICE:  (3,Три)
DO

Чтобы не писать много команд, можно воспользоваться командой FOR, которая делает ровно то же самое: 

=> DO $$
DECLARE
    cur CURSOR FOR SELECT * FROM t;
BEGIN
    FOR rec IN cur LOOP -- cur должна быть связана с запросом
        RAISE NOTICE '%', rec;
    END LOOP;
END;
$$;

NOTICE:  (1,Раз)
NOTICE:  (2,Два)
NOTICE:  (3,Три)
DO

Более того, можно вообще обойтись без явной работы с курсором, если цикл - это все, что требуется. И такой цикл будет работать быстрее (из-за выборки по 10 строк, а не по одной). 

=> DO $$
DECLARE
    rec record; -- надо объявить явно
BEGIN
    FOR rec IN (SELECT * FROM t) LOOP
        RAISE NOTICE '%', rec;
    END LOOP;
END;
$$;

NOTICE:  (1,Раз)
NOTICE:  (2,Два)
NOTICE:  (3,Три)
DO

Скобки вокруг запроса не обязательны, но удобны.

Точно так же, как и цикл LOOP, FOR позволяет указать метку - во вложенных циклах это может оказаться полезным: 

=> DO $$
DECLARE
    rec_outer record;
    rec_inner record;
BEGIN
    <<OUTER>>
    FOR rec_outer IN (SELECT * FROM t ORDER BY id) LOOP
        <<INNER>>
        FOR rec_inner IN (SELECT * FROM t ORDER BY id) LOOP
            EXIT OUTER WHEN rec_inner.id = 3;
            RAISE NOTICE '%, %', rec_outer, rec_inner;
        END LOOP INNER;
    END LOOP OUTER;
END;
$$;

Что будет выведено? 

NOTICE:  (1,Раз), (1,Раз)
NOTICE:  (1,Раз), (2,Два)
DO

Переменная FOUND позволяет узнать, была ли обработана хотя бы одна строка: 

=> DO $$
DECLARE
    rec record;
BEGIN
    FOR rec IN (SELECT * FROM t WHERE false) LOOP
        RAISE NOTICE '%', rec;
    END LOOP;
    RAISE NOTICE 'Была ли как минимум одна итерация? %', FOUND;
END;
$$;

NOTICE:  Была ли как минимум одна итерация? f
DO

Следует заметить, что в большом числе случаев вместо использования циклов можно выполнить задачу одним оператором SQL - и это будет проще и еще быстрее. Часто циклы используют просто потому, что это более привычный, "процедурный" стиль программирования. Но для базы данных этот стиль не подходит.

Например: 

=> BEGIN;
DO $$
DECLARE
    rec record;
BEGIN
    FOR rec IN (SELECT * FROM t) LOOP
        RAISE NOTICE '%', rec;
        DELETE FROM t WHERE id = rec.id;
    END LOOP;
END;
$$;
ROLLBACK;

BEGIN
NOTICE:  (1,Раз)
NOTICE:  (2,Два)
NOTICE:  (3,Три)
DO
ROLLBACK

Такой цикл заменяется одной простой командой: 

=> BEGIN;
DELETE FROM t RETURNING *;
ROLLBACK;

BEGIN
 id |  s  
----+-----
  1 | Раз
  2 | Два
  3 | Три
(3 rows)

DELETE 3
ROLLBACK

Обновление или удаление текущей строки

Продемонстрируем обработку в цикле с обновлением строки, выбранной курсором. Типичный случай - обработка пакета заданий с изменением статуса задания. 

=> DO $$
DECLARE
    cur refcursor;
    rec record;
BEGIN
    OPEN cur FOR SELECT * FROM t FOR UPDATE;
    LOOP
        FETCH cur INTO rec;
        EXIT WHEN NOT FOUND;
        UPDATE t SET s = s || ' (обработано)' WHERE CURRENT OF cur;
    END LOOP;
    CLOSE cur;
END;
$$;

DO

=> SELECT * FROM t;

 id |        s         
----+------------------
  1 | Раз (обработано)
  2 | Два (обработано)
  3 | Три (обработано)
(3 rows)

Аналогичный результат можно получить, явно указав в команде UPDATE уникальный ключ таблицы (WHERE id = rec.id). CURRENT OF работает быстрее и не использует индекс, но применимость такой конструкции ограниченная.

Возврат курсора клиенту

Откроем курсор и посмотрим значение курсорной переменной: 

=> DO $$
DECLARE
    cur refcursor;
BEGIN
    OPEN cur FOR SELECT * FROM t;
    RAISE NOTICE '%', cur;
END;
$$;

NOTICE:  <unnamed portal 10>
DO

Это имя курсора (портала), который был открыт на сервере. Имя было сгенерировано автоматически.

При желании имя можно задать явно (оно должно быть уникальным): 

=> DO $$
DECLARE
    cur refcursor := 'cursor12345';
BEGIN
    OPEN cur FOR SELECT * FROM t;
    RAISE NOTICE '%', cur;
END;
$$;

NOTICE:  cursor12345
DO

Пользуясь этим, можно написать функцию, которая откроет курсор и вернет его имя: 

=> CREATE FUNCTION t_cur() RETURNS refcursor AS $$
DECLARE
    cur refcursor;
BEGIN
    OPEN cur FOR SELECT * FROM t;
    RETURN cur;
END;
$$ VOLATILE LANGUAGE plpgsql;

CREATE FUNCTION

Начнем транзакцию, вызовем функцию, узнаем имя курсора и получим возможность читать из него данные. Как это сделать - зависит от языка программирования. На psql это можно сделать так: 

=> BEGIN;

BEGIN

=> SELECT t_cur() AS curname \gset





=> \echo :curname





<unnamed portal 11>





=> FETCH :"curname"; -- кавычки нужны из-за угловых скобок в имени





 id |        s         
----+------------------
  1 | Раз (обработано)
(1 row)






=> COMMIT;





COMMIT






Чтобы клиенту было проще, можно позволить ему самому устанавливать имя курсора:





=> DROP FUNCTION t_cur();





DROP FUNCTION





=> CREATE FUNCTION t_cur(cur refcursor) RETURNS void AS $$
BEGIN
    OPEN cur FOR SELECT * FROM t;
END;
$$ VOLATILE LANGUAGE plpgsql;





CREATE FUNCTION







=> BEGIN;





BEGIN





=> SELECT t_cur('cursor12345');





 t_cur 
-------
 
(1 row)






=> FETCH cursor12345;





 id |        s         
----+------------------
  1 | Раз (обработано)
(1 row)






=> COMMIT;





COMMIT






Функция может вернуть и несколько открытых курсоров, используя OUT-параметры.




Таким образом можно за один вызов функции обеспечить клиента информацией из
разных таблиц, если это необходимо.






Конец демонстрации.