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
_______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
