對於 PostgreSQL 高階使用者來說，自動執行重複步驟變得越來越有必要，而 gexec 可以提供幫助。本文將描述如何使用 || 運算子和 gexec 命令來避免工作中不必要的重複工作。
psql是一款非常實用的工具，具有很多有用的功能。一個常見的模式是需要用不同的引數執行相同的命令。通常情況下，使用者只需一遍又一遍地重寫命令，或者有時會選擇使用文字編輯器編寫一次命令，然後複製、貼上和編輯以適應不同的引數。有時，自動執行完成這些步驟很有用，不僅可以節省時間，還能避免因錯別字或複製貼上造成的錯誤。
PostgreSQL 可以獲取查詢結果並新增文字，從而建立以這些結果為引數的命令。為此，我們可以使用 || 運算子將文字新增到任何查詢結果中。
使用 || 運算子
假設一個新使用者需要訪問 schema 中的某些表，比如匹配某個字首的所有表。現在，我們可以手動完成這項工作，也可以讓資料庫自動完成這些無聊的工作。
1. 檢索名稱以 pgbench 開頭的相關表
postgres=# SELECT tablename FROM pg_tables WHERE tablename~'^pgbench'; tablename ------------------ pgbench_accounts pgbench_branches pgbench_history pgbench_tellers (4 rows)
2. 讓我們使用 || 來對命令片段進行字首和追加，從而建立一個以 tablename 為引數的有效命令。
postgres=# SELECT 'GRANT SELECT ON TABLE ' || tablename || ' TO someuser;' FROM pg_tables WHERE tablename~'^pgbench'; ?column? ----------------------------------------------------- GRANT SELECT ON TABLE pgbench_accounts TO someuser; GRANT SELECT ON TABLE pgbench_branches TO someuser; GRANT SELECT ON TABLE pgbench_history TO someuser; GRANT SELECT ON TABLE pgbench_tellers TO someuser; (4 rows)
請注意，字串以額外的空格結束或開始，因為 tablename 本身不包含分隔引數所需的空格。此外，還新增了分號;，以便直接執行這些命令。
請注意，雖然使用 || 來連線字串很方便，但這種做法並不可取，因為它很容易受到 SQL 注入攻擊：
切勿在查詢時盲目連線表名。請使用 quote_ident()，或使用 %I 的 format()。 這些方法會根據需要應用正確的轉義
要達到同樣的效果，更安全的方法是類似下面這樣：
postgres=# SELECT format('GRANT SELECT ON TABLE %I TO someuser;', tablename) FROM pg_tables WHERE tablename~'^pgbench';
format
-----------------------------------------------------
GRANT SELECT ON TABLE pgbench_accounts TO someuser;
GRANT SELECT ON TABLE pgbench_branches TO someuser;
GRANT SELECT ON TABLE pgbench_history TO someuser;
GRANT SELECT ON TABLE pgbench_tellers TO someuser;
(4 rows)
現在，這些命令可以直接複製貼上到提示符中。
我甚至見過有人把這些命令列儲存到檔案中，然後讓 psql 執行檔案中的所有命令。
不過值得慶幸的是，還有一種更簡單的方法。就是使用 gexec。
在 psql 中，有許多快捷方式和助手可以快速收集有關於資料庫、schema、表、許可權等的資訊。psql shell 允許在輸入和輸出緩衝區工作，可以與 gexec 一起使用，讓 psql 從輸出緩衝區執行每條命令。
呼叫 gexec
重用上面的命令生成需要的指令，並使用 gexec 執行上面命令的輸出結果中的每一行指令。
postgres=# SELECT 'GRANT SELECT ON TABLE ' || tablename || ' TO someuser;' FROM pg_tables WHERE tablename~'^pgbench'; ?column? ----------------------------------------------------- GRANT SELECT ON TABLE pgbench_accounts TO someuser; GRANT SELECT ON TABLE pgbench_branches TO someuser; GRANT SELECT ON TABLE pgbench_history TO someuser; GRANT SELECT ON TABLE pgbench_tellers TO someuser; (4 rows) postgres=# gexec GRANT GRANT GRANT GRANT
gexec 和 cross join 結合使用
如果要執行涉及更多引數的操作，可以新增更多 || 來在查詢結果周圍新增更多命令片段。
假設還需要授予插入、更新和刪除這些表的許可權。
透過簡單的 cross join ，我們就可以為每個表名提供所需的操作（使用 VALUES 建構函式作為關係構造）。
postgres=# SELECT action, tablename FROM pg_tables CROSS JOIN (VALUES ('INSERT'),('UPDATE'),('DELETE')) AS t(action) WHERE tablename~'^pgbench';
action | tablename
--------+------------------
INSERT | pgbench_accounts
UPDATE | pgbench_accounts
DELETE | pgbench_accounts
INSERT | pgbench_branches
UPDATE | pgbench_branches
DELETE | pgbench_branches
INSERT | pgbench_history
UPDATE | pgbench_history
DELETE | pgbench_history
INSERT | pgbench_tellers
UPDATE | pgbench_tellers
DELETE | pgbench_tellers
(12 rows)
請注意，我們使用 AS t(action) 將操作列名明確分配給了使用 VALUES 生成的表。
postgres=# SELECT 'GRANT ' || action || ' ON TABLE ' || tablename || ' TO someuser;' FROM pg_tables CROSS JOIN (VALUES ('INSERT'),('UPDATE'),('DELETE')) AS t(action) WHERE tablename~'^pgbench';
?column?
-----------------------------------------------------
GRANT INSERT ON TABLE pgbench_accounts TO someuser;
GRANT UPDATE ON TABLE pgbench_accounts TO someuser;
GRANT DELETE ON TABLE pgbench_accounts TO someuser;
GRANT INSERT ON TABLE pgbench_branches TO someuser;
GRANT UPDATE ON TABLE pgbench_branches TO someuser;
GRANT DELETE ON TABLE pgbench_branches TO someuser;
GRANT INSERT ON TABLE pgbench_history TO someuser;
GRANT UPDATE ON TABLE pgbench_history TO someuser;
GRANT DELETE ON TABLE pgbench_history TO someuser;
GRANT INSERT ON TABLE pgbench_tellers TO someuser;
GRANT UPDATE ON TABLE pgbench_tellers TO someuser;
GRANT DELETE ON TABLE pgbench_tellers TO someuser;
(12 rows)
然後，透過 gexec 執行上面命令的輸出結果：
postgres=# gexec GRANT GRANT GRANT GRANT GRANT GRANT GRANT GRANT GRANT GRANT GRANT GRANT
新增引號
根據具體情況，可能需要在輸出中新增額外的引號，例如當表名包含大寫字母或空格時。
postgres=# SELECT 'GRANT SELECT ON TABLE '' || tablename || '' TO someuser;' FROM pg_tables WHERE schemaname='public'; ?column? ----------------------------------------------------- GRANT SELECT ON TABLE 'with spaces' TO someuser; GRANT SELECT ON TABLE 'Capitalization' TO someuser; GRANT SELECT ON TABLE 'capitalization' TO someuser; (3 rows) postgres=# gexec GRANT GRANT GRANT