lo_export (server)

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