PostgreSQL で変数を使う

PostgreSQL のクエリ内で変数を使う方法

運用環境で連番を持つマスタレコードをクエリを使って追加したい場合など、 採番結果を後続のクエリで適用したい状況で、どのように解決するか？ このような場合、いくつか方法がある。

 
1. WITH の利用
2. PL/pgSQL の利用

他にも物理的な一時テーブルを作っての対処などでも目的は果たせるが、 それは通常の DB の使い方と変わらないため、ここでは省略する。

1. WITH の利用

以下のクエリが参考になる。

WITH variables AS (
    SELECT 10 AS my_variable
)
SELECT my_variable
  FROM variables;

WITH で定義した内容を後続のクエリで参照して使うことができる。

 
この方法では、WITH で一時テーブルを作り、その直後のクエリで参照すれば使えるだけであり、 一度採番した内容を変数に格納し、連続したクエリで変数を使い回すような処理には適用できない。 そのため、使える局面はかなり限定される。わざわざ覚えて使うほどのものではないと感じる。

 

2. PL/pgSQL の利用

stored procedure をクエリ上で使う方法である。

 
この方法であれば一般的なプログラミングと同じように 完全な変数宣言が可能となるため、連続した処理でも使い勝手が良い方法となる。

 
PL/pgSQL は PL/SQL や Pascal系の言語を書いたことがある人であれば覚える量も少なく簡単に書ける。

 
以下のようなコードが採番処理とレコードの登録、マスタからリンク整備の雛形として使えるだろう。

DO $$ 
DECLARE
	master_id VARCHAR;
	copy_id VARCHAR;
	temp_no INTEGER;
	copy_no INTEGER;
	rec RECORD;
BEGIN
	SELECT '今回追加するマスタID' INTO master_id;
	SELECT 'リンク内容をコピーするID' INTO copy_id;

	-- 登録済判定
	SELECT m.no INTO temp_no FROM master_table m WHERE m.id = master_id;
	IF (temp_no <> 0) THEN
		RAISE NOTICE 'ID:[%] 登録済のため中断', master_id;
		RETURN;
	END IF;

	-- 採番と登録
	SELECT Coalesce(Max(m.no), 0) + 1 INTO tempNo FROM master_table m;
	INSERT INTO master_table (no, id, ... 必要なカラム)
	VALUES (temp_no, master_id, ... マスタ設定内容);
	RAISE NOTICE 'ID:[%] 登録完了', master_id;

	-- 指定ID と同じグループにリンクを貼る
	SELECT m.no INTO copy_no FROM master_table m WHERE m.id = copy_id;
	IF (copy_no = 0) THEN
		RAISE NOTICE 'ID:[%] コピー元が未登録のため中断', copy_id;
	END IF

	-- リンクテーブルに対するSELECT結果を使って新規にリンクを登録する
	FOR rec IN SELECT * FROM link_table l WHERE l.no = copy_no;
	LOOP
		INSERT INTO link_table (no, linkNo) VALUES (temp_no, rec.linkNo);
	END LOOP;
	RAISE NOTICE 'ID:[%] リンク整備 完了', master_id;
END $$;

PL/pgSQL だと癖はあるが、SQL上で可能な操作は全てできるため、ロジックを組む力さえあればなんとでもなる。