From: lucas@presserv.org
To: pgsql-sql@postgresql.org
Subject: Re: Default on update
Date: Wed, 23 Nov 2005 10:56:26 -0300
Message-ID: <20051123105626.fon1t82f0ctcgogw@www.presserv.org> (raw)
In-Reply-To: <20051123104134.3be3vpyxlc00kko4@www.presserv.org>
References: <20051123084353.8ga6nrpx43gg8408@www.presserv.org>
<43845F23.60204@archonet.com>
<20051123104134.3be3vpyxlc00kko4@www.presserv.org>
> 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.
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: lucas@presserv.org
Subject: Re: Default on update
In-Reply-To: <20051123105626.fon1t82f0ctcgogw@www.presserv.org>
* 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