pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTrigger for updating view with join
3+ messages / 1 participants
[nested] [flat]
* Trigger for updating view with join
@ 2013-09-03 23:38 Dmitry Morozovsky <marck@rinet.ru>
0 siblings, 1 reply; 3+ messages in thread
From: Dmitry Morozovsky @ 2013-09-03 23:38 UTC (permalink / raw)
To: pgsql-sql
Dear colleagues,
I'm running Pg 9.1 and have schema like the following:
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);
Now, I want to create trigger so I can issue
insert into fsl (mname,mpoint) values ('server','/usr')
I understand I should use smth like
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.
Could you point me to the right direction?
Thank you!
--
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Trigger for updating view with join
@ 2013-09-04 05:20 Dmitry Morozovsky <marck@rinet.ru>
parent: Dmitry Morozovsky <marck@rinet.ru>
0 siblings, 1 reply; 3+ messages in thread
From: Dmitry Morozovsky @ 2013-09-04 05:20 UTC (permalink / raw)
To: pgsql-sql
On Wed, 4 Sep 2013, Dmitry Morozovsky wrote:
> Dear colleagues,
>
> I'm running Pg 9.1 and have schema like the following:
[snip]
> I understand I should use smth like
>
> 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
Sorry for the noise.
--
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Trigger for updating view with join
@ 2013-09-04 06:09 Dmitry Morozovsky <marck@rinet.ru>
parent: Dmitry Morozovsky <marck@rinet.ru>
0 siblings, 0 replies; 3+ messages in thread
From: Dmitry Morozovsky @ 2013-09-04 06:09 UTC (permalink / raw)
To: 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 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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2013-09-04 06:09 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-09-03 23:38 Trigger for updating view with join Dmitry Morozovsky <marck@rinet.ru>
2013-09-04 05:20 ` Dmitry Morozovsky <marck@rinet.ru>
2013-09-04 06:09 ` Dmitry Morozovsky <marck@rinet.ru>
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