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

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
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