Re: Deleting BLOBs

On Sun, 12 Aug 2012, Neanderthelle Jones wrote:

> I'd like to delete all the image files from a table by issuing an
> interactive psql command.  Given
>
> CREATE TABLE image (
>     name text,
>     rast oid
> );
>
> with most tuples having images in rast, wanna do something like
>
> => select lo_unlink('select rast from image');
>
> Can such a thing be done, and how?  I guess I've forgotten the syntax.
>
> Or will an update that sets the oids to null or 0 work?
>
> (pgsql 9.1.2)

One attempt.  Is the error mine or PostgreSQL's?

$ for i in $(psql -q -t -U elle -d my_db \
 -c "SELECT raster FROM images where raster > 0"); do
 echo $i
 psql -q -U elle -d my_db -c "SELECT lo_unlink($i)"
done

21234
ERROR:  large object 21234 does not exist
21235
ERROR:  large object 21235 does not exist
21236
ERROR:  large object 21236 does not exist
21237
ERROR:  large object 21237 does not exist
21238
ERROR:  large object 21238 does not exist
21239
ERROR:  large object 21239 does not exist
21240
ERROR:  large object 21240 does not exist
21241
ERROR:  large object 21241 does not exist
21242
ERROR:  large object 21242 does not exist
21243
ERROR:  large object 21243 does not exist

etc. etc.