[postgis-users] auto update tables of a view with instead of trigger

[Garret W]

Hi guys,
I know this is the postgis list, but I thought many of you might be able to
help me out.

Im trying to update my tables thru the views I've created using triggers.
Most views contain 3 tables or more, so do not update without the use of a
trigger. This would make it easier for my team to update our current db
tables.

Ive looked around on StackExchange and found a few examples, but Im unable
to come up with something that works for my use case. Ive listed my
question here:
https://stackoverflow.com/questions/48430516/automate-update-columns-in-a-view-with-trigger-function-in-plpgsql

Here is what I posted:

Im attempting to update several columns in a view very similarly to this
post[1]. The difference is that Im combining in some cases up to 3 tables,
and for each update I would like the trigger to do an INSTEAD OF UPDATE on
the appropriate table.

>From the Documentation I've figured out how to update columns by hand, but
with some views having up to 20 or 30 columns this will get tedious. I
would like to automate the process similar to the previously mentioned post.

My code to update a column at a time

CREATE or REPLACE function a.poi_view_update()
RETURNS trigger as $$
  BEGIN
    IF (OLD.col_a != NEW.col_a) THEN
      UPDATE b.poi SET col_a = NEW.col_a
      WHERE poi_id = OLD.poi_id;
      IF NOT FOUND THEN RETURN NULL; END IF;
      RETURN NEW;
    END IF;
    RAISE NOTICE 'No update performed';
    RETURN NEW;
  END;$$ LANGUAGE plpgsql;

CREATE TRIGGER poi_update_trigger
INSTEAD OF UPDATE ON a.poi_view for each row execute procedure
a.poi_view_update();


My tables are:

CREATE TABLE b.poi(
  poi_id integer,
  col_a integer DEFAULT 0,
  col_b integer DEFAULT 0,
  col_c integer DEFAULT 0,
  ...
  ...
 );
CREATE TABLE b.points(
  poi_id integer,
  col_d varchar(50),
  col_e varchar(50),
  col_f varchar(5),
  ...
  ...
 );
CREATE VIEW a.poi_view as SELECT poi.poi_id,
poi.col_a,
poi.col_b,
points.col_d,
points.col_e FROM b.poiJOIN b.pointsON poi.poi_id=points.poi_id;

* Note that not all columns are included in the new view (poi.col_c, and
points.col_f)

[1]
https://stackoverflow.com/questions/15343075/update-multiple-columns-in-a-trigger-function-in-plpgsql/15351196#15351196


Thanks,
Garret
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20180208/71dacb47/attachment.html>