EXECUTEによるネストされたプロシージャ呼び出し：SQLコードの動的実行

実践に入る前に、まず「動的SQLって何？」ってところからいこう。例えば、パラメータとして渡されたユニークな名前でテーブルを作りたいときや、プログラムの実行時に決まるテーブル名にクエリを投げたいとき。こういう場合、普通の静的SQLじゃ足りないんだよね。そこで動的実行の出番！

PL/pgSQLにはEXECUTEコマンドがあって、文字列として渡したSQLクエリを実行できるんだ。これを使えば、その場でSQLコードを組み立てて実行できるから、パラメータによってクエリを変えたりできるよ。

動的SQLが便利な理由はこんな感じ：

1. 柔軟性：入力データに応じてクエリを動的に組み立てられる。例えば、事前に名前が分からないテーブルやカラムに対して操作したいときとか。
2. 自動化：ユニークな名前でテーブルやインデックスを作るときに便利。
3. 汎用性：データ構造が違っても、プロシージャを書き直さずに使い回せる。

リアルな例：分析システムを作ってて、新しいクライアントごとにデータ保存用のテーブルを作りたいとき。こういうのもEXECUTEで自動化できるよ。

EXECUTEの構文

EXECUTEで動的SQLを使うときは、こんな感じになる：

EXECUTE 'SQL文字列';

シンプルなクエリの例：

DO $$
BEGIN
  EXECUTE 'CREATE TABLE test_table (id SERIAL PRIMARY KEY, name TEXT)';
END $$;

このコードブロックはtest_tableってテーブルを作るよ。簡単だけど、もっと複雑なパターンも見てみよう。

EXECUTEの使い方例

1. 動的な名前でテーブルを作る

例えば、今日の日付によってテーブル名を変えて作りたいときはこう：

DO $$
DECLARE
  table_name TEXT;
BEGIN
  -- テーブル名を生成
  table_name := 'report_' || to_char(CURRENT_DATE, 'YYYYMMDD');

  -- 動的な名前でテーブルを作成
  EXECUTE 'CREATE TABLE ' || table_name || ' (id SERIAL PRIMARY KEY, data TEXT)';

  -- チェック用のメッセージを出す
  RAISE NOTICE 'テーブル % が無事作成されたよ', table_name;
END $$;

ここでは、テーブル名を今日の日付から作って、そのSQL文字列をEXECUTEに渡してる。

2. 動的パラメータでクエリを実行する

例えば、テーブル名をパラメータとして受け取って、そのテーブルからデータを取り出したいとき。こんな関数を作れるよ：

CREATE OR REPLACE FUNCTION get_data_from_table(table_name TEXT)
RETURNS TABLE(id INTEGER, name TEXT) AS $$
BEGIN
  RETURN QUERY EXECUTE
    'SELECT id, name FROM ' || table_name || ' WHERE id < 10';
END $$ LANGUAGE plpgsql;

関数の呼び出し：

SELECT * FROM get_data_from_table('employees');

こういうやり方は、動的なレポートシステムみたいな汎用ツールを作るのにピッタリ！

動的SQLの問題点と制限

動的SQLはめっちゃ自由度高いけど、自由には責任がつきもの。こんな落とし穴があるよ：

1. SQLインジェクション：パラメータをそのままクエリに突っ込むと、悪意ある人に好き勝手SQLを実行される危険がある。

   脆弱なコードの例：

   EXECUTE 'SELECT * FROM users WHERE name = ''' || user_input || '''';
   

   もしuser_inputが'; DROP TABLE users; --みたいな文字列だったら、usersテーブルが消されちゃう。

2. デバッグが難しい：動的なコードは実行時にクエリが組み立てられるから、分析やデバッグがややこしい。

3. パフォーマンス低下：動的クエリはPostgreSQLの実行プランキャッシュが効かなくなるから、パフォーマンスが落ちることもある。

SQLインジェクションから身を守るには

SQLインジェクション攻撃を防ぐには、文字列を単純に連結するんじゃなくて、動的クエリでもパラメータ化を使おう。PL/pgSQLでは、文字列パラメータにはquote_literal()、テーブル名やカラム名みたいな識別子にはquote_ident()を使うのが鉄則！

安全なコードの例：

DO $$
DECLARE
  table_name TEXT;
  user_input TEXT := 'John';
BEGIN
  table_name := 'employees';

  EXECUTE 'SELECT * FROM ' || quote_ident(table_name) ||
          ' WHERE name = ' || quote_literal(user_input);
END $$;

実装例：テーブルの動的更新

こんな感じで、パラメータで渡されたテーブル名のデータを更新するプロシージャも作れる：

CREATE OR REPLACE FUNCTION update_table_data(table_name TEXT, id_value INT, new_data TEXT)
RETURNS VOID AS $$
BEGIN
  EXECUTE 'UPDATE ' || quote_ident(table_name) ||
          ' SET data = ' || quote_literal(new_data) ||
          ' WHERE id = ' || id_value;
END $$ LANGUAGE plpgsql;

関数の呼び出し：

SELECT update_table_data('test_table', 1, '更新された値');

例：クライアント用レポートの作成

例えば、クライアントごとに注文を管理してて、それぞれのレポート用テーブルを自動で作りたいとき。

CREATE OR REPLACE FUNCTION create_client_report(client_id INT)
RETURNS VOID AS $$
DECLARE
  table_name TEXT;
BEGIN
  -- レポートテーブル名を作る
  table_name := 'client_report_' || client_id;

  -- レポート用テーブルを作成
  EXECUTE 'CREATE TABLE ' || quote_ident(table_name) || ' (order_id INT, amount NUMERIC)';

  -- データをテーブルに入れる
  EXECUTE 'INSERT INTO ' || quote_ident(table_name) ||
          ' SELECT order_id, amount FROM orders WHERE client_id = ' || client_id;

  RAISE NOTICE 'クライアント % のレポートが作成されたよ: テーブル %', client_id, table_name;
END $$ LANGUAGE plpgsql;

EXECUTEを使った動的SQLは、PL/pgSQLで自動化や柔軟性を実現する強力な武器！でも、SQLインジェクションのリスクもあるから、使うときは注意してね。クエリを安全にしたいなら、quote_ident()とquote_literal()を必ず使おう。

次の講義では、データのバリデーション、レコードの更新、操作のログ記録などを含む複雑なプロシージャの作り方を深掘りするよ。動的クエリがこういうタスクの基礎になるから、しっかり準備しておいてね！