psql 中使用 gexec
2024-05-24 09:48 abce 阅读(14) 评论(0) 编辑 收藏 举报对于 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
