<div dir="ltr"><div><div>Matt,<br><br></div>It looks like you are on the right track with your view and rules setup. I think your primary issue may be that each of the rules is trying to act on the view to do the insert, update, delete, as opposed to acting on the underlying table.<br><br></div>For example, instead of this:<br><br><pre><code><span>--delete rule</span><span>
</span><span>create</span><span> </span><span>or</span><span> replace </span><span>rule</span><span> </span><span>"delete_label"</span><span> </span><span>as</span><span>
</span><span>on</span><span> </span><span>delete</span><span> </span><span>to</span><span> schools_district_map do instead
</span><span>delete</span><span> </span><span>from</span><span> schools_district_map
</span><span>where</span><span> oid </span><span>=</span><span> old</span><span>.</span><span>oid</span><span>;<br><br></span></code></pre><pre><code><span>Try this<br><br></span></code></pre><pre><code><span>--delete rule<br></span></code></pre><pre><code><span>create or replace rule "delete_label" as<br></span></code></pre><pre><code><span>on delete to schools_district_map do instead<br></span></code></pre><pre><code><span>delete from temp_schools_label<br></span></code></pre><pre><code><span>where oid = old.oid;<br><br></span></code></pre><div>This way, you are directing the delete command to redirect to the underlying table instead of sticking with the view itself, which will always be read only. If you change each of your rules to reflect the same, you should have better luck.<br><br></div><div>Hope that helps,<br></div><div>Russell<br></div><div><br></div></div><div class="gmail_extra"><br><div class="gmail_quote">On Fri, Sep 16, 2016 at 1:36 PM, Matthew Baker <span dir="ltr"><<a href="mailto:mattbaker@gmail.com" target="_blank">mattbaker@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div bgcolor="#FFFFFF" text="#000000">
<p><font face="Arial">Hi all,</font></p>
<p><font face="Arial">I've asked this on <a href="http://GIS.SE" target="_blank">GIS.SE</a>, to no avail:</font></p>
<p><font face="Arial">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 manually. The idea is
when the labels are updated, the placement coordinates would go
into the LABEL_X and LABEL_Y fields in the label placement
table, but the geometry of the underlying points and several
other attributes (coming from the source tables) would remain
unchanged.<br>
<br>
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):<br>
</font></p>
<pre><code><span>Could </span><span>not</span><span> </span><span>commit</span><span> changes </span><span>to</span><span> layer schools_district_map
Errors</span><span>:</span><span> ERROR</span><span>:</span><span> </span><span>1</span><span> attribute value change</span><span>(</span><span>s</span><span>)</span><span> </span><span>not</span><span> applied</span><span>.</span><span>
Provider errors</span><span>:</span><span> PostGIS error </span><span>while</span><span> changing attributes</span><span>:</span><span>
ERROR</span><span>:</span><span> infinite recursion detected </span><span>in</span><span> rules </span><span>for</span><span> relation </span><span>"schools_district_map"
</span></code><code><span></span></code></pre>
<p><font face="Arial">Here is the definition of the view :</font></p>
<pre><font face="Courier New"><code><span>CREATE</span><span> </span><span>OR</span><span> REPLACE </span><span>VIEW</span><span> </span><span>public</span><span>.</span><span>schools_district_map </span><span>AS</span><span>
</span><span>SELECT</span><span> sch</span><span>.</span><span>schnum</span><span>,</span><span>
sch</span><span>.</span><span>oid</span><span>,</span><span>
sch</span><span>.</span><span>abbreviation</span><span>,</span><span>
sch</span><span>.</span><span>school_level</span><span>,</span><span>
sch</span><span>.</span><span>geom</span><span>,</span><span>
l</span><span>.</span><span>label_x</span><span>,</span><span>
l</span><span>.</span><span>label_y
</span><span>FROM</span><span> temp_schools_label sch
</span><span>LEFT</span><span> </span><span>JOIN</span><span> district_map_labels l </span><span>ON</span><span> sch</span><span>.</span><span>schnum </span><span>=</span><span> l</span><span>.</span><span>schnum</span><span>;</span></code></font></pre>
<p><font face="Arial">And here are the rules I've applied to make
the view 'editable':</font></p>
<pre><code><span>--delete rule</span><span>
</span><span>create</span><span> </span><span>or</span><span> replace </span><span>rule</span><span> </span><span>"delete_label"</span><span> </span><span>as</span><span>
</span><span>on</span><span> </span><span>delete</span><span> </span><span>to</span><span> schools_district_map do instead
</span><span>delete</span><span> </span><span>from</span><span> schools_district_map
</span><span>where</span><span> oid </span><span>=</span><span> old</span><span>.</span><span>oid</span><span>;</span><span>
</span><span>--insert rule</span><span>
</span><span>create</span><span> </span><span>or</span><span> replace </span><span>rule</span><span> </span><span>"insert_label"</span><span> </span><span>as</span><span>
</span><span>on</span><span> </span><span>insert</span><span> </span><span>to</span><span> schools_district_map do instead
</span><span>insert</span><span> </span><span>into</span><span> schools_district_map </span><span>(</span><span>label_x</span><span>,</span><span> label_y</span><span>)</span><span>
</span><span>values</span><span> </span><span>(</span><span>new</span><span>.</span><span>label_x</span><span>,</span><span> new</span><span>.</span><span>label_y</span><span>);</span><span>
</span><span>--update rule</span><span>
</span><span>create</span><span> </span><span>or</span><span> replace </span><span>rule</span><span> </span><span>"labels_update"</span><span> </span><span>as</span><span>
</span><span>on</span><span> </span><span>UPDATE</span><span> </span><span>TO</span><span> schools_district_map do instead
</span><span>update</span><span> schools_district_map </span><span>set</span><span>
label_x </span><span>=</span><span> new</span><span>.</span><span>label_x
</span><span>,</span><span> label_y </span><span>=</span><span> new</span><span>.</span><span>label_y
</span><span>where</span><span> oid </span><span>=</span><span> new</span><span>.</span><span>oid</span><span>;</span></code></pre>
<p><font face="Arial">QGIS is then set to display the labels using
the label_x and label_y field.</font></p>
<p><font face="Arial">I used this post as a guide to build the view,
rules: <br>
</font></p>
<p><font face="Arial"><a href="http://gis.stackexchange.com/questions/88120/how-to-set-posgis-default-sequential-value-in-a-qgis-editable-view" target="_blank">http://gis.stackexchange.com/<wbr>questions/88120/how-to-set-<wbr>posgis-default-sequential-<wbr>value-in-a-qgis-editable-view</a></font></p>
<font face="Arial"></font>
<p><font face="Arial">If anyone can spot where I might have left
something out, or if there is a glaring oversight on my part, OR
if this is maybe a bad idea... let me know!</font></p>
<p><font face="Arial">Thank you!!!</font></p>
<p><font face="Arial">-Matt Baker<br>
Denver Public Schools<br>
Denver, CO<br>
</font></p>
<p><br>
</p>
</div>
<br>______________________________<wbr>_________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">http://lists.osgeo.org/<wbr>mailman/listinfo/postgis-users</a><br></blockquote></div><br></div>