How to export raster from PostGIS in one go?

Question

In the simplified canonical example (which I often see on forums and in the books) of a raster exporting script the OID (returned by lo_create(0), here is 9585208) has to be known before the penultimate script line (lo_export 9585208 'C:/temp/raster.png'):

SELECT oid, lowrite(lo_open(oid, 131072), img) As num_bytes
FROM (
    VALUES (
        lo_create(0),
        (SELECT ST_AsPNG(rast)
        FROM bag_o_rasters
        LIMIT 1)
    )
) As v(oid, img);

lo_export 9585208 'C:/temp/raster.png'

SELECT lo_unlink(9585208);

I have a hard time with figuring out how to make PSQL cli utility to run this script in one pass, i.e., how to communicate OID returned by lo_create(0) to lo_export command. It seems the lo_export is client side command and it is not able to digest any query results... please, help...

score 3 · Accepted Answer · answered Oct 30 '18 at 22:38

Finally, there is a PSQL command \gset which stores values into the script variables, the documentation says:

\gset sends the current query buffer to the server and stores the query's output into psql variables. The query to be executed must return exactly one row. Each column of the row is stored into a separate variable, named the same as the column.

Here is correspondingly modified script:

SELECT lo_create(0) as blob_oid

\gset

SELECT oid, lowrite(lo_open(oid, 131072), img) As num_bytes
FROM (
    VALUES (
        :blob_oid,
        (SELECT ST_AsPNG(rast)
        FROM bag_o_rasters
        LIMIT 1)
    )
) As v(oid,img);

\lo_export :blob_oid 'C:/temp/raster.png'

SELECT lo_unlink(:blob_oid);

How to export raster from PostGIS in one go?

1 Answers1