Have you ever tried to output a file stored in a bytea column from PostgreSQL using PSQL? It ain't pretty. Andrew Dunstan
has a couple of examples of doing this in his article Clever Trick Challenge. This issue has now become very relevant for PostGIS raster use. The reason being is that now that PostGIS in it's 2.0 encarnation can do clipping of rasters, unions of rasters,
map algebra, convert geometries to rasters and convert those rasters to various imagery type file formats, people naturally assume it should be trivial
to output your new fangled raster query as a jpeg or png etc via, PSQL. Turns out it is not. For those who need to, I've documented an example using
one suggestion from Andrew's blog which utilizes the Large object support built into PostgreSQL. It would be nice if this were simpler.
I chose this approach because it was the only one that didn't assume a user has Perl installed or is on a Unix system so should work fairly much the same regardless the client OS in use. You can find the example in Outputting Rasters with PSQL. Suggestions on how to improve on this are welcome.
I don't think that psql is that far off from being able to do this. True you can use other tools, but as a developer of PostreSQL specific contribs (PostGIS in this case), I like to have a solution readily available that I know all my end-users can run without requiring extra installation. psql fits the bill nicely for that.
You might want to have a look at SQL Workbench/J which can easily write BLOBs (that are part of a query result) into a local file on the client. Either for all rows or for a specific row and this can be used as a console application or in batch mode: http://www.sql-workbench.net/blob_example.html