Re: lo_export (server)

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