PL/pgSQL で FOR 文を書いて動かしてみる

PostgreSQL では、PL/pgSQL という手続き言語を使うことができる。標準の SQL にはないループ文などを書くことができるため、試しに FOR 文を動かしてみる。

サンプルデータ

過去の記事でも利用している、都道府県コード（id）とローマ字表記（name）のテーブルで試してみる。

postgres=# SELECT * FROM prefecture ORDER BY id LIMIT 10;
 id |   name    
----+-----------
  1 | Hokkaido
  2 | Aomori
  3 | Iwate
  4 | Miyagi
  5 | Akita
  6 | Yamagata
  7 | Fukushima
  8 | Ibaraki
  9 | Tochigi
 10 | Gunma
(10 rows)

動かしてみたコード

例えば、サンプルデータのテーブルから id と name を SELECT し、1行ずつ出力するようなスクリプトは以下のようになる。

DO $$
DECLARE rec RECORD;
BEGIN
    FOR rec IN SELECT id, name FROM prefecture
    LOOP
        RAISE NOTICE '%, %', rec.id, rec.name;
    END LOOP;
END; $$ LANGUAGE plpgsql;

DECLARE rec RECORD はいわゆる変数宣言の部分で、こちらで宣言した変数 rec に SELECT 文の結果が入るようになる。

また、RAISE NOTICE はターミナルにメッセージを出力する部分で、'%' の部分は指定したパラメータで順に置き換わって出力される。

出力は以下のようになる。

NOTICE:  1, Hokkaido
NOTICE:  2, Aomori
NOTICE:  3, Iwate

（ ... 中略 ... ）

NOTICE:  45, Miyazaki
NOTICE:  46, Kagoshima
NOTICE:  47, Okinawa

他にも、[1-10] のようなリストを作成して id を指定するような FOR ループを回すこともできる。

DO $$ 
DECLARE rec RECORD;
BEGIN
    FOR counter IN 1..10
    LOOP
        SELECT * INTO rec FROM prefecture WHERE id = counter;
        RAISE NOTICE '%', rec.name;
    END LOOP;
END; $$ LANGUAGE plpgsql;

注意点として、SELECT 結果を RECORD に入れるなど他の場所で利用せずに実行しようとした場合はエラーとなり実行が失敗する。

postgres=# DO $$ 
BEGIN
    FOR counter IN 1..10
    LOOP
        SELECT * FROM prefecture WHERE id = counter;
    END LOOP;
END; $$ LANGUAGE plpgsql;

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function inline_code_block line 5 at SQL statement

SELECT だけしたい場合は PERFORM を利用する必要があるということではあるが、出力や他の箇所での利用もしないのであれば実行しても何も起きないのと同義なので、エラーが出た場合は処理内容を見直して修正するのが良さそうである。

参考サイト

PL/pgSQL では FOR 以外にも色々な制御文が使える。ドキュメントにもサンプルコードが載っているため参考になる。