PostgreSQLでカーソルはすでに使われていますを回避する方法

事象

PostgreSQLのファンクション内でカーソルを開き、カーソルを閉じる前に別の(同一でも構わない)ファンクションを呼び出すと、カーソル名が被っている場合に、

ERROR: カーソル "my_cursor" はすでに使われています

といったエラーが発生する。一般的なプログラミング言語で考えれば、スコープが違うだろと思うが、PostgreSQLにおいてはそうではないらしい。異なるファンクションを呼び出すのであればカーソル名を一意にすればよいが、同一ファンクションを再帰的に呼び出す場合はその方法では解決できない。

エラー再現

エラーを再現するために、test_tableのnumに値が入ると全てのnumが5に書き換えられるという意味不明なファンクションを作ってみる。

-- テストテーブルの作成
CREATE TABLE test_table (
    id SERIAL,
    num INTEGER
);

-- エラー再現ファンクションの作成
CREATE FUNCTION cursor_error_function () RETURNS trigger
    LANGUAGE plpgsql
    AS $$

    DECLARE
        var_current_id INTEGER;
        error_cursor CURSOR FOR
            SELECT id FROM test_table WHERE num != 5;

    BEGIN
        OPEN error_cursor;

        <<read_loop>>
        LOOP
            FETCH error_cursor INTO var_current_id;
            
            IF NOT FOUND THEN
                EXIT read_loop;
            END IF;

            UPDATE test_table SET num = 5 WHERE id = var_current_id;
        END LOOP;

        CLOSE error_cursor;
        RETURN NULL;
    END;
    $$;

-- テーブルに設定
CREATE TRIGGER error_trigger
    AFTER INSERT OR UPDATE ON test_table FOR EACH ROW
    EXECUTE FUNCTION cursor_error_function();

-- 実行
INSERT INTO test_table (num) VALUES (3);

-- 結果
-- ERROR: カーソル "error_cursor" はすでに使われています
-- CONTEXT: PL/pgSQL関数cursor_error_function()の9行目 - OPEN
-- SQL文 "UPDATE test_table SET num = 5 WHERE id = var_current_id"
-- PL/pgSQL関数cursor_error_function()の20行目 - SQLステートメント

解決策

カーソルを使わなければよい。
ループ -> カーソルと考えがちであるが、FOR INを使っても同じ処理を書くことができる。

-- 上記で作ったトリガー・ファンクションの削除
DROP TRIGGER IF EXISTS error_trigger ON test_table;
DROP FUNCTION IF EXISTS cursor_error_function;

-- 正しく動くファンクションの作成
CREATE FUNCTION cursor_success_function () RETURNS trigger
    LANGUAGE plpgsql
    AS $$

    DECLARE
        var_current_id INTEGER;

    BEGIN
        FOR var_current_id IN (SELECT id FROM test_table WHERE num != 5)
            LOOP
                UPDATE test_table SET num = 5 WHERE id = var_current_id;
            END LOOP;

        RETURN NULL;
    END;
    $$;

-- テーブルに設定
CREATE TRIGGER success_trigger
    AFTER INSERT OR UPDATE ON test_table FOR EACH ROW
    EXECUTE FUNCTION cursor_success_function();

-- 実行
INSERT INTO test_table (num) VALUES (3);

-- 確認
SELECT * FROM test_table;

-- 結果
--  id | num
-- ----+-----
--   2 |   5
-- (1 行)