[postgis-users] Creating an 'editable' view in PostGIS

[Russell Mercer]

Matt,

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.

For example, instead of this:

--delete rulecreate or replace rule "delete_label" as on delete to
schools_district_map do insteaddelete from schools_district_map where
oid = old.oid;

Try this

--delete rule

create or replace rule "delete_label" as

on delete to schools_district_map do instead

delete from temp_schools_label

where oid = old.oid;

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.

Hope that helps,
Russell


On Fri, Sep 16, 2016 at 1:36 PM, Matthew Baker <mattbaker at gmail.com> wrote:

> Hi all,
>
> I've asked this on GIS.SE, to no avail:
>
> 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.
>
> 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 rulecreate or replace rule "delete_label" as on delete to schools_district_map do insteaddelete from schools_district_map where oid = old.oid;
> --insert rule
> create or replace rule "insert_label" as on insert to schools_district_map do insteadinsert into schools_district_map (label_x, label_y)values (new.label_x, new.label_y);
>
> --update rulecreate 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_ywhere oid = new.oid;
>
> QGIS is then set to display the labels using the label_x and label_y field.
>
> I used this post as a guide to build the view, rules:
>
> http://gis.stackexchange.com/questions/88120/how-to-set-
> posgis-default-sequential-value-in-a-qgis-editable-view
>
> 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!
>
> Thank you!!!
>
> -Matt Baker
> Denver Public Schools
> Denver, CO
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20160916/bcb336aa/attachment.html>