[Qgis-developer] Grouping insert/update when sending to PostGIS

[Rémi Cura]

Hey dear list,

I recently stumbled upon a strange behaviour of QGIS.

With a simple point postgis layer.

When making several edit on the layer, then saving the edits.
All the edits are send one by one to the database (though in the same
transaction).

It would hold much benefits to group those insert/update per table before
sending it, and it may be easy to do.

A pure SQL solution :
instead of doing for instance :
---------------------
--adding stuff
INSERT INTO my_table (geom) VALUES ('POINT(1,2)');
INSERT INTO my_table (geom) VALUES ('POINT(3,4)');
--updating stuff
UPDATE my_table SET geom = 'POINT(5,6)' WHERE id = 1 ;
UPDATE my_table SET geom = 'POINT(7,8)' WHERE id = 2 ;
--deleting stuff
DELETE FROM my_table WHERE id = 3 ;
DELETE FROM my_table WHERE id = 4 ;
----------------------

We could do
----------------------
WITH inserting AS (
    INSERT INTO my_table (geom) VALUES ('POINT(1,2)')
)
,to_update AS (
    SELECT 1 AS id, 'POINT(5,6)' AS geom
    UNION ALL
    SELECT 2 , 'POINT(7,8)'
)
, updating AS (
    UPDATE my_table SET geom = t_o.geom
    FROM update AS t_o
    WHERE my_table.id = t_o.id
    RETURNING 1
)
, deleting AS (
    DELETE FROM my_table
    WHERE id = ANY (ARRAY[3,4])
    RETURNING 1
)
SELECT 1
FROM deleting ;
--------------------


Speed may benefit from it, but much more important, all the action would be
in one statement, which would permit to use STATEMENT TRIGGER in postgis
database.

Cheers,
Rémi-C
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-developer/attachments/20150528/4bf79c03/attachment.html>