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:[email protected]] *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 <[email protected] > <mailto:[email protected]>>: > > 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","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 > <[email protected] > <mailto:[email protected]>>: > > 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:*[email protected] <mailto:[email protected]> > [mailto:[email protected] <mailto:[email protected]>] *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 > <[email protected] > <mailto:[email protected]>>: > > 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:[email protected] > <mailto:[email protected]>] *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 > [email protected] <mailto:[email protected]> > List info: http://lists.osgeo.org/mailman/listinfo/qgis-user > Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user > > > > > _______________________________________________ > Qgis-user mailing list > [email protected] <mailto:[email protected]> > List info: https://lists.osgeo.org/mailman/listinfo/qgis-user > <https://lists.osgeo.org/mailman/listinfo/qgis-user> > Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user > <https://lists.osgeo.org/mailman/listinfo/qgis-user> > > > > > > _______________________________________________ > Qgis-user mailing list > [email protected] > List info: https://lists.osgeo.org/mailman/listinfo/qgis-user > Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
_______________________________________________ Qgis-user mailing list [email protected] List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
