PostgreSQL export large object to client

Question

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?

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!

Answer 1

this answer is very late but will be helpful so some one i am sure

To get the images from the server to the client system you can use this

"C:\Program Files\PostgreSQL\9.0\bin\psql.exe" -h 192.168.1.101 -p 5432 -d mDB -U mYadmin -c  "\lo_export 19135 'C://leeImage.jpeg' ";

Where

1. h 192.168.1.101 : is the server system IP
2. -d mDB : the database name
3. -U mYadmin : user name
4. \lo_export : the export function that will create the image at the client system location
5. C://leeImage.jpeg : The location and the name of the target image from the OID of the image
6. 19135 : this is the OID of the image in you table.

the documentation is here commandprompt.com

Answer 2

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.

---

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:

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.

File copy example.

Answer 3

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.