Taking some code from this post, I've been trying to create an 'editable' view in PostGIS.
I'd like to store label placement properties in a separate table, build a view of the data I want to use for my map, and use QGIS to move the labels. The idea is when the labels are updated, the placement coordinate would go into the LABEL_X and LABEL_Y fields - the geometry of the underlying points and several other attributes (in the view, coming from the source tables) would remain unchanged.
However, when all is in place, QGIS throws the following error when trying to save the edits to the view (the edit session can start, labels moved, but cannot save):
Could not commit changes to layer schools_district_map Errors: ERROR: 1 attribute value change(s) not applied. Provider errors: PostGIS error while changing attributes: ERROR: infinite recursion detected in rules for relation "schools_district_map"
Here is the definition of the view:
CREATE OR REPLACE VIEW public.schools_district_map AS SELECT sch.schnum, sch.oid, sch.abbreviation, sch.school_level, sch.geom, l.label_x, l.label_y FROM temp_schools_label sch LEFT JOIN district_map_labels l ON sch.schnum = l.schnum;
And here are the rules I've applied to make the view 'editable':
--delete rule create or replace rule "delete_label" as on delete to schools_district_map do instead delete from schools_district_map where oid = old.oid; --insert rule create or replace rule "insert_label" as on insert to schools_district_map do instead insert into schools_district_map (label_x, label_y) values (new.label_x, new.label_y); --update rule create or replace rule "labels_update" as on UPDATE TO schools_district_map do instead update schools_district_map set label_x = new.label_x , label_y = new.label_y where oid = new.oid;
QGIS is then set to display the labels using the label_x and label_y field.
If anyone can spot where I might have left something out, or if there is a glaring oversight on my part, let me know.