Re: Trigger for updating view with join - Mailing list pgsql-sql
From | Dmitry Morozovsky |
---|---|
Subject | Re: Trigger for updating view with join |
Date | |
Msg-id | alpine.BSF.2.00.1309041002390.75311@woozle.rinet.ru Whole thread Raw |
In response to | Re: Trigger for updating view with join (Dmitry Morozovsky <marck@rinet.ru>) |
List | pgsql-sql |
Heh, it's me spamming you again :) now -- asking for comments. > > create trigger fsl_update instead of insert or update on fsl ... > > > > but till now did not succeed in it. Quick googlink did not help either. > > Argh. My google-fu is definitely low in the night ;) > > for the record: it's right at > http://www.postgresql.org/docs/9.1/static/plpgsql-trigger.html > example 39-5 this example is only related, as the second one found at http://vibhork.blogspot.ru/2011/10/updateable-views-in-postgresql-91-using.html (the latter does not allow inserting one-to many records) after a bit if try'n'error, I'm with the following --- 8< --- create table machines ( mid serial not null primary key, mname text not null unique ); create table fs ( fsid serial not null primary key, mid int not null references machines, mpoint text not null ); create unique index fs_mp on fs(mid, mpoint); create view fsl as select fsid, mid, mname, mpoint from fs join machines using(mid); create or replace function update_fsl() returns trigger as $$ declare mmid bigint; begin if (TG_OP = 'DELETE')then -- only fs is deleted, not machine delete from fs where mpoint= OLD.mpoint and mid = (select mid from machines where mname=OLD.mname); if NOTFOUND then return NULL; else return OLD; end if; elsif (TG_OP = 'INSERT') then select mid into mmidfrom machines where mname=NEW.mname; if NOT FOUND then insert into machines (mname) values(NEW.mname); select mid into mmid from machines where mname=NEW.mname; end if; insert into fs (mid, mpoint) select mmid, NEW.mpoint; return NEW; elsif (TG_OP = 'UPDATE')then -- only mpoint is allowed to be changed update fs set mpoint = NEW.mpoint where mpoint = OLD.mpoint and mid = (select mid from machines where mname=OLD.mname); if NOT FOUND then return NULL; else return NEW; end if; end if; end; $$ language plpgsql; create trigger update_fsl instead of insert or update or delete on fsl for each row execute procedure update_fsl(); --- 8< --- I looks all requested operations are fine, but I'm a bit reluctant: maybe I missed something easy but important? Thanks! -- Sincerely, D.Marck [DM5020, MCK-RIPE, DM3-RIPN] [ FreeBSD committer: marck@FreeBSD.org ] ------------------------------------------------------------------------ *** Dmitry Morozovsky --- D.Marck --- Wild Woozle --- marck@rinet.ru *** ------------------------------------------------------------------------