[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@lists.osgeo.org List info: http://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] can't editing view using rules
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-boun...@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@lists.osgeo.org List info: http://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] can't editing view using rules
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.jons...@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-boun...@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@lists.osgeo.org > List info: http://lists.osgeo.org/mailman/listinfo/qgis-user > Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user > ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: http://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] can't editing view using rules
Hi! Ok. When I try another view with an INSERT rule I have the same problem in 2.18.2. Works in 2.14. UPDATE and DELETE are ok as well. Can we check the resulting SQL from QGIS? Must be some difference there. I don’t know if we can turn on logging on our database. Karl-Magnus Jönsson Från: mesca...@gmail.com [mailto:mesca...@gmail.com] För Luca Lanteri Skickat: den 16 januari 2017 14:44 Till: Karl-Magnus Jönsson Kopia: qgis-user Ämne: Re: [Qgis-user] can't editing view using rules 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 mailto:karl-magnus.jons...@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<http://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<http://old.id> IS NOT NULL DO INSTEAD UPDATE park.priser SET pris = new.pris WHERE priser.id<http://priser.id> = old.id<http://old.id>; Karl-Magnus Jönsson Från: Qgis-user [mailto:qgis-user-boun...@lists.osgeo.org<mailto:qgis-user-boun...@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@lists.osgeo.org<mailto:Qgis-user@lists.osgeo.org> List info: http://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] can't editing view using rules
This is the postgres log [0]: In fact the 2.18 query add a RETURNING clause that is non present in QGIS 2.14 I've just opened this ticket: http://hub.qgis.org/issues/16083 Luca [0] With QGIS 2.18 2017-01-17 11:09:32 CET [25093]: [5-1] user=l_lanteri,db=sigeo,app=QGIS STATEMENT: INSERT INTO "sicod"."v_dif_spon"("geom","oper_id","padr_id","sigla_rile","cod_opera","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","data_rilev","note","utente_crea","data_crea","utente_mod","data_mod","fonte","desc_fonte","font_elab","font_sopr","font_altro") VALUES (st_multi(st_geomfromwkb($1::bytea,32632)),$2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'0',NULL,'0',NULL,'0','0','0',NULL,'0','0','0','0','0','0','0','0','0','0','0','2000-01-01',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL) RETURNING "oper_id" With QGIS 2.14 2017-01-17 11:12:01 CET [26026]: [3-1] user=l_lanteri,db=sigeo,app=QGIS LOG: execute addfeatures: INSERT INTO "sicod"."v_dif_spon"("geom","oper_id","padr_id","sigla_rile","cod_opera","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","data_rilev","note","utente_crea","data_crea","utente_mod","data_mod","fonte","desc_fonte","font_elab","font_sopr","font_altro") VALUES (st_multi(st_geomfromwkb($1::bytea,32632)),$2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'0',NULL,'0',NULL,'0','0','0',NULL,'0','0','0','0','0','0','0','0','0','0','0','2000-01-01',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL) 2017-01-17 10:41 GMT+01:00 Karl-Magnus Jönsson < karl-magnus.jons...@kristianstad.se>: > Hi! > > Ok. When I try another view with an INSERT rule I have the same problem in > 2.18.2. Works in 2.14. UPDATE and DELETE are ok as well. Can we check the > resulting SQL from QGIS? Must be some difference there. I don’t know if we > can turn on logging on our database. > > > > *Karl-Magnus Jönsson* > > > > *Från:* mesca...@gmail.com [mailto:mesca...@gmail.com] *För *Luca Lanteri > *Skickat:* den 16 januari 2017 14:44 > *Till:* Karl-Magnus Jönsson > *Kopia:* qgis-user > *Ämne:* Re: [Qgis-user] can't editing view using rules > > > > 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 (n
Re: [Qgis-user] can't editing view using rules
Could it be connected with the new feature to "execute expressions on the server side if possible"? Then you should get the new primay key from the database before you save edits so you can use it for childs in subforms etc. /Karl-Magnus 17 jan 2017 kl. 11:16 skrev Luca Lanteri mailto:lklant...@gmail.com>>: This is the postgres log [0]: In fact the 2.18 query add a RETURNING clause that is non present in QGIS 2.14 I've just opened this ticket: http://hub.qgis.org/issues/16083 Luca [0] With QGIS 2.18 2017-01-17 11:09:32 CET [25093]: [5-1] user=l_lanteri,db=sigeo,app=QGIS STATEMENT: INSERT INTO "sicod"."v_dif_spon"("geom","oper_id","padr_id","sigla_rile","cod_opera","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","data_rilev","note","utente_crea","data_crea","utente_mod","data_mod","fonte","desc_fonte","font_elab","font_sopr","font_altro") VALUES (st_multi(st_geomfromwkb($1::bytea,32632)),$2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'0',NULL,'0',NULL,'0','0','0',NULL,'0','0','0','0','0','0','0','0','0','0','0','2000-01-01',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL) RETURNING "oper_id" With QGIS 2.14 2017-01-17 11:12:01 CET [26026]: [3-1] user=l_lanteri,db=sigeo,app=QGIS LOG: execute addfeatures: INSERT INTO "sicod"."v_dif_spon"("geom","oper_id","padr_id","sigla_rile","cod_opera","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","data_rilev","note","utente_crea","data_crea","utente_mod","data_mod","fonte","desc_fonte","font_elab","font_sopr","font_altro") VALUES (st_multi(st_geomfromwkb($1::bytea,32632)),$2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'0',NULL,'0',NULL,'0','0','0',NULL,'0','0','0','0','0','0','0','0','0','0','0','2000-01-01',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL) 2017-01-17 10:41 GMT+01:00 Karl-Magnus Jönsson mailto:karl-magnus.jons...@kristianstad.se>>: Hi! Ok. When I try another view with an INSERT rule I have the same problem in 2.18.2. Works in 2.14. UPDATE and DELETE are ok as well. Can we check the resulting SQL from QGIS? Must be some difference there. I don’t know if we can turn on logging on our database. Karl-Magnus Jönsson Från: mesca...@gmail.com<mailto:mesca...@gmail.com> [mailto:mesca...@gmail.com<mailto:mesca...@gmail.com>] För Luca Lanteri Skickat: den 16 januari 2017 14:44 Till: Karl-Magnus Jönsson Kopia: qgis-user Ämne: Re: [Qgis-user] can't editing view using rules 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_sott
Re: [Qgis-user] can't editing view using rules
Hi again! I've made some more testing. By adding a RETURNING clause to the rule I succeeded to insert features. But since it appears that the RETURNING clause has to return all the attributes in the view and my view is complex and consists of attributes from several tables I couldn't just add RETURNING * (It will just return the attributes from the table inserted into). I had to list all fields in right order, make some attributes up and cast them to the right data type. Like this: CREATE OR REPLACE RULE lekplatsredskap_ins AS ON INSERT TO park.v_lekplatsredskap DO INSTEAD INSERT INTO park.skotselpunkt (kod, antal, inkopsar, fabrikat, nr, fritext, utforare, geom) VALUES (new.kod, new.antal, new.inkopsar, new.fabrikat, new.nr, new.fritext, new.utforare, new.geom) RETURNING skotselobj_id,1,kod, 'text'::character varying,1234,4567,'plkod'::text, antal, inkopsar, fabrikat, nr, fritext,utforare,'2017-01-18'::date,'b_resultat'::character varying,'status'::character varying,geom ; Please let me know if you find a way to solve this better or a way to turn this feature off in QGIS. Karl-Magnus Jönsson Från: Qgis-user [mailto:qgis-user-boun...@lists.osgeo.org] För Karl-Magnus Jönsson Skickat: den 17 januari 2017 17:32 Till: Luca Lanteri Kopia: qgis-user Ämne: Re: [Qgis-user] can't editing view using rules Could it be connected with the new feature to "execute expressions on the server side if possible"? Then you should get the new primay key from the database before you save edits so you can use it for childs in subforms etc. /Karl-Magnus 17 jan 2017 kl. 11:16 skrev Luca Lanteri mailto:lklant...@gmail.com>>: This is the postgres log [0]: In fact the 2.18 query add a RETURNING clause that is non present in QGIS 2.14 I've just opened this ticket: http://hub.qgis.org/issues/16083 Luca [0] With QGIS 2.18 2017-01-17 11:09:32 CET [25093]: [5-1] user=l_lanteri,db=sigeo,app=QGIS STATEMENT: INSERT INTO "sicod"."v_dif_spon"("geom","oper_id","padr_id","sigla_rile","cod_opera","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","data_rilev","note","utente_crea","data_crea","utente_mod","data_mod","fonte","desc_fonte","font_elab","font_sopr","font_altro") VALUES (st_multi(st_geomfromwkb($1::bytea,32632)),$2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'0',NULL,'0',NULL,'0','0','0',NULL,'0','0','0','0','0','0','0','0','0','0','0','2000-01-01',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL) RETURNING "oper_id" With QGIS 2.14 2017-01-17 11:12:01 CET [26026]: [3-1] user=l_lanteri,db=sigeo,app=QGIS LOG: execute addfeatures: INSERT INTO "sicod"."v_dif_spon"("geom","oper_id","padr_id","sigla_rile","cod_opera","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","data_rilev","note","utente_crea","data_crea","utente_mod","data_mod","fonte","desc_fonte","font_elab","font_sopr","font_altro") VALUES (st_multi(st_geomfromwkb($1::bytea,32632)),$2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'0',NULL,'0',NULL,'0','0','0',NULL,'0','0','0','0','0','0','0','0','0','0','0','2000-01-01',NUL
Re: [Qgis-user] can't editing view using rules
Hi Karl-Magnus, What datatype is your primary key? I think returning is only required for 64 bit integer or compound primary keys. For an example of a complex view with return, have a look here: https://github.com/QGEP/datamodel/blob/master/view/vw_qgep_wastewater_structure.sql Best regards Matthias On 18/01/17 09:11, Karl-Magnus Jönsson wrote: > > Hi again! > I’ve made some more testing. By adding a RETURNING clause to the rule > I succeeded to insert features. But since it appears that the > RETURNING clause has to return all the attributes in the view and my > view is complex and consists of attributes from several tables I > couldn’t just add RETURNING * (It will just return the attributes from > the table inserted into). I had to list all fields in right order, > make some attributes up and cast them to the right data type. Like this: > > > > CREATE OR REPLACE RULE lekplatsredskap_ins AS > > ON INSERT TO park.v_lekplatsredskap DO INSTEAD INSERT INTO > park.skotselpunkt (kod, antal, inkopsar, fabrikat, nr, fritext, > utforare, geom) > > VALUES (new.kod, new.antal, new.inkopsar, new.fabrikat, new.nr, > new.fritext, new.utforare, new.geom) > > RETURNING skotselobj_id,1,kod, 'text'::character > varying,1234,4567,'plkod'::text, antal, inkopsar, fabrikat, nr, > fritext,utforare,'2017-01-18'::date,'b_resultat'::character > varying,'status'::character varying,geom ; > > > > Please let me know if you find a way to solve this better or a way to > turn this feature off in QGIS. > > > > *Karl-Magnus Jönsson* > > > > *Från:*Qgis-user [mailto:qgis-user-boun...@lists.osgeo.org] *För > *Karl-Magnus Jönsson > *Skickat:* den 17 januari 2017 17:32 > *Till:* Luca Lanteri > *Kopia:* qgis-user > *Ämne:* Re: [Qgis-user] can't editing view using rules > > > > Could it be connected with the new feature to "execute expressions on > the server side if possible"? Then you should get the new primay key > from the database before you save edits so you can use it for childs > in subforms etc. > > /Karl-Magnus > > > 17 jan 2017 kl. 11:16 skrev Luca Lanteri <mailto:lklant...@gmail.com>>: > > This is the postgres log [0]: > > In fact the 2.18 query add a RETURNING clause that is non present > in QGIS 2.14 > > > > I've just opened this ticket: http://hub.qgis.org/issues/16083 > <http://hub.qgis.org/issues/16083> > > > > Luca > > > > [0] > > With QGIS 2.18 > > 2017-01-17 11:09:32 CET [25093]: [5-1] > user=l_lanteri,db=sigeo,app=QGIS STATEMENT: INSERT INTO > > "sicod"."v_dif_spon"("geom","oper_id","padr_id","sigla_rile","cod_opera","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","data_rilev","note","utente_crea","data_crea","utente_mod","data_mod","fonte","desc_fonte","font_elab","font_sopr","font_altro") > VALUES > > (st_multi(st_geomfromwkb($1::bytea,32632)),$2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'0',NULL,'0',NULL,'0','0','0',NULL,'0','0','0','0','0','0','0','0','0','0','0','2000-01-01',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL) > RETURNING "oper_id" > > > > With QGIS 2.14 > > 2017-01-17 11:12:01 CET [26026]: [3-1] > user=l_lanteri,db=sigeo,app=QGIS LOG: execute addfeatures: INSERT > INTO > > "sicod"."v_dif_spon"("geom","oper_id","padr_id","sigla_rile","cod_opera","prog_opera","localita","sponda","alt_min","alt_max","lung","tipologia","efficienza","stato","mat_massi","mat_c_secc","mat_c_int","mat_a_secc",
Re: [Qgis-user] can't editing view using rules
Integer (4 bytes). I'll have a look at the view. Karl-Magnus Jönsson Från: Qgis-user [mailto:qgis-user-boun...@lists.osgeo.org] För Matthias Kuhn Skickat: den 18 januari 2017 09:21 Till: qgis-user@lists.osgeo.org Ämne: Re: [Qgis-user] can't editing view using rules Hi Karl-Magnus, What datatype is your primary key? I think returning is only required for 64 bit integer or compound primary keys. For an example of a complex view with return, have a look here: https://github.com/QGEP/datamodel/blob/master/view/vw_qgep_wastewater_structure.sql Best regards Matthias On 18/01/17 09:11, Karl-Magnus Jönsson wrote: Hi again! I've made some more testing. By adding a RETURNING clause to the rule I succeeded to insert features. But since it appears that the RETURNING clause has to return all the attributes in the view and my view is complex and consists of attributes from several tables I couldn't just add RETURNING * (It will just return the attributes from the table inserted into). I had to list all fields in right order, make some attributes up and cast them to the right data type. Like this: CREATE OR REPLACE RULE lekplatsredskap_ins AS ON INSERT TO park.v_lekplatsredskap DO INSTEAD INSERT INTO park.skotselpunkt (kod, antal, inkopsar, fabrikat, nr, fritext, utforare, geom) VALUES (new.kod, new.antal, new.inkopsar, new.fabrikat, new.nr, new.fritext, new.utforare, new.geom) RETURNING skotselobj_id,1,kod, 'text'::character varying,1234,4567,'plkod'::text, antal, inkopsar, fabrikat, nr, fritext,utforare,'2017-01-18'::date,'b_resultat'::character varying,'status'::character varying,geom ; Please let me know if you find a way to solve this better or a way to turn this feature off in QGIS. Karl-Magnus Jönsson Från: Qgis-user [mailto:qgis-user-boun...@lists.osgeo.org] För Karl-Magnus Jönsson Skickat: den 17 januari 2017 17:32 Till: Luca Lanteri Kopia: qgis-user Ämne: Re: [Qgis-user] can't editing view using rules Could it be connected with the new feature to "execute expressions on the server side if possible"? Then you should get the new primay key from the database before you save edits so you can use it for childs in subforms etc. /Karl-Magnus 17 jan 2017 kl. 11:16 skrev Luca Lanteri mailto:lklant...@gmail.com>>: This is the postgres log [0]: In fact the 2.18 query add a RETURNING clause that is non present in QGIS 2.14 I've just opened this ticket: http://hub.qgis.org/issues/16083 Luca [0] With QGIS 2.18 2017-01-17 11:09:32 CET [25093]: [5-1] user=l_lanteri,db=sigeo,app=QGIS STATEMENT: INSERT INTO "sicod"."v_dif_spon"("geom","oper_id","padr_id","sigla_rile","cod_opera","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","data_rilev","note","utente_crea","data_crea","utente_mod","data_mod","fonte","desc_fonte","font_elab","font_sopr","font_altro") VALUES (st_multi(st_geomfromwkb($1::bytea,32632)),$2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'0',NULL,'0',NULL,'0','0','0',NULL,'0','0','0','0','0','0','0','0','0','0','0','2000-01-01',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL) RETURNING "oper_id" With QGIS 2.14 2017-01-17 11:12:01 CET [26026]: [3-1] user=l_lanteri,db=sigeo,app=QGIS LOG: execute addfeatures: INSERT INTO "sicod"."v_dif_spon"("geom","oper_id","padr_id","sigla_rile","cod_opera","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_sif
Re: [Qgis-user] can't editing view using rules
Hi Karl, I've tried to run the same way but also my views are complex and I've a huge number of view so it's a big deal to change all my views. I think the issue should be considered a regression. I've seen that the ticket was automatically assigned to Jurgen Fisher by the system, I think as PostGIS Data Provider maintenancer. I Hope to have some news. Thanks Luca 2017-01-18 9:11 GMT+01:00 Karl-Magnus Jönsson < karl-magnus.jons...@kristianstad.se>: > Hi again! > I’ve made some more testing. By adding a RETURNING clause to the rule I > succeeded to insert features. But since it appears that the RETURNING > clause has to return all the attributes in the view and my view is complex > and consists of attributes from several tables I couldn’t just add > RETURNING * (It will just return the attributes from the table inserted > into). I had to list all fields in right order, make some attributes up and > cast them to the right data type. Like this: > > > > CREATE OR REPLACE RULE lekplatsredskap_ins AS > > ON INSERT TO park.v_lekplatsredskap DO INSTEAD INSERT INTO > park.skotselpunkt (kod, antal, inkopsar, fabrikat, nr, fritext, utforare, > geom) > > VALUES (new.kod, new.antal, new.inkopsar, new.fabrikat, new.nr, > new.fritext, new.utforare, new.geom) > > RETURNING skotselobj_id,1,kod, 'text'::character > varying,1234,4567,'plkod'::text, antal, inkopsar, fabrikat, nr, > fritext,utforare,'2017-01-18'::date,'b_resultat'::character > varying,'status'::character varying,geom ; > > > > Please let me know if you find a way to solve this better or a way to turn > this feature off in QGIS. > > > > *Karl-Magnus Jönsson* > > > > *Från:* Qgis-user [mailto:qgis-user-boun...@lists.osgeo.org] *För *Karl-Magnus > Jönsson > *Skickat:* den 17 januari 2017 17:32 > *Till:* Luca Lanteri > > *Kopia:* qgis-user > *Ämne:* Re: [Qgis-user] can't editing view using rules > > > > Could it be connected with the new feature to "execute expressions on the > server side if possible"? Then you should get the new primay key from the > database before you save edits so you can use it for childs in subforms etc. > > /Karl-Magnus > > > 17 jan 2017 kl. 11:16 skrev Luca Lanteri : > > This is the postgres log [0]: > > In fact the 2.18 query add a RETURNING clause that is non present in QGIS > 2.14 > > > > I've just opened this ticket: http://hub.qgis.org/issues/16083 > > > > Luca > > > > [0] > > With QGIS 2.18 > > 2017-01-17 11:09:32 CET [25093]: [5-1] user=l_lanteri,db=sigeo,app=QGIS > STATEMENT: INSERT INTO "sicod"."v_dif_spon"("geom"," > oper_id","padr_id","sigla_rile","cod_opera","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","data_rilev","note"," > utente_crea","data_crea","utente_mod","data_mod","fonte" > ,"desc_fonte","font_elab","font_sopr","font_altro") VALUES > (st_multi(st_geomfromwkb($1::bytea,32632)),$2,NULL,NULL, > NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'0',NULL,' > 0',NULL,'0','0','0',NULL,'0','0','0','0','0','0','0','0','0' > ,'0','0','2000-01-01',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL) > RETURNING "oper_id" > > > > With QGIS 2.14 > > 2017-01-17 11:12:01 CET [26026]: [3-1] user=l_lanteri,db=sigeo,app=QGIS > LOG: execute addfeatures: INSERT INTO "sicod"."v_dif_spon"("geom"," > oper_id","padr_id","sigla_rile","cod_opera","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_i