PostgreSQLでストアドプロシージャ開発をするその6 ～SQLを動的に組み立てる～

はじめに

前回に引き続き、ストアドプロシージャの開発に入門していく。
今回は動的にSQLを組み立てる方法について学ぶ。

動的SQLは、実行時に条件や値に応じてSQLを構築できるため、変数を使って組み立てる際に利用できる。

環境

Windows 11 Professional
PostgreSQL 17
Docker Desktop 4.37.0 (178034)

今回の記事のゴール

この記事では、PostgreSQLのPL/pgSQLで動的にSQLを組み立てられるようになることを目指す。

動的SQLとは

動的SQLは、プログラム実行時にSQL文を文字列として組み立て、それを実行するものである。
静的SQLとは異なり、実行するSQL文が事前に確定していない場合に有効となっている。

動的SQLの活用例

以下のような場面で動的SQLが役立つ

1. テーブル名を動的に指定する

-- 静的SQL（テーブル名は固定）
SELECT * FROM todos;

-- 動的SQL（テーブル名を引数で受け取る）
EXECUTE 'SELECT * FROM ' || table_name;

2. 条件を動的に組み立てる

-- 検索条件がオプショナルな場合
CREATE OR REPLACE PROCEDURE search_todos(
  p_status BOOLEAN DEFAULT NULL,
  p_due_date DATE DEFAULT NULL
)
LANGUAGE plpgsql
AS $$
DECLARE
  sql_query TEXT := 'SELECT * FROM todos WHERE 1=1';
BEGIN
  -- 条件を動的に追加
  IF p_status IS NOT NULL THEN
    sql_query := sql_query || ' AND is_completed = ' || p_status;
  END IF;
  
  IF p_due_date IS NOT NULL THEN
    sql_query := sql_query || ' AND due_date <= ' || quote_literal(p_due_date);
  END IF;
  
  -- 動的SQLを実行
  EXECUTE sql_query;
END;
$$;

3. カラム名を動的に指定する

-- ソート順序を実行時に決定
CREATE OR REPLACE PROCEDURE get_sorted_todos(
  p_sort_column TEXT,
  p_sort_direction TEXT DEFAULT 'ASC'
)
LANGUAGE plpgsql
AS $$
DECLARE
  sql_query TEXT;
BEGIN
  sql_query := format('SELECT * FROM todos ORDER BY %I %s', 
                      p_sort_column, 
                      p_sort_direction);
  
  EXECUTE sql_query;
END;
$$;

動的SQLのメリットについて

柔軟性
実行時に条件やテーブル名を変更できる
コードの削減
動的にSQLを組み立てることにより、似たようなプロシージャを複数作る必要がないこと

動的SQLの注意点

SQLインジェクション
適切なエスケープ処理が必要となる
デバッグの難しさ
実行時にSQLが構築されるため、エラーの原因特定が難しい
※とはいえ、プロシージャ自体のデバッグがそもそも難しいのだが
パフォーマンス
実行計画のキャッシュが効きにくい場合がある

準備

元となる環境については、以下のリポジトリのDockerを使用している。

katsuobushiFPGA / postgresql-procedure-dev
https://github.com/katsuobushiFPGA/postgresql-procedure-dev

SQLを動的に組み立てるサンプル

SQLを動的に組み立てる際にも、いくつか方法があるのでそれぞれ紹介していく。

1. 文字列を結合していく

最もシンプルな方法として、文字列連結演算子||を使ってSQL文を組み立てる方法がある。

基本的な文字列結合の例

CREATE OR REPLACE PROCEDURE dynamic_select_by_status(
  p_is_completed BOOLEAN
)
LANGUAGE plpgsql
AS $$
DECLARE
  sql_query TEXT;
BEGIN
  -- SQL文を文字列として組み立てる
  sql_query := 'SELECT * FROM todos WHERE is_completed = ' || p_is_completed;
  
  -- 組み立てたSQLを実行
  RAISE NOTICE 'Executing SQL: %', sql_query;
  EXECUTE sql_query;
END;
$$;

実際にこのプロシージャをCALLしてみる。

CALL dynamic_select_by_status(true);

結果

NOTICE:  Executing SQL: SELECT * FROM todos WHERE is_completed = true
CALL

Query returned successfully in 30 msec.

条件を動的にIF文で組み立てる例

CREATE OR REPLACE PROCEDURE search_todos_dynamic(
  p_title TEXT DEFAULT NULL,
  p_is_completed BOOLEAN DEFAULT NULL,
  p_due_date DATE DEFAULT NULL
)
LANGUAGE plpgsql
AS $$
DECLARE
  sql_query TEXT := 'SELECT * FROM todos WHERE 1=1';
BEGIN
  -- タイトルの条件を追加
  IF p_title IS NOT NULL THEN
    sql_query := sql_query || ' AND title LIKE ' || quote_literal('%' || p_title || '%');
  END IF;
  
  -- 完了フラグの条件を追加
  IF p_is_completed IS NOT NULL THEN
    sql_query := sql_query || ' AND is_completed = ' || p_is_completed;
  END IF;
  
  -- 期日の条件を追加
  IF p_due_date IS NOT NULL THEN
    sql_query := sql_query || ' AND due_date <= ' || quote_literal(p_due_date);
  END IF;
  
  -- 組み立てたSQLを表示して実行
  RAISE NOTICE 'Final SQL: %', sql_query;
  EXECUTE sql_query;
END;
$$;

実行例：

-- すべての条件を指定
CALL search_todos_dynamic('report', false, '2025-12-31');
-- SELECT * FROM todos WHERE 1=1 AND title LIKE '%report%' AND is_completed = false AND due_date <= '2025-12-31'

-- 一部の条件のみ指定
CALL search_todos_dynamic(p_is_completed := false);
-- SELECT * FROM todos WHERE 1=1 AND is_completed = false

-- タイトルのみで検索
CALL search_todos_dynamic(p_title := 'meeting');
-- SELECT * FROM todos WHERE 1=1 AND title LIKE '%meeting%'

2. FORMAT関数を使った例

FORMAT関数を使うことで、より安全で読みやすいコードが書ける。
%I（識別子）と%L（リテラル）の使い分けが必要であることに注意

CREATE OR REPLACE PROCEDURE get_todos_by_column(
  p_column_name TEXT,
  p_column_value TEXT,
  p_order_by TEXT DEFAULT 'id'
)
LANGUAGE plpgsql
AS $$
DECLARE
  sql_query TEXT;
BEGIN
  -- FORMAT関数で安全にSQLを組み立てる
  -- %I: 識別子（テーブル名、カラム名）を安全にエスケープ
  -- %L: リテラル（値）を安全にエスケープ
  sql_query := format(
    'SELECT * FROM todos WHERE %I = %L ORDER BY %I',
    p_column_name,
    p_column_value,
    p_order_by
  );
  
  RAISE NOTICE 'Executing: %', sql_query;
  EXECUTE sql_query;
END;
$$;

実行例：

-- titleカラムで検索してidでソート
CALL get_todos_by_column('title', 'Buy groceries', 'id');
-- SELECT * FROM todos WHERE title = 'Buy groceries' ORDER BY id

-- descriptionカラムで検索してdue_dateでソート
CALL get_todos_by_column('description', 'Important task', 'due_date');
-- SELECT * FROM todos WHERE description = 'Important task' ORDER BY due_date

FORMAT関数の書式指定子

書式指定子	用途	説明
`%I`	識別子	テーブル名、カラム名を安全にエスケープ
`%L`	リテラル	文字列や値を安全にエスケープ（引用符付き）
`%s`	文字列	そのまま文字列として挿入（エスケープなし）

位置指定引数を使った例

FORMAT関数では%1$I、%2$Lのように位置を指定して引数を参照できる。
同じ引数を複数回使用する場合や、引数の順序を変更したい場合に便利である。

CREATE OR REPLACE PROCEDURE update_todo_with_position(
  p_id INTEGER,
  p_column_name TEXT,
  p_new_value TEXT
)
LANGUAGE plpgsql
AS $$
DECLARE
  sql_query TEXT;
BEGIN
  -- 位置指定引数を使ってSQLを組み立てる
  -- %1$I: 1番目の引数を識別子として使用
  -- %2$L: 2番目の引数をリテラルとして使用
  -- %1$I: 再度1番目の引数を使用（同じカラム名を2箇所で使える）
  sql_query := format(
    'UPDATE todos SET %1$I = %2$L WHERE id = %3$L',
    p_column_name,  -- %1$I
    p_new_value,    -- %2$L
    p_id            -- %3$L
  );
  
  RAISE NOTICE 'Executing: %', sql_query;
  EXECUTE sql_query;
END;
$$;

実行例

-- titleカラムを更新
CALL update_todo_with_position(1, 'title', 'Updated Title');
-- UPDATE todos SET "title" = 'Updated Title' WHERE id = '1'

-- descriptionカラムを更新
CALL update_todo_with_position(2, 'description', 'New description');
-- UPDATE todos SET "description" = 'New description' WHERE id = '2'

より複雑なストアドの例

CREATE OR REPLACE PROCEDURE complex_query_with_positions(
  p_table_name TEXT,
  p_column1 TEXT,
  p_column2 TEXT,
  p_value TEXT
)
LANGUAGE plpgsql
AS $$
DECLARE
  sql_query TEXT;
BEGIN
  -- 同じ引数を複数回使用する例
  sql_query := format(
    'SELECT %2$I, %3$I FROM %1$I WHERE %2$I = %4$L OR %3$I = %4$L ORDER BY %2$I',
    p_table_name,  -- %1$I (テーブル名)
    p_column1,     -- %2$I (カラム1 - 3回使用)
    p_column2,     -- %3$I (カラム2 - 2回使用)
    p_value        -- %4$L (値 - 2回使用)
  );
  
  RAISE NOTICE 'Executing: %', sql_query;
  EXECUTE sql_query;
END;
$$;

実行例

CALL complex_query_with_positions('todos', 'title', 'description', 'Important');
-- SELECT "title", "description" FROM "todos" 
-- WHERE "title" = 'Important' OR "description" = 'Important' 
-- ORDER BY "title"

位置指定引数のメリット

位置指定引数は非常に便利で以下のメリットがある。

引数の再利用
同じ引数を複数回使用できる
可読性向上
引数の対応関係が明確になる
保守性向上
引数の順序変更が容易

3. FORMAT関数 + ドル記号で引用符付けされた文字列定数

FORMAT関数と、ドル記号で引用符付けされた文字列定数を組み合わせることで、複雑なSQLも読みやすく記述できる。

4.1.2.4. ドル記号で引用符付けされた文字列定数 | PostgreSQL 17.6文書
https://www.postgresql.jp/document/17/html/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING

CREATE OR REPLACE PROCEDURE complex_search_todos(
  p_table_name TEXT,
  p_min_id INTEGER DEFAULT 1,
  p_max_id INTEGER DEFAULT 100,
  p_status BOOLEAN DEFAULT NULL
)
LANGUAGE plpgsql
AS $$
DECLARE
  sql_query TEXT;
  status_condition TEXT := '';
BEGIN
  -- ステータス条件を動的に追加
  IF p_status IS NOT NULL THEN
    status_condition := format('AND is_completed = %L', p_status);
  END IF;
  
  -- ドル記号引用符を使って複雑なSQLを記述
  sql_query := format($sql$
    SELECT 
      id,
      title,
      description,
      due_date,
      is_completed
    FROM %I
    WHERE id BETWEEN %L AND %L
      %s
    ORDER BY id
  $sql$, 
    p_table_name,
    p_min_id,
    p_max_id,
    status_condition
  );
  
  RAISE NOTICE 'Executing complex query: %', sql_query;
  EXECUTE sql_query;
END;
$$;

実行例：

-- todosテーブルからID 1-50の未完了タスクを取得
CALL complex_search_todos('todos', 1, 50, false);
/*
NOTICE:  Executing complex query: 
    SELECT 
      id,
      title,
      description,
      due_date,
      is_completed
    FROM todos
    WHERE id BETWEEN '1' AND '50'
      AND is_completed = 'f'
    ORDER BY id
  
CALL

Query returned successfully in 27 msec.
*/

-- todosテーブルからID 10-20のすべてのタスクを取得
CALL complex_search_todos('todos', 10, 20);
/*
NOTICE:  Executing complex query: 
    SELECT 
      id,
      title,
      description,
      due_date,
      is_completed
    FROM todos
    WHERE id BETWEEN '10' AND '20'
      
    ORDER BY id
  
CALL

Query returned successfully in 29 msec.
*/

ドル記号引用符のメリット

エスケープ不要
内部の単一引用符をエスケープする必要がない
可読性向上
複数行のSQLが読みやすくなる
保守性向上
SQLの構造が明確になる

比較例

-- 通常の引用符（エスケープが必要）
sql_query := 'SELECT * FROM todos WHERE title = ''Important''';

-- ドル記号引用符（エスケープ不要）
sql_query := $sql$SELECT * FROM todos WHERE title = 'Important'$sql$;

個人的な結論

可読性や保守性を考えると、
FORMAT + ドル記号で引用符付けされた文字列定数 + 位置指定引数 を活用するのがよさそう！

例えば以下の例を挙げると、 $sql$ で括っている箇所については既存のSQLをそのままに、変更したい部分だけを %1$I などの位置指定引数に変更してあげればよかったりする。

※ IF で条件を付け加えたりする場合は、やはり文字列結合は必要になる。

DO $$
DECLARE
  sql_query TEXT;
  v_total_count INTEGER;
  v_completed_count INTEGER;
BEGIN
  -- 全件数を取得
  sql_query := 'SELECT COUNT(*) FROM todos';
  EXECUTE sql_query INTO v_total_count;
  
  -- 完了済み件数を取得
  sql_query := FORMAT($sql$
    SELECT 
      COUNT(*)
    FROM %1$I
    WHERE 
      is_completed = %2$L
  $sql$
    , 'todos'
    , 'true'
  );
  EXECUTE sql_query INTO v_completed_count;
  
  -- 結果を表示
  RAISE NOTICE '=== TODO Statistics ===';
  RAISE NOTICE 'Total: %', v_total_count;
  RAISE NOTICE 'Completed: %', v_completed_count;
  RAISE NOTICE 'query: %', sql_query;
END $$;  

参考

4.1.2.4. ドル記号で引用符付けされた文字列定数 | PostgreSQL 17.6文書
https://www.postgresql.jp/document/17/html/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING
4.2.2. 位置パラメータ | PostgreSQL 17.6文書
https://www.postgresql.jp/document/17/html/sql-expressions.html#SQL-EXPRESSIONS-PARAMETERS-POSITIONAL
9.4.1. format | PostgreSQL 17.6文書
https://www.postgresql.jp/document/17/html/functions-string.html#FUNCTIONS-STRING-FORMAT

おわりに

できるだけ綺麗に書きたかったので、動的SQLの組み立てについて備忘録として記載した。
自分の中で回答ができたので満足したので良し。