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 <lklant...@gmail.com
> <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","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
>     <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_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
>     <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 <mailto:Qgis-user@lists.osgeo.org>
>     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
> Qgis-user@lists.osgeo.org
> List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
> Unsubscribe: https://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

Reply via email to