Taking some code from this postthis 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.
