using \COPY with BINARY for lo_export in postgreSQL on server system

Question

i am working wit h PostgreSQL 9 for an application, i have a database with a table 'species' where i store fish species details along with the image of the species. the table is

       CREATE TABLE fishes
               (
                  fishes character varying(7) NOT NULL,
                  speciesimages oid,
                  CONSTRAINT species_pkey PRIMARY KEY (species)
                 )
                   WITH (
                       OIDS=TRUE
               );

i use

INSERT INTO species(fishes,fishesimages VALUES('01',lo_import('C://01.jpg'));

To store the images in the database.

to retrieve the images i use

       SELECT lo_export(fishes.fishesimages,'c://outimage.jpg') 
       FROM   fishes
       WHERE  fishes= '01';

This works fine when the host is Localhost but when it is the server i cannot use the path c:// as this may not exist on the server system and i dont have permissions anyways.

so i set out to use

\COPY

like this

"C:\Program Files\PostgreSQL\9.0\bin\psql.exe" -h 192.168.1.101 -p 5432 -d myDB -U DB_admin -c  "\COPY (SELECT lo_export(fishes.fishesimages,'01.jpg') FROM   fishes WHERE  species = '01') TO 'C://leeImage.jpeg' WITH BINARY";

but this create a image file but when i open it its invalid image enter image description here

can anyone tell me how to use lo_export function from the server machine and create the image on client machine?

PresleyDias · Accepted Answer · 2012-08-19 15:04:37Z

3

Basically:

lo_export will instruct the server to write a file locally (always)
\copy will be transformed by psql to a COPY ... TO STDOUT command and the output written to the specified file. (So what is written to that file is the result of the select statment you were doing before)

So, you can not use lo_export in this way, it will always write a file onto the server's filesystem.

Of course, you can solve this simply by having the server write to a shared drive, and then read the file from that drive. Ugly, but effective IMHO.

For some recent versions of psql (not sure when this was introduced) there is a \lo_export psql command which takes an OID and filename, e.g.:

\lo_export 28914648 testfile

However you need to get the OID of the file into the script somehow...

You can write a PL/PGsql function like this to dump the file as a bytea:

CREATE OR REPLACE FUNCTION getfiledata(lobjid oid) RETURNS bytea
  STRICT STABLE LANGUAGE plpgsql AS $$
DECLARE
  fd int4;
  imgsize int4;
  INV_READ int4 := 262144;
  SEEK_SET int4 := 0;
  SEEK_END int4 := 2;
BEGIN
  SELECT lo_open(lobjid, INV_READ) INTO fd;
  PERFORM lo_lseek(fd, 0, SEEK_END);
  SELECT lo_tell(fd) INTO imgsize;
  PERFORM lo_lseek(fd, 0, SEEK_SET);
  RETURN loread(fd, imgsize);
END;
$$;

Now calling this function with the OID of the large object will return its content as a bytea value. You can thus call this function in a COPY command and it will return the file data... and by using \copy it will be sent to the client.

It's generally recommended these days to use bytea columns directly rather than this large object interface (bytea was introduced a lot later). PostgreSQL will automatically move large values into out-of-line storage ("TOAST tables") and will also compress them (unless the storage mode is set to "external" to suppress this, which is probably the right thing to do for JPEG images etc)

EDIT : \lo_export Try this from commandprompt.com

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

where the number 19135 is the OID of the species whose image i want on the client system.. the OID you can get from the fishes table fishesimages OID use the OID in the above code and you can use the OID get the images.

edited Aug 19, 2012 at 15:04

PresleyDias

3,7116 gold badges41 silver badges62 bronze badges

answered Feb 16, 2012 at 14:27

araqnid

135k25 gold badges163 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

araqnid Over a year ago

simply change the SQL command in there to something like

\copy (SELECT getfiledata(species.speciesimages) FROM species WHERE species = 'Acaac01') TO 'C://leeImage.jpeg' WITH BINARY

PresleyDias Over a year ago

ok, but where to write CREATE OR REPLACE FUNCTION getfiledata(lobjid oid) RETURNS bytea this? meaning is it in built into postgreSQL ?

araqnid Over a year ago

oh right; just paste that into a psql prompt and run it once, it's a one-time setup thing that will persist between sessions.

araqnid Over a year ago

Oh, my bad... COPY ... WITH BINARY will always surround the data with some intro/trailer bytes, so you'd have to strip those. Glad to hear that \lo_export worked out for you!

PresleyDias Over a year ago

COPY ... WITH BINARY will always surround the data with some intro/trailer bytes, so you'd have to strip those. can you tell me how to do that?

Collectives™ on Stack Overflow

using \COPY with BINARY for lo_export in postgreSQL on server system

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related