TransWikia.com

Commit error when splitting several polygons with "array type" attribute (PostGIS / QGIS)

Geographic Information Systems Asked by A. Jean on February 1, 2021

I have a simple PostGIS table :

CREATE TABLE public.elem_p
(
    gid integer NOT NULL DEFAULT nextval('elem_p_gid_seq'::regclass),
    liste character varying[] COLLATE pg_catalog."default" NOT NULL,
    geom geometry(MultiPolygon,2154),
    CONSTRAINT elem_p_pkey PRIMARY KEY (gid)
)

The ‘liste’ field is array-type ;

When I split polygons one-by-one with the QGIS splitting tool, there is no problem.

When I split several polygons that all have at least 2 dimension in the array type "liste", there is no problem …

BUT when I split several polygons that have less than 2 dimensions in the array type "liste" (0 or 1) I have an error like this :

Could not commit changes to layer elem_p

Errors: SUCCESS: 2 geometries were changed. ERROR: 2 feature(s) not
added.
Provider errors:
PostGIS error while adding features: ERREUR: tableau litéral mal formé : « abcd »
DETAIL: La valeur du tableau doit commencer avec « { » ou avec l’information de la
dimension.

(raw translation from french : "wrongly build array : "abcd" ; DETAIL : array value must start with "{" or with information about dimension)

enter image description here

EDIT :
The whole new script :

/* TABLE */ 
DROP TABLE IF EXISTS elem_p CASCADE ; CREATE TABLE elem_p (gid serial, liste varchar[], geom geometry(multipolygon, 2154)) ;
INSERT INTO elem_p (gid, liste, geom) 
VALUES (nextval('elem_p_gid_seq'::regclass), '{E-1, E-5}', ST_MULTI(ST_SETSRID(st_geomfromtext('POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))'), 2154)))
   ,(nextval('elem_p_gid_seq'::regclass), '{E-1, E-7}', ST_MULTI(ST_SETSRID(st_geomfromtext('POLYGON((10 10, 6 6, 6 7, 6 8, 7 10 , 9 10, 10 10))'), 2154)))
   ,(nextval('elem_p_gid_seq'::regclass), NULL, ST_MULTI(ST_SETSRID(st_geomfromtext('POLYGON((0 10, -3 10, -3 8, -5 5, 0 4, 0 10))'), 2154))) ;
UPDATE elem_p  SET liste = '{E-30}'  WHERE gid = 2 ; 

/* VIEW  */
CREATE OR REPLACE VIEW v_elem_p AS (SELECT * FROM elem_p) ;

/*  FUNCTION  */
DROP FUNCTION IF EXISTS maj_elem_p() CASCADE ;
CREATE FUNCTION maj_elem_p() RETURNS TRIGGER AS 
$$   BEGIN 
IF TG_OP = 'INSERT' THEN INSERT INTO elem_p (liste, geom)  VALUES(COALESCE((NEW.liste::varchar)::varchar[], '{}'::varchar[]), NEW.geom); RETURN NEW; END IF;
IF TG_OP = 'UPDATE' THEN UPDATE elem_p SET (liste, geom) = (COALESCE((NEW.liste::varchar)::varchar[], '{}'::varchar[]), NEW.geom) WHERE gid = NEW.gid; RETURN NEW ; END IF ;
END;  
$$  LANGUAGE PLPGSQL ;

/* TRIGGER  */ 
DROP TRIGGER IF EXISTS tg_maj_elem_p  ON v_elem_p ; CREATE TRIGGER tg_maj_elem_p INSTEAD OF INSERT OR UPDATE ON v_elem_p  FOR EACH ROW   EXECUTE PROCEDURE public.maj_elem_p();

/* TESTS */
UPDATE v_elem_p  SET liste = '{E-99}'  WHERE gid = 2 ; 
INSERT INTO v_elem_p VALUES (DEFAULT, '{E-123, E-54}', ST_MULTI(ST_SETSRID(st_geomfromtext('POLYGON((3 3, 5 3, 5 4,5 5, 2 6, 3 3))'), 2154)))

One Answer

Picking up the comments, and referring to your edit:

In order to workaround the apparent bug with a proxy View you will make it hold a string column rather than the original array, or the same issues will arise!

Use

CREATE OR REPLACE VIEW elem_p_v AS
    SELECT  gid,
            ARRAY_TO_STRING(liste, ',') AS liste,
            geom
    FROM    elem_p
;

CREATE OR REPLACE FUNCTION elem_p_v_insert_func()
  RETURNS TRIGGER AS
    $$
    BEGIN
      INSERT INTO elem_p (liste, geom) VALUES
        (REGEXP_SPLIT_TO_ARRAY(NEW.liste, ','), NEW.geom)
      ;
      RETURN NEW;
    END;
    $$
  LANGUAGE 'plpgsql'
;

CREATE TRIGGER elem_p_check_array_literal
  INSTEAD OF INSERT ON elem_p_v
  FOR EACH ROW
  EXECUTE PROCEDURE elem_p_v_insert_func()
;

instead. The View will hold a string, concatenated from the array (change the concatenator if needed, but remember to change the regexp pattern in the trigger function accordingly).

Needless to say that you then need to work with a single string in QGIS rather than an array.

Note that in this particular case (splitting geometries), the issue arises for the actual INSERT operation only. As long as you don't change the liste values, you don't need an UPDATE handler.


This could be handled with a custom CAST, but since it requires to coerce an initial undecorated string literal (as UNKNOWN pseudo-type) into VARCHAR within a function, it would require a C conversion function; higher level language functions do not accept pseudo-types as of yet. Since adding casts can also have plenty of unforeseen consequences, I'll not add that here.

Answered by geozelot on February 1, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP