Date: Wed, 4 Sep 2013 10:09:17 +0400 (MSK)
From: Dmitry Morozovsky <marck@rinet.ru>
To: pgsql-sql@postgresql.org
Subject: Re: Trigger for updating view with join
In-Reply-To: <alpine.BSF.2.00.1309040919030.75311@woozle.rinet.ru>
Message-ID: <alpine.BSF.2.00.1309041002390.75311@woozle.rinet.ru>
References: <alpine.BSF.2.00.1309040335020.75311@woozle.rinet.ru>
 <alpine.BSF.2.00.1309040919030.75311@woozle.rinet.ru>
User-Agent: Alpine 2.00 (BSF 1167 2008-08-23)
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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