将 PL/pgSQL 输出从 PostgreSQL 保存到 CSV 文件
问题:将 PL/pgSQL 输出从 PostgreSQL 保存到 CSV 文件 将 PL/pgSQL 输出从 PostgreSQL 数据库保存到 CSV 文件的最简单方法是什么? 我正在使用带有 pgAdmin III 和 PSQL 插件的 PostgreSQL 8.4,我从中运行查询。 解答 您希望在服务器上还是在客户端上生成结果文件? 服务器端 如果你想要一些易于重用或自动化的东西,你可以使用
问题:将 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 的函数,就好像你是超级用户。
关键部分是你的函数可以执行额外的检查,而不仅仅是绕过安全性——所以你可以编写一个函数来导出你需要的确切数据,或者你可以编写一些可以接受各种选项的东西,只要它们满足严格的白名单。你需要检查两件事:
-
应该允许用户在磁盘上读/写哪些文件?例如,这可能是一个特定的目录,并且文件名可能必须具有合适的前缀或扩展名。
-
用户应该能够在数据库中读取/写入哪些表?这通常由数据库中的
GRANT
s 定义,但该函数现在以超级用户身份运行,因此通常“越界”的表将可以完全访问。您可能不想让某人调用您的函数并在“用户”表的末尾添加行......
我写了一篇博客文章,扩展了这种方法,包括一些导出(或导入)满足严格条件的文件和表的函数示例。
客户端
另一种方法是在客户端进行文件处理,即在您的应用程序或脚本中。 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 数组复制,这对于大型数据集可能效率不高。
更多推荐
所有评论(0)