将PostgreSQL的PL / pgSQL输出保存到CSV文件
技术问答
116 人阅读
|
0 人回复
|
2023-09-13
|
将PL / pgSQL输出从PostgreSQL保存数据库CSV文件最简单的方法是什么?# ~' s1 ~' a" K: ?7 B$ K2 M
我正在将PostgreSQL 8.4与pgAdmin III和PSQL一起使用插件,运行查询。
2 t. F( \1 R/ S8 ^) ] `" M
9 k, Q {; _, | 解决方案:
" d/ t: ?$ W$ T i 您是想在服务器上还是在客户端上生成结果文件?
, I. K2 G- s- c/ t/ I服务器端如果你想要一些容易重用或自动化的东西,可以使用Postgresql的内置COPY命令。例如
0 x' ~! V# o2 p9 h" D# ECopy (Select * From foo) To '/tmp/test.csv' With CSV DELIMITER ',' HEADER;这种方法完全在远程服务器上运行 -本地写不出来PC。它也需要以Postgres超级用户(通常称为 B+ Q( R6 B/ R- ~) A8 w0 x2 ^
root因为Postgres它不能阻止它使用计算机的本地文件系统来执行令人讨厌的事情。
; L: L+ z: \/ u9 P+ `这并不意味着你必须作为超级用户连接(自动连接会带来另一种安全风险),因为你可以使用它SECURITYDEFINER选项CREATEFUNCTION来使功能 像超级用户一样运行 。
: y# a9 z9 B2 d关键部分是你的函数可以进行其他检查,而不仅仅是绕过安全-" F7 x: ]6 V! Y, R
因此,只要能满足严格的白名单,您就可以编写函数导出所需的确切数据,或编写可以接受各种选项的东西。您需要检查两件事:3 M, O9 c7 R) F3 G7 l2 O
[ol]用户应该在磁盘上阅读/写入哪些 文件 ?例如,这可能是一个特定的目录,文件名必须有合适的前缀或扩展名。
" D) ^+ l* w8 o$ x$ Q用户应该能够在数据库中读取/写入哪些 表 这通常是由的GRANT数据库中的s定义,但该函数现在作为超级用户运行,因此通常可以完全访问超出范围的表。您可能不希望有人调用您的函数并在您的表格结尾添加行。[/ol]我写了一篇关于这种方法的博客文章,包括一些导出(或导入)满足严格条件的文件和表函数的示例。
; {1 G; {0 q P+ k6 e/ O- y, D. Y1 Z客户端另一种方法是 在客户端 (即在您的应用程序或脚本中) 处理文件* n. e, J/ L9 j1 V z( }1 I
。Postgres服务器不需要知道要复制哪个文件,只需要吐出数据,然后客户端就可以把它放在某个位置。* e" w5 b+ d; j, b- w' d
该COPY TO STDOUT命令的基本语法是命令,pgAdmin这种图形工具将在一个漂亮的对话框中为您包装。
' x6 H+ _& J8 W0 D在 psql 命令行客户端有一为元命令\copy ,这需要所有相同的选项,“真正的”
; M5 Z" q1 c3 S `* p4 rCOPY,但在运营客户端:7 H+ ~3 f" B, J- t, M
\copy (Select * From foo) To '/tmp/test.csv' With CSV请注意,没有终止,因为和,SQL命令不同,元命令由换行符终止。9 R" ]" O% ~( L2 X6 T
从文档:) l; P( k7 s- r( ^4 K$ Z1 n& F/ h
请勿将COPY与psql指令\ copy混淆。\ copy调用COPY FROM STDIN或COPY TO3 r z0 B5 r2 \, \" o: q, g
STDOUT,然后提取/存储数据psql客户端可访问的文件中。因此,使用\ copy文件的可访问性和访问权限取决于客户端而不是服务器。
# X+ M, o( E4 D, _您的应用程序编程语言 可能 还支持推送或获取数据,但通常不是标准的,因为它不能连接输入/输出流SQL语句中使用COPY FROM STDIN/
: l2 W! @) d2 k) [- h$ I$ NTO STDOUT。PHP的PostgreSQL处理程序( 不是
# k6 A: T* V" ]PDO)包括非常基本的功能pg_copy_from和可与pg_copy_toPHP复制数组的功能对大型数据集可能无效。 |
|
|
|
|
|