postgresql - 如何运行一系列 SQL 查询并保存结果？

Question

在其他统计程序中，可以创建一个日志文件来显示作为命令结果发出的输出。是否可以在 SQL 中做类似的事情？

特别是，我想要一个包含许多查询的 .sql 文件，然后将每个结果输出到一个文本文件。

我正在使用 PostgreSQL 和 Navicat。

Answer 1

plpgsql函数和COPY

一种方法是将 SQL 脚本放入 plpgsql function 中，您可以在其中将各个返回值写入文件 COPY并根据需要根据中间结果编制报告。

这会产生额外的效果，可能是理想的，也可能不是理想的。比如，您可以授予或撤销对任意角色的整个功能的权限。了解 SECURITY DEFINER在手册中。并且在您保存函数时将验证语法 - 但是，只是表面上的(有计划在未来改变它)。更多详情在此 answer on dba.SE .

基本示例:

CREATE OR REPLACE FUNCTION func()
  RETURNS void AS
$BODY$
BEGIN

COPY (SELECT * FROM tbl WHERE foo) TO '/path/to/my/file/tbl.csv';

COPY (SELECT * FROM tbl2 WHERE NOT bar) TO '/path/to/my/file/tbl2.csv';

END;
$BODY$
  LANGUAGE plpgsql;

当然，您需要在数据库和文件系统中拥有必要的权限。
从 shell 中调用它:

psql mydb -c 'SELECT func();'

---

psql在元命令和SQL之间切换

#!/bin/sh

BASEDIR='/var/lib/postgresql/outfiles/'

echo "
\\o $OUTDIR/file1.txt  \\\\\\\\ SELECT * FROM tbl1;
\\o $OUTDIR/file2.txt  \\\\\\\\ SELECT * FROM tbl2;
\\o $OUTDIR/file3.txt  \\\\\\\\ SELECT * FROM tbl3;" | psql event -p 5432 -t -A

没错，8 个反斜杠。来自被解释两次的双反斜杠的结果，因此您必须将它们加倍两次。

我引用 the manual关于元命令 \o:

Saves future query results to the file filename or ...

和\\:

command must be either a command string that is completely parsable by the server (i.e., it contains no psql-specific features), or a single backslash command. Thus you cannot mix SQL and psql meta-commands with this option. To achieve that, you could pipe the string into psql, like this: echo '\x \\ SELECT * FROM foo;' | psql. (\\ is the separator meta-command.)