Thread: unique value - trigger?
Hi folks, I'm back with my lnumbers table again. nymr=# \d lnumbers Table "lnumbers" Column | Type | Modifiers -----------+-----------------------+-----------lnid | integer | not nulllnumber | character varying(10)| not nulllncurrent | boolean | Primary key: lnumbers_pkey Triggers: RI_ConstraintTrigger_7575462 While each loco can have a number of different numbers, only one can be current at any one time. I want to make it so that if I set lncurrent to true for one row, any existing true rows are set to false. I'm guessing that I need to create a trigger to be actioned after an insert or update which would update set lncurrent=false where lnid not = <current lnid> but I can't seem to sus it put. -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
Gary Stainburn wrote: >Hi folks, > >I'm back with my lnumbers table again. > >nymr=# \d lnumbers > Table "lnumbers" > Column | Type | Modifiers >-----------+-----------------------+----------- > lnid | integer | not null > lnumber | character varying(10) | not null > lncurrent | boolean | >Primary key: lnumbers_pkey >Triggers: RI_ConstraintTrigger_7575462 > >While each loco can have a number of different numbers, only one can be >current at any one time. > >I want to make it so that if I set lncurrent to true for one row, any existing >true rows are set to false. > >I'm guessing that I need to create a trigger to be actioned after an insert or update which would update set lncurrent=falsewhere lnid not = <current lnid> > Why "not"? I thought, you wanted just the opposite - update the ones that *do* have the same lnid? I'd also recommend you to add ' and lncurrent' to the query - otherwise every insert would be updating *every* row with the same lnid (it doesn't check if the new row is actually the same as the old one) before updating, and that may be expensive. You may also want to create a pratial index on lnumbers (lnid) where lncurrent to speed up your trigger >but I can't seem to sus it put. > > What is the problem? Dima > >
On Thursday 17 Jul 2003 3:34 pm, Dmitry Tkach wrote: > Gary Stainburn wrote: > >Hi folks, > > > >I'm back with my lnumbers table again. > > > >nymr=# \d lnumbers > > Table "lnumbers" > > Column | Type | Modifiers > >-----------+-----------------------+----------- > > lnid | integer | not null > > lnumber | character varying(10) | not null > > lncurrent | boolean | > >Primary key: lnumbers_pkey > >Triggers: RI_ConstraintTrigger_7575462 > > > >While each loco can have a number of different numbers, only one can be > >current at any one time. > > > >I want to make it so that if I set lncurrent to true for one row, any > > existing true rows are set to false. > > > >I'm guessing that I need to create a trigger to be actioned after an > > insert or update which would update set lncurrent=false where lnid not = > > <current lnid> > > Why "not"? I thought, you wanted just the opposite - update the ones > that *do* have the same lnid? > I'd also recommend you to add ' and lncurrent' to the query - otherwise > every insert would be updating *every* row with the same lnid (it > doesn't check if the new row is actually the same as the old one) before > updating, and that may be expensive. > You may also want to create a pratial index on lnumbers (lnid) where > lncurrent to speed up your trigger All good and valid points. > > >but I can't seem to sus it put. > > What is the problem? The problem is I don't know how to convert the following pseudo code to valid SQL: create trigger unique_current on insert/update to lnumbers if new.lncurrent = true update lnumbers set all other recordsfor this loco to false > > Dima > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
> > >The problem is I don't know how to convert the following pseudo code to valid >SQL: > >create trigger unique_current on insert/update to lnumbers > if new.lncurrent = true > update lnumbers set all other records for this loco to false > > > I see... The bad news is you can't do it directly... You can only create functions to run as triggers, not plain sql statements for some reason :-( The correct syntax is create trigger unique_current before insert or update on lnumbers for each row execute procedure reset_current_lnum(); Where reset_current_lnum () is a function, that you have to write either in "C" or in 'plpgsql'; I could give you some sample code in "C" to do that, but it's rather complicated if you are not used to writing postgres stored procs in C... plpgsql would be much easier, but I can't help you there, because I don't know the syntax ... something like this, I guess, but I doubt this will compile exactly as it is: create function reset_current_lnum () returns triggers as ' begin if new.lncurrent = true update lnumbers set lncurrent=false where lnid=new.lnid and lncurrent; endif return new; end;' language 'plpgsql'; Dima
n Thu, Jul 17, 2003 at 12:56:52PM +0100, Gary Stainburn wrote: > > nymr=# \d lnumbers > Table "lnumbers" > Column | Type | Modifiers > -----------+-----------------------+----------- > lnid | integer | not null > lnumber | character varying(10) | not null > lncurrent | boolean | > Primary key: lnumbers_pkey > Triggers: RI_ConstraintTrigger_7575462 > > I want to make it so that if I set lncurrent to true for one row, any existing > true rows are set to false. > > I'm guessing that I need to create a trigger to be actioned after an insert or > update which would update set lncurrent=false where lnid not = <current lnid> Absolutely. Something like this will work: CREATE FUNCTION lnumbers_current_trigger() RETURNS TRIGGER AS ' BEGIN IF NEW.lncurrent THEN UPDATE lnumbers SET lncurrent = ''f'' WHERE lnid = NEW.lnid AND lnumber != NEW.lnumber ANDlncurrent = ''t''; END IF; RETURN NEW; END' LANGUAGE 'plpgsql'; CREATE TRIGGER lnumbers_current AFTER UPDATE OR INSERT ON lnumbers FOR EACH ROW EXECUTE PROCEDURE lnumbers_current_trigger(); (Lightly tested only on 7.3.3) In the WHERE condition in the function, specifying "lncurrent = 't'" means that we don't update more rows than we have to. Making the trigger fire AFTER INSERT as well as AFTER UPDATE means that if you just add a new row with lncurrent set to true, it Does The Right Thing. In this particular example, the trigger will work perfectly well as a BEFORE, also. If you can't or don't want to install PL/PgSQL (or some other procedural language), you can do it with rules. It's more long-winded that way, although I also think it's more elegant... Richard
On Thu, Jul 17, 2003 at 12:56:52PM +0100, Gary Stainburn wrote: > > nymr=# \d lnumbers > Table "lnumbers" > Column | Type | Modifiers > -----------+-----------------------+----------- > lnid | integer | not null > lnumber | character varying(10) | not null > lncurrent | boolean | > Primary key: lnumbers_pkey > Triggers: RI_ConstraintTrigger_7575462 > > I want to make it so that if I set lncurrent to true for one row, any existing > true rows are set to false. > > I'm guessing that I need to create a trigger to be actioned after an insert or > update which would update set lncurrent=false where lnid not = <current lnid> Absolutely. Something like this will work: CREATE FUNCTION lnumbers_current_trigger() RETURNS TRIGGER AS ' BEGIN IF NEW.lncurrent THEN UPDATE lnumbers SET lncurrent = ''f'' WHERE lnid = NEW.lnid AND lnumber != NEW.lnumber ANDlncurrent = ''t''; END IF; RETURN NEW; END' LANGUAGE 'plpgsql'; CREATE TRIGGER lnumbers_current AFTER UPDATE OR INSERT ON lnumbers FOR EACH ROW EXECUTE PROCEDURE lnumbers_current_trigger(); (Lightly tested only on 7.3.3) In the WHERE condition in the function, specifying "lncurrent = 't'" means that we don't update more rows than we have to. Making the trigger fire AFTER INSERT as well as AFTER UPDATE means that if you just add a new row with lncurrent set to true, it Does The Right Thing. In this particular example, the trigger will work perfectly well as a BEFORE, also. If you can't or don't want to install PL/PgSQL (or some other procedural language), you can do it with rules. It's more long-winded that way, although I also think it's more elegant... Richard