Steve Tucknott <steve@retsol.co.uk> writes:
> 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
[ itch... ] This query is assuming that you know the exact timing of
the side-effects of the lo_export() function, which you do not.
I think the problem is that lo_export() will be evaluated before the
ORDER BY step occurs, ie, all the calls are executed in whatever order
the JOIN produces. Then the sort occurs, and then the cursor mechanism
hands back the rows one at a time. But by the time you see the first
row, the last execution of lo_export() has already overwritten all the
earlier outputs.
You could probably make it work as
SELECT lo_export(document,'/tmp/vendBImage.png') AS image,
docForeignRecNo AS vendBRecNo
FROM
(SELECT *
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
) subsel;
but this is still assuming a lot more than you ought to about the
execution engine's behavior. In particular you could easily have
off-by-one problems (say, the engine executing one row ahead of what
you've gotten back), and there's not going to be a lot of sympathy for
treating that as a bug.
My recommendation is not to execute more than one lo_export() per query,
if you expect them to overwrite the same file each time.
regards, tom lane
