psql 中使用 gexec

 

对于 PostgreSQL 高级用户来说，自动执行重复步骤变得越来越有必要，而 gexec 可以提供帮助。本文将描述如何使用 || 操作符和 gexec 命令来避免工作中不必要的重复工作。

 

psql是一款非常实用的工具，具有很多有用的功能。一个常见的模式是需要用不同的参数运行相同的命令。通常情况下，用户只需一遍又一遍地重写命令，或者有时会选择使用文本编辑器编写一次命令，然后复制、粘贴和编辑以适应不同的参数。有时，自动执行完成这些步骤很有用，不仅可以节省时间，还能避免因错别字或复制粘贴造成的错误。

 

PostgreSQL 可以获取查询结果并添加文本，从而创建以这些结果为参数的命令。为此，我们可以使用 || 操作符将文本添加到任何查询结果中。

使用 || 操作符

假设一个新用户需要访问 schema 中的某些表，比如匹配某个前缀的所有表。现在，我们可以手动完成这项工作，也可以让数据库自动完成这些无聊的工作。

 

1. 检索名称以 pgbench 开头的相关表

?

1 2 3 4 5 6 7 8

postgres=# SELECT tablename FROM pg_tables WHERE tablename~'^pgbench'; tablename ------------------ pgbench_accounts pgbench_branches pgbench_history pgbench_tellers (4 rows)

2. 让我们使用 || 来对命令片段进行前缀和追加，从而创建一个以 tablename 为参数的有效命令。

?

1 2 3 4 5 6 7 8

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 注入攻击：

?

1 2	切勿在查询时盲目连接表名。请使用 quote_ident()，或使用 %I 的 format()。 这些方法会根据需要应用正确的转义

要达到同样的效果，更安全的方法是类似下面这样：

?

1 2 3 4 5 6 7 8

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 执行上面命令的输出结果中的每一行指令。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

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 构造函数作为关系构造）。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

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 生成的表。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

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 执行上面命令的输出结果：

?

1 2 3 4 5 6 7 8 9 10 11 12 13

postgres=# gexec GRANT GRANT GRANT GRANT GRANT GRANT GRANT GRANT GRANT GRANT GRANT GRANT

添加引号

根据具体情况，可能需要在输出中添加额外的引号，例如当表名包含大写字母或空格时。

?

1 2 3 4 5 6 7 8 9 10 11 12

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