将PostgreSQL的PL / pgSQL输出保存到CSV文件

Question

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

我正在将PostgreSQL 8.4与pgAdmin III和PSQL插件一起使用，从中运行查询。

Answer 1

您要在服务器上还是在客户端上生成结果文件？

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

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

这种方法完全在远程服务器上运行 -无法写入本地PC。它也需要以Postgres的“超级用户”（通常称为“
root”）运行，因为Postgres不能阻止它使用该计算机的本地文件系统执行令人讨厌的事情。

这实际上并不意味着您必须以超级用户身份进行连接（自动连接将带来另一种类型的安全风险），因为您可以使用该SECURITY DEFINER选项CREATE FUNCTION来使功能 像超级用户一样运行 。

关键部分是您的函数可以执行其他检查，而不仅仅是绕过安全性-
因此您可以编写一个函数来导出所需的确切数据，或者编写可以接受各种选项的东西，只要它们可以符合严格的白名单。您需要检查两件事：

1. 应该允许用户在磁盘上读取/写入哪些 文件 ？例如，这可能是一个特定的目录，并且文件名可能必须具有合适的前缀或扩展名。
2. 用户应该能够在数据库中读取/写入哪些 表 ？这通常由GRANT数据库中的s定义，但是该函数现在以超级用户身份运行，因此通常可以“超出范围”的表将可以完全访问。您可能不想让某人调用您的函数并在您的“表格”末尾添加行。

我写了一篇有关此方法的博客文章，其中包括一些导出（或导入）满足严格条件的文件和表的函数的示例。

客户端

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

该COPY TO STDOUT命令的基本语法是命令，而pgAdmin之类的图形工具将在一个漂亮的对话框中为您包装它。

在 psql 命令行客户端有一个特殊的“元命令”之称 \copy ，这需要所有相同的选项，“真正的”
COPY，但在运行客户端中：

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

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

从文档：

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

您的应用程序编程语言 可能 还支持推送或获取数据，但是由于无法连接输入/输出流，因此通常不能在标准SQL语句中使用COPY FROM STDIN/
TO STDOUT。PHP的PostgreSQL处理程序（ 不是
PDO）包括非常基本的功能pg_copy_from和可与pg_copy_toPHP数组进行复制的功能，这些功能对于大型数据集可能无效。