将PostgreSQL数据库的PL/pgSQL输出保存到CSV文件的最简单方法是什么?
我正在使用PostgreSQL 8.4和pgAdmin III以及PSQL插件来运行查询.
将PostgreSQL数据库的PL/pgSQL输出保存到CSV文件的最简单方法是什么?
我正在使用PostgreSQL 8.4和pgAdmin III以及PSQL插件来运行查询.
您希望生成的文件在服务器上还是在客户端上?
如果您想要一些易于重用或自动化的东西,可以使用Postgresql的内置COPY命令.例如
Copy (Select * From foo) To '/tmp/test.csv' With CSV DELIMITER ',' HEADER;
This approach runs entirely on the remote server-它无法写入本地PC.它还需要作为Postgres"超级用户"(通常称为"root")运行,因为Postgres无法阻止它在该机器的本地文件系统中做一些令人讨厌的事情.
这实际上并不意味着你必须以超级用户的身份连接(自动化这将是另一种安全风险),因为你可以使用the SECURITY DEFINER
option to CREATE FUNCTION
来创建一个runs as though you were a superuser的函数.
关键的一点是,你的函数可以执行额外的判断,而不仅仅是绕过安全性——因此你可以编写一个函数来导出你需要的确切数据,或者你可以编写一个可以接受各种选项的东西,只要它们符合严格的白名单.你需要判断两件事:
GRANT
来定义,但该函数现在以超级用户的身份运行,因此通常"超出范围"的表将完全可访问.您可能不想让别人调用您的函数,并在"users"表的末尾添加行…我已经写了a blog post expanding on this approach个,包括一些导出(或导入)满足严格条件的文件和表的函数示例.
另一种方法是do the file handling on the client side,即在应用程序或脚本中.Postgres服务器不需要知道您要复制到哪个文件,它只需吐出数据,然后客户端将其放在某个地方.
它的基本语法是COPY TO STDOUT
命令,像pgAdmin这样的图形工具将把它包装在一个漂亮的对话框中.
100 command-line client有一个名为101的特殊"元命令",它采用与"真实"COPY
相同的所有选项,但在客户端内部运行:
\copy (Select * From foo) To '/tmp/test.csv' With CSV DELIMITER ',' HEADER
请注意,没有终止;
,因为元命令是通过换行符终止的,与SQL命令不同.
从the docs开始:
不要将COPY与psql指令\COPY混淆.\copy调用copy FROM STDIN或copy TO STDOUT,然后将数据获取/存储在psql客户端可访问的文件中.因此,使用\copy时,文件可访问性和访问权限取决于客户端而不是服务器.
应用程序编程语言may也支持推送或获取数据,但通常不能在标准SQL语句中使用COPY FROM STDIN
/TO STDOUT
,因为无法连接输入/输出流.PHP的PostgreSQL处理程序(not PDO)包含非常基本的pg_copy_from
和pg_copy_to
个函数,它们在PHP数组中进行复制,这对于大型数据集来说可能效率不高.