[Qgis-user] can't editing view using rules

[Luca Lanteri]

Hi Magnus, thanks for the answer.

I think my rules are ok also because they work fine with QGIS 2.14.
The strange things is that the error say about an "INSERT RETURNING" when I
don't need any RETURING clause.
The problem happens only with INSERT rules. UPDATE and DELETE are ok.


this is my INSERT rule:

CREATE OR REPLACE RULE dif_spon_insert AS
    ON INSERT TO sicod.v_dif_spon DO INSTEAD  INSERT INTO sicod.dif_spon
(padr_id, sigla_rile, prog_opera, localita, sponda, alt_min, alt_max, lung,
tipologia, efficienza, stato, mat_massi, mat_c_secc, mat_c_int, mat_a_secc,
mat_a_int, mat_gabbio, mat_cls, mat_legna, i_manuten, i_nessuna, i_prolung,
i_pulizia, i_ricostru, i_sottomur, i_svuotam, so_dissest, so_interra,
so_scalzat, so_sifonat, note, data_rilev, desc_fonte, font_elab, font_sopr,
font_altro, geom)
  VALUES (new.padr_id, new.sigla_rile, new.prog_opera, new.localita,
new.sponda, new.alt_min, new.alt_max, new.lung, new.tipologia,
new.efficienza, new.stato, new.mat_massi, new.mat_c_secc, new.mat_c_int,
new.mat_a_secc, new.mat_a_int, new.mat_gabbio, new.mat_cls, new.mat_legna,
new.i_manuten, new.i_nessuna, new.i_prolung, new.i_pulizia, new.i_ricostru,
new.i_sottomur, new.i_svuotam, new.so_dissest, new.so_interra,
new.so_scalzat, new.so_sifonat, new.note, new.data_rilev, new.desc_fonte,
new.font_elab, new.font_sopr, new.font_altro, new.geom);



2017-01-16 7:56 GMT+01:00 Karl-Magnus Jönsson <
Karl-Magnus.Jonsson at kristianstad.se>:

> Hi!
>
> Are you sure it isn’t an issue with the view? I have a view with rules and
> it work in 2.18. When you have conditions in the rule you must have another
> rule on the same event that is unconditional. Check the last paragraph in
> the description : https://www.postgresql.org/docs/current/static/sql-
> createrule.html
>
>
>
> In my case I have a unconditional rule that does nothing:
>
>
>
> CREATE OR REPLACE RULE v_prislistor_upd_ins AS
>
>     ON UPDATE TO park.v_prislistor
>
>    WHERE old.id IS NULL DO INSTEAD  INSERT INTO park.priser (kod, pris,
> utforare)
>
>   VALUES (new.kod, new.pris, new.utforare);
>
>
>
> CREATE OR REPLACE RULE v_prislistor_upd_nothing AS
>
>     ON UPDATE TO park.v_prislistor DO INSTEAD NOTHING;
>
>
>
> CREATE OR REPLACE RULE v_prislistor_upd_upd AS
>
>     ON UPDATE TO park.v_prislistor
>
>    WHERE old.id IS NOT NULL DO INSTEAD  UPDATE park.priser SET pris =
> new.pris
>
>   WHERE priser.id = old.id;
>
>
>
> *Karl-Magnus Jönsson*
>
>
>
> *Från:* Qgis-user [mailto:qgis-user-bounces at lists.osgeo.org] *För *Luca
> Lanteri
> *Skickat:* den 13 januari 2017 16:39
> *Till:* qgis-user
> *Ämne:* [Qgis-user] can't editing view using rules
>
>
>
> Hi to all,
>
>
>
> starting fron QIS 2.16 I can't edit anymore postgres views with rules.
> When I try to insert a new feature I have this error [0]. All works fine
> with QGIS 2.14
>
>
>
> Can anyone confirm the issue ?
>
> Thanks
>
>
>
> Luca
>
>
>
> [0]
>
> Impossibile applicare le modifiche al vettore v_frane_pol_full
>
>
>
> Errori: ERRORE: 1 geometria non aggiunta.
>
> Errori della sorgente dati:
>
> Errore PostGIS nell'aggiunta delle geometrie: ERROR: cannot perform INSERT
> RETURNING on relation "v_frane_pol_full"
>
> HINT: You need an unconditional ON INSERT DO INSTEAD rule with a RETURNING
> clause.
>
>
>
> _______________________________________________
> Qgis-user mailing list
> Qgis-user at lists.osgeo.org
> List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
> Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20170116/b7e81626/attachment.html>