pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Default on update 4+ messages / 2 participants [nested] [flat]
* Default on update @ 2005-11-23 11:43 lucas@presserv.org 2005-11-23 12:22 ` Re: Default on update Richard Huxton <dev@archonet.com> 0 siblings, 1 reply; 4+ messages in thread From: lucas@presserv.org @ 2005-11-23 11:43 UTC (permalink / raw) To: pgsql-sql Hi. Is there a way to create "default" constraint on UPDATE query. It's becouse I have a bool field that may NOT support NULL value, but the Front-End calls null for FALSE values. I was thinking something like: create table table1 ( id serial primary key, bv bool default false not null ); I would want to replace "bv" values with FALSE when insert/update NULL value for this field. Or need I create a TRIGGER that check it and replace the itens??? CREATE or REPLACE function TG_table1_check RETURNS trigger as ' BEGIN IF nullvalue(NEW.bv) THEN NEW.bv=FALSE; END IF; END; ' language 'plpgsql'; CREATE TRIGGER TG_table1_check BEFORE UPDATE on table1 for each row execute procedure tg_table1_check(); Other question: I have a lot of triggers in my db system, I have table that has 5/6 triggers, many triggers are simple (like the tg_table1_check), any are complex... Is it a problem??? My tests are with few regs and run fine. Where can I read more about triggers and performance? Thanks for all. --- Lucas Vendramin ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Default on update 2005-11-23 11:43 Default on update lucas@presserv.org @ 2005-11-23 12:22 ` Richard Huxton <dev@archonet.com> 2005-11-23 13:56 ` Re: Default on update lucas@presserv.org 0 siblings, 1 reply; 4+ messages in thread From: Richard Huxton @ 2005-11-23 12:22 UTC (permalink / raw) To: lucas@presserv.org; +Cc: pgsql-sql lucas@presserv.org wrote: > Hi. > Is there a way to create "default" constraint on UPDATE query. > It's becouse I have a bool field that may NOT support NULL value, but the > Front-End calls null for FALSE values. Sounds like your frontend is broken. > I was thinking something like: > create table table1 ( > id serial primary key, > bv bool default false not null > ); > I would want to replace "bv" values with FALSE when insert/update NULL value for > this field. You could do this by having the application insert to a view with a rule that replaces null bv values before redirecting to the base table. > Or need I create a TRIGGER that check it and replace the itens??? > CREATE or REPLACE function TG_table1_check RETURNS trigger as ' > BEGIN > IF nullvalue(NEW.bv) THEN IF NEW.bv IS NULL THEN > NEW.bv=FALSE; > END IF; > END; > ' language 'plpgsql'; CREATE TRIGGER TG_table1_check BEFORE UPDATE on table1 for > each row execute procedure tg_table1_check(); To make the trigger work you'll have to relax the "NOT NULL" on column "bv" otherwise PG's type-checks will raise an error. Oh, and then make sure the trigger is called before INSERT too. > Other question: I have a lot of triggers in my db system, I have table that has > 5/6 triggers, many triggers are simple (like the tg_table1_check), any are > complex... Is it a problem??? My tests are with few regs and run fine. Where > can I read more about triggers and performance? Triggers behave exactly as you'd expect. For every row (or statement) the function gets executed. Difficult to say what effect they'll have on performance without testing with your actual setup. -- Richard Huxton Archonet Ltd ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Default on update 2005-11-23 11:43 Default on update lucas@presserv.org 2005-11-23 12:22 ` Re: Default on update Richard Huxton <dev@archonet.com> @ 2005-11-23 13:56 ` lucas@presserv.org 2005-11-23 14:08 ` Re: Default on update Richard Huxton <dev@archonet.com> 0 siblings, 1 reply; 4+ messages in thread From: lucas@presserv.org @ 2005-11-23 13:56 UTC (permalink / raw) To: pgsql-sql > Quoting Richard Huxton <dev@archonet.com>: > lucas@presserv.org wrote: >> Hi. >> Is there a way to create "default" constraint on UPDATE query. >> It's becouse I have a bool field that may NOT support NULL value, but the >> Front-End calls null for FALSE values. > > Sounds like your frontend is broken. > Yes, it is. But I have no access to the front-end. I will send it to the programmer. >> I was thinking something like: >> create table table1 ( >> id serial primary key, >> bv bool default false not null >> ); >> I would want to replace "bv" values with FALSE when insert/update >> NULL value for >> this field. > > You could do this by having the application insert to a view with a > rule that replaces null bv values before redirecting to the base > table. Is more functional to create a Rule instead of a trigger? > >> Or need I create a TRIGGER that check it and replace the itens??? >> CREATE or REPLACE function TG_table1_check RETURNS trigger as ' >> BEGIN >> IF nullvalue(NEW.bv) THEN > > IF NEW.bv IS NULL THEN > What is the difference between nullvalue() and IS NULL??? >> NEW.bv=FALSE; >> END IF; >> END; >> ' language 'plpgsql'; CREATE TRIGGER TG_table1_check BEFORE UPDATE >> on table1 for >> each row execute procedure tg_table1_check(); > > To make the trigger work you'll have to relax the "NOT NULL" on > column "bv" otherwise PG's type-checks will raise an error. Oh, and > then make sure the trigger is called before INSERT too. > Okay. >> Other question: I have a lot of triggers in my db system, I have >> table that has >> 5/6 triggers, many triggers are simple (like the tg_table1_check), any are >> complex... Is it a problem??? My tests are with few regs and run fine. Where >> can I read more about triggers and performance? > > Triggers behave exactly as you'd expect. For every row (or statement) > the function gets executed. Difficult to say what effect they'll have > on performance without testing with your actual setup. > Ok, I will test with more records. Thank you. ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Default on update 2005-11-23 11:43 Default on update lucas@presserv.org 2005-11-23 12:22 ` Re: Default on update Richard Huxton <dev@archonet.com> 2005-11-23 13:56 ` Re: Default on update lucas@presserv.org @ 2005-11-23 14:08 ` Richard Huxton <dev@archonet.com> 0 siblings, 0 replies; 4+ messages in thread From: Richard Huxton @ 2005-11-23 14:08 UTC (permalink / raw) To: lucas@presserv.org; +Cc: pgsql-sql lucas@presserv.org wrote: >>> I would want to replace "bv" values with FALSE when insert/update >>> NULL value for >>> this field. >> >> You could do this by having the application insert to a view with a >> rule that replaces null bv values before redirecting to the base table. > > Is more functional to create a Rule instead of a trigger? It's different - you can think of a rule as a kind of macro, rewriting the query the application provides. Make sure you read the manuals carefully to be sure you understand how they work though. >>> Or need I create a TRIGGER that check it and replace the itens??? >>> CREATE or REPLACE function TG_table1_check RETURNS trigger as ' >>> BEGIN >>> IF nullvalue(NEW.bv) THEN >> >> >> IF NEW.bv IS NULL THEN >> > What is the difference between nullvalue() and IS NULL??? "IS NULL" is a standard SQL expression. -- Richard Huxton Archonet Ltd ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-11-23 14:08 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2005-11-23 11:43 Default on update lucas@presserv.org 2005-11-23 12:22 ` Richard Huxton <dev@archonet.com> 2005-11-23 13:56 ` lucas@presserv.org 2005-11-23 14:08 ` Richard Huxton <dev@archonet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox