title: Update table data from view in PostgreSQL/PostGIS
body: <p>I am having an entity-relationship model in which I am trying to respect the normalization rules. But in order to work into QGIS through PostGIS and have only relevant data at the moment, I would like to work through some views, display these views into QGIS and possibly update the corresponding table according to the modifications made in the view.</p>
  
  <p>For example I have three tables where cable is the one containing the datas I have to work with, modele_cable a reference table (weak entity) containing some informations which would fill the view, and type_cable a weak entity of modele_table, also containing some information relevant in the view. As such :</p>
  
  <pre><code>CREATE TABLE test.cable(
        id_cable serial NOT NULL,
        id_modele_cable integer,
        geom geometry(MultiLineString,2154),
        CONSTRAINT prk_constraint_cable PRIMARY KEY (id_cable),
        CONSTRAINT fk_cable_id_modele_cable FOREIGN KEY (id_modele_cable)
            REFERENCES test.modele_cable (id_modele_cable)
      );
      CREATE TABLE test.modele_cable(
        id_modele_cable serial NOT NULL,
        id_type_cable integer,
        CONSTRAINT prk_constraint_modele_cable PRIMARY KEY (id_modele_cable),
        CONSTRAINT fk_modele_cable_id_type_cable FOREIGN KEY (id_type_cable)
            REFERENCES test.type_cable (id_type_cable)
      );
      CREATE TABLE test.type_cable(
        id_type_cable serial NOT NULL,
        nb_fo integer,
        CONSTRAINT prk_constraint_type_cable PRIMARY KEY (id_type_cable)
      );
      CREATE VIEW public.cable_ok AS 
       SELECT cable.id_cable,
          cable.designation,
          type_cable.nb_fo,
          cable.geom
         FROM test.cable
           LEFT JOIN test.modele_cable ON cable.id_modele_cable=modele_cable.id_modele_cable
           LEFT JOIN test.type_cable ON type_cable.id_type_cable=modele_cable.id_type_cable;
  </code></pre>
  
  <p>I didn't find any information on how to do what I want, nor if it is even possible, and if not what could be a workaround. I tried to make a trigger which would detect the modification in the view in PostGIS, and update the table 'cable' accordingly, but I didn't manage to do so (I successfully only once created a trigger in another context before, and quite painfully). I also noticed that it is not possible to update a view manually inside the table of the view (which sounds logical since it is a view).</p>
  
  <p>Does anyone have any clue, direction, help, experience that could help me solve this problem ?</p>
  
  <p><strong>UPDATE</strong> here is a trigger as I tried to implement it :</p>
  
  <pre><code>create or replace function tout.update_cable_view() returns trigger as 
      $body$
      begin
      select modele_cable.id_modele_cable
      into cable.id_modele_cable
      from test.cable
      inner join test.modele_cable on cable.id_modele_cable=modele_cable.id_modele_cable 
      inner join test.type_cable on modele_cable.id_type_cable=type_cable.id_type_cable, 
      public.cable_ok
      where cable_ok.nb_fo=type_cable.nb_fo;
      return null;
      end;
      $body$
      language plpgsql;
  
      create trigger t_update_cable_view
      instead of insert or update on public.cable_ok
      for each row
      execute procedure tout.update_cable_view();
  </code></pre>
  
  <p><strong>UPDATE 2</strong> here is a trigger function which should be working, but is not :</p>
  
  <pre><code>create or replace function tout.update_cable_view() returns trigger as 
  $body$
  begin   
  update test.cable
  set id_modele_cable = (select id_modele_cable from test.modele_cable 
  inner join test.type_cable on 
  
  modele_cable.id_type_cable=type_cable.id_type_cable
      where new.nb_fo=type_cable.nb_fo and old.id_cable=new.id_cable);
  return new;
  end;
  $body$
  language plpgsql;
  </code></pre>