PostgreSQL exports a large object to a client

Question

I have a PostgreSQL 9.1 database in which images are stored as large objects. Is there a way to export files to the client file system via an SQL query?

select lo_export(data,'c:\img\test.jpg') from images where id=0; 

I am looking for a method similar to the line above, but with the client as the target. Thanks in advance!

Answer 1

this answer is very late, but will be useful, so I'm sure that

To get images from the server on 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

• h 192.168.1.101 : this is an IP server system
• -d mDB : database name
• -U mYadmin : username
• \ lo_export : an export function that will create an image at the client’s location.
• C: //leeImage.jpeg : location and name of the target image from the OID of the image
• 19135 : This is the OID of the image in your table.

the documentation is here commandprompt.com

Answer 2

George,

According to the documentation for 9.1 , lo_export refers to the client making the call. Therefore, if clientA is connected to databaseB when clientA executes your SQL, lo_export should create a file on clientA where you told it.

---

In light of the fact that you stated that you are using JDBC under MATLAB (I am not familiar with what you can do, and I am not familiar with the interface for making a call), if you call 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 played very fast and fast with JDBC operations for short here. There should be more error checks, as well as try / catch / finally statements for transferring and cleaning connections.

An example of copying files .

Answer 3

This is not possible because all the PostgreSQL server can do is send data to the client through the network connection established by the client. In particular, it cannot create a file on the client file system, only client code can do this.

Answer 4

Source: 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';