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 NOT FOUND then return NULL; else return OLD; end if; elsif (TG_OP = 'INSERT') then select mid into mmid from 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: ma...@freebsd.org ] ------------------------------------------------------------------------ *** Dmitry Morozovsky --- D.Marck --- Wild Woozle --- ma...@rinet.ru *** ------------------------------------------------------------------------ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
