将 PL/pgSQL 输出从 PostgreSQL 保存到 CSV 文件 [英] Save PL/pgSQL output from PostgreSQL to a CSV file
问题描述
将 PL/pgSQL 输出从 PostgreSQL 数据库保存到 CSV 文件的最简单方法是什么?
What is the easiest way to save PL/pgSQL output from a PostgreSQL database to a CSV file?
我将 PostgreSQL 8.4 与 pgAdmin III 和 PSQL 插件一起使用，我从中运行查询.
I'm using PostgreSQL 8.4 with pgAdmin III and PSQL plugin where I run queries from.
推荐答案
你想要结果文件在服务器上，还是在客户端?
Do you want the resulting file on the server, or on the client?
如果你想要一些易于重用或自动化的东西，你可以使用 Postgresql 的内置 COPY 命令.例如
If you want something easy to re-use or automate, you can use Postgresql's built in COPY command. e.g.
Copy (Select * From foo) To '/tmp/test.csv' With CSV DELIMITER ',' HEADER;
这种方法完全在远程服务器上运行 - 它无法写入您的本地 PC.它还需要以 Postgres超级用户"(通常称为root")身份运行，因为 Postgres 无法阻止它使用该机器的本地文件系统做令人讨厌的事情.
This approach runs entirely on the remote server - it can't write to your local PC. It also needs to be run as a Postgres "superuser" (normally called "root") because Postgres can't stop it doing nasty things with that machine's local filesystem.
这实际上并不意味着您必须以超级用户的身份进行连接(自动化会带来不同类型的安全风险)，因为您可以使用 SECURITY DEFINER 选项 CREATE FUNCTION 来创建一个函数，就像您是超级用户一样运行.
That doesn't actually mean you have to be connected as a superuser (automating that would be a security risk of a different kind), because you can use the SECURITY DEFINER option to CREATE FUNCTION to make a function which runs as though you were a superuser.
关键部分是您的函数在那里执行额外的检查，而不仅仅是绕过安全性 - 因此您可以编写一个函数来导出您需要的确切数据，或者您可以编写一些可以接受各种选项的函数只要他们符合严格的白名单.您需要检查两件事:
The crucial part is that your function is there to perform additional checks, not just by-pass the security - so you could write a function which exports the exact data you need, or you could write something which can accept various options as long as they meet a strict whitelist. You need to check two things:
- 应该允许用户在磁盘上读/写哪些文件?例如，这可能是一个特定的目录，并且文件名可能必须具有合适的前缀或扩展名.
- 用户应该能够在数据库中读/写哪些表?这通常由数据库中的
GRANT定义，但该函数现在以超级用户身份运行，因此通常越界"的表将完全可以访问.您可能不想让某人调用您的函数并在用户"表的末尾添加行......
- Which files should the user be allowed to read/write on disk? This might be a particular directory, for instance, and the filename might have to have a suitable prefix or extension.
- Which tables should the user be able to read/write in the database? This would normally be defined by
GRANTs in the database, but the function is now running as a superuser, so tables which would normally be "out of bounds" will be fully accessible. You probably don’t want to let someone invoke your function and add rows on the end of your "users" table…
我写了一篇扩展这种方法的博文，包括一些例子导出(或导入)满足严格条件的文件和表格的函数.
I've written a blog post expanding on this approach, including some examples of functions that export (or import) files and tables meeting strict conditions.
另一种方法是在客户端进行文件处理，即在您的应用程序或脚本中.Postgres 服务器不需要知道您要复制到哪个文件，它只是吐出数据，然后客户端将其放在某处.
The other approach is to do the file handling on the client side, i.e. in your application or script. The Postgres server doesn't need to know what file you're copying to, it just spits out the data and the client puts it somewhere.
此操作的底层语法是 COPY TO STDOUT 命令，并且像 pgAdmin 这样的图形工具会在一个漂亮的对话框中为您包装它.
The underlying syntax for this is the COPY TO STDOUT command, and graphical tools like pgAdmin will wrap it for you in a nice dialog.
psql 命令行客户端 有一个名为 copy 的特殊元命令"，它采用与真实"COPY 相同的所有选项，但在客户端内部运行:
The psql command-line client has a special "meta-command" called copy, which takes all the same options as the "real" COPY, but is run inside the client:
copy (Select * From foo) To '/tmp/test.csv' With CSV
注意没有终止;，因为元命令以换行符终止，不像SQL命令.
Note that there is no terminating ;, because meta-commands are terminated by newline, unlike SQL commands.
来自文档:
不要将 COPY 与 psql 指令 copy 混淆.copy 调用 COPY FROM STDIN 或 COPY TO STDOUT，然后在 psql 客户端可访问的文件中获取/存储数据.因此，当使用copy 时，文件可访问性和访问权限取决于客户端而不是服务器.
Do not confuse COPY with the psql instruction copy. copy invokes COPY FROM STDIN or COPY TO STDOUT, and then fetches/stores the data in a file accessible to the psql client. Thus, file accessibility and access rights depend on the client rather than the server when copy is used.
您的应用程序编程语言可能也支持推送或获取数据，但您通常不能使用COPY FROM STDIN/TO STDOUT在标准 SQL 语句中，因为无法连接输入/输出流.PHP 的 PostgreSQL 处理程序(不是 PDO)包括非常基本的pg_copy_from 和 pg_copy_to 向/从 PHP 数组复制的函数，这对于大型数据集可能效率不高.
Your application programming language may also have support for pushing or fetching the data, but you cannot generally use COPY FROM STDIN/TO STDOUT within a standard SQL statement, because there is no way of connecting the input/output stream. PHP's PostgreSQL handler (not PDO) includes very basic pg_copy_from and pg_copy_to functions which copy to/from a PHP array, which may not be efficient for large data sets.
这篇关于将 PL/pgSQL 输出从 PostgreSQL 保存到 CSV 文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
