问题:将 PL/pgSQL 输出从 PostgreSQL 保存到 CSV 文件

将 PL/pgSQL 输出从 PostgreSQL 数据库保存到 CSV 文件的最简单方法是什么?

我正在使用带有 pgAdmin III 和 PSQL 插件的 PostgreSQL 8.4,我从中运行查询。

解答

您希望在服务器上还是在客户端上生成结果文件?

服务器端

如果你想要一些易于重用或自动化的东西,你可以使用 Postgresql 内置的COPY命令。例如

Copy (Select * From foo) To '/tmp/test.csv' With CSV DELIMITER ',' HEADER;

此方法完全在远程服务器上运行 - 它无法写入您的本地 PC。它还需要作为 Postgres“超级用户”(通常称为“root”)运行,因为 Postgres 无法阻止它对该机器的本地文件系统进行令人讨厌的事情。

这实际上并不意味着您必须以超级用户身份进行连接(自动化将是一种不同类型的安全风险),因为您可以使用CREATE FUNCTION的SECURITY DEFINER选项来创建一个 runs 的函数,就好像你是超级用户。

关键部分是你的函数可以执行额外的检查,而不仅仅是绕过安全性——所以你可以编写一个函数来导出你需要的确切数据,或者你可以编写一些可以接受各种选项的东西,只要它们满足严格的白名单。你需要检查两件事:

1. 应该允许用户在磁盘上读/写哪些文件?例如,这可能是一个特定的目录,并且文件名可能必须具有合适的前缀或扩展名。

2. 用户应该能够在数据库中读取/写入哪些表?这通常由数据库中的GRANTs 定义,但该函数现在以超级用户身份运行,因此通常“越界”的表将可以完全访问。您可能不想让某人调用您的函数并在“用户”表的末尾添加行......

我写了一篇博客文章,扩展了这种方法,包括一些导出(或导入)满足严格条件的文件和表的函数示例。

客户端

另一种方法是在客户端进行文件处理,即在您的应用程序或脚本中。 Postgres 服务器不需要知道您要复制到哪个文件,它只是吐出数据,然后客户端将其放在某个位置。

其底层语法是COPY TO STDOUT命令,而 pgAdmin 等图形工具会在一个漂亮的对话框中为您包装它。

psql命令行客户端 有一个特殊的“元命令”,称为 \copy,它采用与“真实”COPY相同的选项,但在客户端内部运行:

\copy (Select * From foo) To '/tmp/test.csv' With CSV DELIMITER ',' HEADER

请注意,没有终止;,因为元命令由换行符终止,这与 SQL 命令不同。

从文档:

不要将 COPY 与 psql 指令 \copy 混淆。 \copy 调用 COPY FROM STDIN 或 COPY TO STDOUT,然后在 psql 客户端可访问的文件中获取/存储数据。因此,当使用 \copy 时,文件可访问性和访问权限取决于客户端而不是服务器。

您的应用程序编程语言_可能_也支持推送或获取数据,但您通常不能在标准 SQL 语句中使用COPY FROM STDIN/TO STDOUT,因为无法连接输入/输出流。 PHP 的 PostgreSQL 处理程序 (not PDO) 包括非常基本的pg_copy_from和pg_copy_to函数,它们复制到 PHP 数组/从 PHP 数组复制,这对于大型数据集可能效率不高。