Re: [GENERAL] Deleting BLOBs

2012-08-12 Thread Tom Lane
Neanderthelle Jones writes: > On Sun, 12 Aug 2012, Tom Lane wrote: >> That script looks reasonable enough, if perhaps not too fast. Are you >> sure the table actually does reference live large objects? > What does "live" mean? Well, my point is that the OIDs in the table are just numbers. They

Re: [GENERAL] Deleting BLOBs

2012-08-12 Thread Neanderthelle Jones
On Sun, 12 Aug 2012, Tom Lane wrote: > Neanderthelle Jones writes: > > 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)" >

Re: [GENERAL] Deleting BLOBs

2012-08-12 Thread Tom Lane
Neanderthelle Jones writes: > 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 no

Re: [GENERAL] Deleting BLOBs

2012-08-12 Thread Neanderthelle Jones
On Mon, 13 Aug 2012, Neanderthelle Jones wrote: > > $ 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 "BEGIN; SELECT lo_unlink($i); COMMIT" makes no difference. -- Sent via

Re: [GENERAL] Deleting BLOBs

2012-08-12 Thread Neanderthelle Jones
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 > > => sel

Re: [GENERAL] Expression alias not recognized in WHERE clause (ERROR: relation "p" does not exist)

2012-08-12 Thread Stefan Keller
Salut Alban Thanks for your patient hints. As your signature suggests, I probably could not see the forest for the trees. But now I think I do (see below) - except for the following: 2012/8/9 Alban Hertroys wrote: > You're referencing "p" as a table, not as a table-alias, because you > select FR

Re: [GENERAL] How to raise error from PostgreSql SQL statement if some condition is met

2012-08-12 Thread Craig Ringer
On 08/12/2012 06:02 PM, Andrus wrote: ... RaiseException(text, variadic text[]) .. VARIADIC is keyword, not datatype Thank you. I tried code below but got error shown in comment. No idea what I'm doing wrong. Andrus. CREATE OR REPLACE FUNCTION RaiseException(text, variadic text[] ) RET

Re: [GENERAL] Deleting BLOBs

2012-08-12 Thread Andreas Kretschmer
> > select lo_unlink(rest) from image > > but i'm never used this function... i've ... - Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Deleting BLOBs

2012-08-12 Thread Andreas Kretschmer
If i where you i would try: select lo_unlink(rest) from image but i'm never used this function... Neanderthelle Jones hat am 12. August 2012 um 12:16 geschrieben: > I'd like to delete all the image files from a table by issuing an > interactive psql command. Given > > CREATE TABLE image (

[GENERAL] Deleting BLOBs

2012-08-12 Thread Neanderthelle Jones
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,

Re: [GENERAL] How to raise error from PostgreSql SQL statement if some condition is met

2012-08-12 Thread Andrus
... RaiseException(text, variadic text[]) .. VARIADIC is keyword, not datatype Thank you. I tried code below but got error shown in comment. No idea what I'm doing wrong. Andrus. CREATE OR REPLACE FUNCTION RaiseException(text, variadic text[] ) RETURNS void LANGUAGE plpgsql AS $BODY$ BEG

Re: [GENERAL] How to raise error from PostgreSql SQL statement if some condition is met

2012-08-12 Thread Pavel Stehule
2012/8/12 Andrus : > Than you very much. > It worked. > > I tried to extend it to pass message parameters. Tried code below but got > syntax error. How to pass message parameters ? > > Andrus. > > CREATE OR REPLACE FUNCTION RaiseException(text, variadic ) ... RaiseException(text, variadic text[])

Re: [GENERAL] How to raise error from PostgreSql SQL statement if some condition is met

2012-08-12 Thread Andrus
Than you very much. It worked. I tried to extend it to pass message parameters. Tried code below but got syntax error. How to pass message parameters ? Andrus. CREATE OR REPLACE FUNCTION RaiseException(text, variadic ) RETURNS void LANGUAGE plpgsql AS $BODY$ BEGIN RAISE EXCEPTION $1, $2;