Thread: Trigger for updating view with join

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 ***
------------------------------------------------------------------------

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 ***
------------------------------------------------------------------------

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 ***
------------------------------------------------------------------------