I am working in a database with PostgreSQL9.5/PostGIS2.3.

I am working with updatable views, which systematically include a set of rules to make them updatable. Everything works fine with 2.14, but when I try the same with 2.18 I get an error that says (this is a rough translation since my softwares are in french) :

Impossible to validate the changements for the layer_name
Errors: 1 attribute not added
Error of the data provider :
PostGIS error while adding entity : cannot execute INSERT RETURNING on the "layer_name" relation
HINT: You need a rule ON INSERT DO INSTEAD without condition and a RETURNING clause

I insist on the fact that everything works fine with 2.14, does anyone have any idea on what is happening? I really would prefer to go to 2.18, first because it will soon be the new LTR and so it would make sense to switch to this version then, and second the possibility to graphically move rule based labels is really a plus.


So I found the way to go, here is an example of a view definition :

 SELECT cable.id_cable AS "Id1",
    cable.identifiant AS "Id2",
    nb_fo.nb_fo AS "Nb_FO",
    type_cable.type AS "Cable",
   FROM cable
     LEFT JOIN nb_fo ON nb_fo.id_nb_fo = cable.id_nb_fo
     LEFT JOIN type_cable ON type_cable.id_type_cable = cable.id_type_cable;

And here the rule definition (for an INSERT, it is also needed for a DELETE, but not for an UPDATE rule) :

    ON INSERT TO test_cables DO INSTEAD ( 
    INSERT INTO cable (id_cable, identifiant, id_nb_fo, id_type_cable, geom)
  VALUES (nextval('cable_id_cable_seq'::regclass), 
          ( SELECT nb_fo.id_nb_fo
           FROM nb_fo
          WHERE new."Nb_FO" = nb_fo.nb_fo), 
          ( SELECT type_cable.id_type_cable
           FROM type_cable
          WHERE new."Cable"::text = type_cable.type::text), 
          RETURNING id_cable, identifiant, select id_nb_fo, (select type from type_cable where id_type_cable = cable.id_type_cable), geom;

Well I cannot say I understand the concept fully, it seems to me that the rule need to RETURN the view definition, it was not necessary with QGIS 2.14, it is now in 2.18, and considering the time I have ahead of me to change every of my updatable views, I'd say it is quite a painful need.

If someone has a complement of information, or see that my solution is not ideal, it is very welcome, because it stays not really clear for me.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.