title: postgresql create trigger on materialized view
body: <p>I would like to have a field (produkt) in one table that references a view (owoce).</p>
  
  <pre><code>-- see definition of 'owoce' below
  FOREIGN KEY (produkt) REFERENCES owoce(nazwa) ON DELETE RESTRICT
  </code></pre>
  
  <p>It's forbidden in PostgreSQL (9.3), so I decided to create materialized view instead (it's probably even better - faster- in this case). It's said that materialized views can be used as ordinary tables.</p>
  
  <p>My previously used ordinary view had a trigger that modified underlying table (see below), but when I change the view to materialized I got an error:</p>
  
  <blockquote>
    <p>'komplety' is not a table nor view</p>
  </blockquote>
  
  <p><strong>In Oracle it's possible to create a trigger on materialized views</strong> (<a href="https://stackoverflow.com/questions/5037139/materialized-view-with-trigger">Materialized view with trigger?</a>)<strong>, and what about PostgreSQL 9.3?
  Maybe is there any other possibility to make a reference field to view?</strong> I'd prefer to use standar view in this case.</p>
  
  <p><em>Sorry for mixing languages in my code, but my GUI directly uses table names.</em></p>
  
  <pre><code>CREATE MATERIALIZED VIEW komplety AS
      SELECT nazwa, klient
      FROM produkty
      WHERE komplet = true;
  
  CREATE OR REPLACE FUNCTION update_view_komplety()
  RETURNS TRIGGER
  LANGUAGE plpgsql
  AS $function$
     BEGIN
        IF TG_OP = 'INSERT' THEN
          INSERT INTO produkty(komplet,nazwa,klient,jm) VALUES(true,NEW.nazwa,NEW.klient,'kpl.');
          RETURN NEW;
        ELSIF TG_OP = 'UPDATE' THEN
         UPDATE produkty SET nazwa=NEW.nazwa, klient=NEW.klient WHERE nazwa=OLD.nazwa AND klient=OLD.klient AND komplet=true;
         RETURN NEW;
        ELSIF TG_OP = 'DELETE' THEN
         DELETE FROM produkty WHERE nazwa=OLD.nazwa AND klient=OLD.klient AND komplet=true;
         RETURN NULL;
        END IF;
        RETURN NEW;
      END;
  $function$;
  
  CREATE TRIGGER update_view_komplety_trig
      INSTEAD OF INSERT OR UPDATE OR DELETE ON
        komplety FOR EACH ROW EXECUTE PROCEDURE update_view_komplety();
  </code></pre>