Skip site navigation (1) Skip section navigation (2)

lo_export (server)

From: Steve Tucknott <steve(at)retsol(dot)co(dot)uk>
To: PostGreSQL <pgsql-novice(at)postgresql(dot)org>
Subject: lo_export (server)
Date: 2004-11-29 21:24:37
Message-ID: 1101763479.11208.42.camel@retsol1 (view raw or flat)
Thread:
Lists: pgsql-novice
PostgreSQL 7.4.5 Redhat 8

I have a cursor:
SELECT  lo_export(document,'/tmp/vendBImage.png') AS image, 
             docForeignRecNo AS vendBRecNo 
FROM document  AS document 
         JOIN lookupCodes 
                 ON document.docuLookUpCodesRecNo = lookUpCodes.recNo 
                AND lookUpCodes.subClassification = 'P'  
                AND document.docForeignTableName = 'vendorbus' 
                AND document.docForeignRecNo IN      
                       (SELECT vendorBusRecNo         
                        FROM propmatch AS match         
                       WHERE match.buyerRecNo = 6)  
ORDER BY vendBRecNo

The cursor works fine - apart from the fact that the output image always
stays the same- whatever happens to be the first image returned remains
for each row read (I can see this by stopping the cursor after each read
and looking at the exported image).
If the cursor is changed to make the output file name unique (ie
'/tmp/vendBImage||TRUNC(RANDOM()*10000)||'.png') then I can see the
correct image in the unique filename after each row read. Any ideas as
to why the original image appears not to be overwritten when using a
fixed output file name? (I need to use a fixed name as I need a known
value to use in subsequent copy statement - the cursor is read in the
program and for each row read the image is copied away)

I have tried running the statement in psql with the same result 
SELECT  lo_export(document,'/tmp/vendBImage.png') AS image,
docForeignRecNo AS vendBRecNo FROM document  AS document JOIN
lookupCodes ON document.docuLookUpCodesRecNo = lookUpCodes.recNo AND
lookUpCodes.subClassification = 'P'
 AND document.docForeignTableName = 'vendorbus' AND
document.docForeignRecNo IN
(3,4,2) order by vendbrecNo;
 image | vendbrecno
-------+------------
     1 |          2
     1 |          3
     1 |          3
     1 |          3
     1 |          4
(5 rows)

The image in /tmp at the end is that of record 2. If however, I change
the statement to:
SELECT  lo_export(document,'/tmp/vendBImage.png') AS image,
docForeignRecNo AS
vendBRecNo FROM document  AS document JOIN lookupCodes ON
document.docuLookUpCodesRecNo = lookUpCodes.recNo AND
lookUpCodes.subClassification = 'P'
 AND document.docForeignTableName = 'vendorbus' AND
document.docForeignRecNo IN
(3) order by vendbrecNo;

Then I see image for record 3 - ditto for record 4 (record 4's image and
2's image do differ - as I know that by running this in psql I sholuld
only expect to see the image for rec 4)

I feel that I'm doing something really stupid in the code, but I can't
see what it is!

Any help would be appreciated.



Regards,

Steve Tucknott

ReTSol Ltd

DDI: 01903 828769

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2004-11-29 22:50:12
Subject: Re: lo_export (server)
Previous:From: Tom LaneDate: 2004-11-29 19:30:37
Subject: Re: problematic upgrade

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group