From: Dmitry Morozovsky <marck@rinet.ru>
To: pgsql-sql@postgresql.org
Subject: Re: Trigger for updating view with join
Date: Wed, 4 Sep 2013 10:09:17 +0400 (MSK)
Message-ID: <alpine.BSF.2.00.1309041002390.75311@woozle.rinet.ru> (raw)
In-Reply-To: <alpine.BSF.2.00.1309040919030.75311@woozle.rinet.ru>
References: <alpine.BSF.2.00.1309040335020.75311@woozle.rinet.ru>
<alpine.BSF.2.00.1309040919030.75311@woozle.rinet.ru>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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 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: marck@FreeBSD.org ]
------------------------------------------------------------------------
*** Dmitry Morozovsky --- D.Marck --- Wild Woozle --- marck@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
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: marck@rinet.ru
Subject: Re: Trigger for updating view with join
In-Reply-To: <alpine.BSF.2.00.1309041002390.75311@woozle.rinet.ru>
* 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