[Qgis-developer] problem with ON INSERT TO ... DO INSTEAD postgres rules

[Luca Lanteri]

I resolve the problem by adding the RETURNING clause in the INSERT RULES
fololwed by all single field name and with a dummy value where I can't
returning the real value, eg:

REATE OR REPLACE RULE insert_generalita AS
    ON INSERT TO sifrap.v_frane_pol_full DO INSTEAD  INSERT INTO
sifrap.iffi_pol (idfrana, tipopol, the_geom)
  VALUES (new.idfrana, new.tipopol, st_multi(new.the_geom))
  RETURNING
    iffi_pol.gid,
    iffi_pol.idfrana,
    ''::text AS idesteso,
    1::smallint tipopol,
''::CHARACTER VARYING tipo_poligono,
1::smallint tipo,
[...].

It seem all works fine.
Thanks for the tips !

Luca


2017-01-23 18:45 GMT+01:00 Luca Lanteri <lklanteri at gmail.com>:

> Hi Matthias,
>
> thanks for your answer. Sorry but I missed your previous mail.
> Now I've seen that in your example you used TRIGGERS to update your data,
> instead in my case I use view RULES.
> I try to add "RETURNING new" clause in the rules but it doesn't work.
>
> I can try to change all my code and substitute RULES with TRIGGERS but I
> still hope there is another way to maintain the possibility of using rules.
> Is It possible that I'm just missing a simply solution because I don't
> know well the using of RETURNING in postgres views?
>
> thanks
> Luca
>
>
> 2017-01-23 18:15 GMT+01:00 Matthias Kuhn <matthias at opengis.ch>:
>
>> Hi Luca,
>>
>> this was a deliberate change to allow QGIS to directly use the newly
>> inserted features (e.g. add child records to a newly created feature).
>>
>> To make the views compatible with this, all you need to do is add a
>>
>>     RETURN NEW;
>>
>> to the end of the insert function (I think). Have a look at the views
>> which I linked in my response in the other thread.
>>
>> We could possibly add a flag to specify in which cases we need the new
>> primary key and in which cases not.
>> But I somehow suspect, that in the end we will need to specify this in
>> some often-used code (e.g. add feature form) and in the end one will
>> still be required to update all views and we have some half-baked
>> pointless workaround code inside QGIS.
>>
>> Hope that helps for you!
>> Matthias
>>
>> On 01/23/2017 04:19 PM, Luca Lanteri wrote:
>> > Hi to all,
>> >
>> > after a short disscussion in qgis user list I've opened this ticket [0],
>> > because starting from QGIS 2.16 I can't insert new feature in postgres
>> > views using rules. When I try to insert a new feature I have this error
>> > [1]. All works fine with QGIS 2.14. The problem happens only with INSERT
>> > rules. UPDATE and DELETE are ok.
>> >
>> > I saw that for simple rules I can add a RETURNING * clause and all works
>> > fine, but I have a lot of complex view that use many tables, so
>> > RETURNING * clause doesn't work and compose the clause it's really hard.
>> >
>> > Then I have some question:
>> > 1) Is it really an issue or the problem is due to a new functionality of
>> > QGIS ?
>> > 2) In the first case, it could be considered a regression for it works
>> > on QGIS 2.14 ?
>> > 3) Is there a simple way to modify my views by adding a RETURNING clause
>> > without specify all returning field (* doesn't work) ?
>> >
>> > I love to know if it's a deliberate change or it's just a bug,so I can
>> > choose if I have to modify all my views, in order to allow me to migrate
>> > to QGIS 2.18.
>> >
>> > Many thanks in advance
>> > Regards
>> > Luca
>> >
>> >
>> > [0] https://hub.qgis.org/issues/16083
>> >
>> > [1] 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-developer mailing list
>> > Qgis-developer at lists.osgeo.org
>> > List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
>> > Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer
>> >
>> _______________________________________________
>> Qgis-developer mailing list
>> Qgis-developer at lists.osgeo.org
>> List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
>> Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer
>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-developer/attachments/20170124/5326586e/attachment.html>