From: Richard Huxton <dev@archonet.com>
To: lucas@presserv.org
Cc: pgsql-sql@postgresql.org
Subject: Re: Default on update
Date: Wed, 23 Nov 2005 12:22:59 +0000
Message-ID: <43845F23.60204@archonet.com> (raw)
In-Reply-To: <20051123084353.8ga6nrpx43gg8408@www.presserv.org>
References: <20051123084353.8ga6nrpx43gg8408@www.presserv.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, lucas@presserv.org
Subject: Re: Default on update
In-Reply-To: <43845F23.60204@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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