Quoting Richard Huxton <dev@archonet.com>:
[EMAIL PROTECTED] 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.
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
