PostgreSQL 将大对象导出到客户端
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8787075/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL export large object to client
提问by Herr von Wurst
I have a PostgreSQL 9.1 database in which pictures are stored as large objects. Is there a way to export the files to the clients filesystem through an SQL query?
我有一个 PostgreSQL 9.1 数据库，其中图片存储为大对象。有没有办法通过 SQL 查询将文件导出到客户端文件系统？
select lo_export(data,'c:\img\test.jpg') from images where id=0;
I am looking for a way similar to the line above, but with the client as target. Thanks in advance!
我正在寻找一种类似于上面一行的方法，但以客户端为目标。提前致谢！
回答by PresleyDias
this answer is very late but will be helpful so some one i am sure
这个答案很晚了，但会有所帮助，所以我相信有人
To get the images from the serverto the client systemyou can use this
要将图像从服务器获取到客户端系统，您可以使用它
"C:\Program Files\PostgreSQL.0\bin\psql.exe" -h 192.168.1.101 -p 5432 -d mDB -U mYadmin -c "\lo_export 19135 'C://leeImage.jpeg' ";
Where
在哪里
- h 192.168.1.101: is the server system IP
- -d mDB: the database name
- -U mYadmin: user name
- \lo_export: the export function that will create the image at the client system location
- C://leeImage.jpeg: The location and the name of the target image from the OID of the image
- 19135: this is the OID of the image in you table.
- h 192.168.1.101: 是服务器系统IP
- -d mDB: 数据库名称
- -U myadmin: 用户名
- \lo_export：将在客户端系统位置创建图像的导出函数
- C://leeImage.jpeg: 目标图片的位置和名称来自图片的OID
- 19135：这是您表中图像的 OID。
the documentation is here commandprompt.com
文档在这里commandprompt.com
回答by Dave G
Georg,
乔治，
According to the documentation for 9.1, lo_export is relative to the client executing the call. So if clientA is connected to databaseB, when clientA executes your SQL, lo_export should create the file on clientA where you've told it to.
根据9.1的文档， lo_export 与执行调用的客户端有关。因此，如果clientA 连接到databaseB，当clientA 执行您的SQL 时，lo_export 应该在clientA 上创建您指定的文件。
In light of the fact that you've stated your using JDBC under MATLAB (I'm not familiar with what you can do under there nor am I familiar with the interface to perform the call), if you are calling this from a JDBC connection manually:
鉴于您已声明在 MATLAB 下使用 JDBC（我不熟悉您在那里可以做什么，也不熟悉执行调用的接口），如果您从 JDBC 连接调用它手动：
java.sql.Connection conn= ...
java.sql.Statement stmt= conn.createStmt();
java.sql.ResultSet rs= stmt.executeQuery("select data from images where id=0");
// Assume one result
rs.next();
// Gets the blob input stream
InputStream blobData= rs.getInputStream(1);
// At this point you will have to write it to a file.
// See below
rs.close();
stmt.close();
conn.close();
I have played very loose and fast with the JDBC operations for brevity here. There should be more error checking as well as try/catch/finally statements to wrap and clean up the connections.
为简洁起见，我在这里对 JDBC 操作进行了非常宽松和快速的操作。应该有更多的错误检查以及 try/catch/finally 语句来包装和清理连接。
回答by Daniel Vérité
It's not possible, because all the PostgreSQL server can do is send back data to the client through the network connection that the client has established. In particular, it can't create a file on the client filesystem, only client code can do that.
这是不可能的，因为 PostgreSQL 服务器所能做的就是通过客户端建立的网络连接将数据发送回客户端。特别是，它不能在客户端文件系统上创建文件，只有客户端代码才能做到这一点。
回答by Andre Valdestilhas
Source: http://www.postgresql.org/docs/8.4/static/lo-funcs.html
来源：http: //www.postgresql.org/docs/8.4/static/lo-funcs.html
CREATE TABLE image (
name text,
raster oid
);
SELECT lo_creat(-1); -- returns OID of new, empty large object
SELECT lo_create(43213); -- attempts to create large object with OID 43213
SELECT lo_unlink(173454); -- deletes large object with OID 173454
INSERT INTO image (name, raster)
VALUES ('beautiful image', lo_import('/etc/motd'));
INSERT INTO image (name, raster) -- same as above, but specify OID to use
VALUES ('beautiful image', lo_import('/etc/motd', 68583));
SELECT lo_export(image.raster, '/tmp/motd') FROM image
WHERE name = 'beautiful image';
