将PostgreSQL中的PL / pgSQL输出保存为CSV文件 [英] Save PL/pgSQL output from PostgreSQL to a CSV file

问题描述

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

我使用PostgreSQL 8.4与pgAdmin III和PSQL插件，我在其中运行查询。

解决

服务器端

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

 复制（选择*从foo）到/tmp/test.csv使用CSV DELIMITER' 
  

此方法完全在远程服务器上运行到您的本地PC。它还需要作为Postgres超级用户（通常称为root）运行，因为Postgres不能阻止它在该机器的本地文件系统中执行恶意的操作。

这实际上并不意味着你必须作为超级用户连接（自动化将是不同类型的安全风险），因为你可以使用 的 SECURITY DEFINER 选项CREATE FUNCTION a>创建一个像您是超级用户一样运行的函数。

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

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


2. 用户应该能够读取哪些表 /写入数据库？这通常由数据库中的 GRANT 定义，但是该函数现在作为超级用户运行，因此通常是超出范围的表将完全可访问。您可能不希望让某人调用您的函数并在用户表格的末尾添加行...

ve写了关于此方法的博文，包括一些导出功能的示例（或

b p>另一种方法是在客户端执行文件处理，即在您的应用程序或脚本中。 Postgres服务器不需要知道你要复制什么文件，它只是输出数据，客户端把它放在某个地方。

这个底层语法是 COPY TO STDOUT 命令，而像pgAdmin这样的图形工具将在一个漂亮的对话框中为你包装。

psql 命令行客户端有一个特殊的元命令，名为 \copy ，它采用与真实 COPY 相同的选项，但是在客户端中运行：

  \copy（选择*从foo）到'/tmp/test.csv'使用CSV 
  ; ，因为元命令由换行符终止，与SQL命令不同。<$ c> / p> 
 
 
从文档：
 
 
不要将COPY与psql指令\copy混淆。 \copy调用COPY FROM STDIN或COPY TO STDOUT，然后将数据提取/存储在psql客户端可访问的文件中。因此，当使用\copy时，文件访问权限和访问权限取决于客户端而不是服务器。
 
 
您的应用程序编程语言 也支持推送或提取数据，但是你通常不能使用 COPY FROM STDIN  /  TO STDOUT 在标准SQL语句中，因为没有办法连接输入/输出流。 PHP的PostgreSQL处理程序（不是 PDO）包括非常基本的  pg_copy_from  和 $ b 
 $  $ b
What is the easiest way to save PL/pgSQL output from a PostgreSQL database to a CSV file? 

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?

Server side

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 ',';
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.

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:

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.



Client side

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.

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.

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
Note that there is no terminating ;, because meta-commands are terminated by newline, unlike SQL commands.

From the docs:

  
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.
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.

                        
这篇关于将PostgreSQL中的PL / pgSQL输出保存为CSV文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！