巢狀程序呼叫與 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. 用動態參數查詢資料

假設你要從一個名稱是參數傳進來的資料表撈資料。我們來寫個 function：

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;

呼叫這個 function：

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. 難除錯：動態程式碼比較難追蹤跟 debug，因為查詢是執行時才組出來的。

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 $$;

實作：動態更新資料表

這裡有個 procedure 範例，可以根據參數傳進來的資料表名稱去更新資料：

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;

呼叫這個 function：

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()。

下一堂課我們會更深入聊怎麼寫複雜的程序，像是資料驗證、更新紀錄、操作日誌等等。準備好，因為動態查詢會是這些任務的基礎喔！

1

問卷/小測驗

巢狀交易，等級 53，課堂 4

未開放

巢狀交易

巢狀交易

開啟