Friday 21 April 2017

How to set postgis default sequential value in a QGIS "editable" view?


Introduction


In a QGIS project I have a VIEW, that I want to became editable (I'm trying to replicate the versioning system used DB Manager with some tweeks). Therefore I created a few rules to make is so (see below). I'm able to delete and update the features without problems, but while inserting new features, I'm not being able to use postgis default value (sequence) for gid (that in this case is not a primary key) but has a NOT NULL constraint. So, if the user don't fill the gid value, qgis is not be able to commit the changes, if it does... we neve know where thing going to get wrong.


The question


Is there a way I can prepare my view or set qgis form to read the default value?


What I got



-- The original table
CREATE TABLE "PGHP".infraestruturas
(
gid serial,
nome character varying(40),
classe character varying(40),
geom geometry(POINT, 3763),
"id_hist" serial primary key,
"time_start" timestamp,
"time_end" timestamp,

"user_update" character varying(40)
);

-- Creating the view
CREATE OR REPLACE VIEW "PGHP".infra_portoes AS
SELECT "gid", "nome", "geom"::Geometry(POINT, 3763), "time_start", "user_update"
FROM "PGHP"."infraestruturas_pontos"
WHERE "classe" = 'portoes' AND "time_end" IS NULL;

-- Make the view editable

CREATE OR REPLACE RULE "_DELETE" AS ON DELETE TO "PGHP".infra_portoes DO INSTEAD
DELETE FROM "PGHP"."infraestruturas_pontos" WHERE gid = old.gid;

CREATE OR REPLACE RULE "_INSERT" AS ON INSERT TO "PGHP".infra_portoes DO INSTEAD
INSERT INTO "PGHP"."infraestruturas_pontos" ("gid","classe","nome","geom")
VALUES (NEW."gid",'portoes',NEW."nome",NEW."geom");

CREATE OR REPLACE RULE "_UPDATE" AS ON UPDATE TO "PGHP".infra_portoes DO INSTEAD
UPDATE "PGHP"."infraestruturas" SET
"gid" = NEW."gid",

"classe" = 'portoes',
"nome" = NEW."nome",
"geom" = NEW."geom"
WHERE gid = NEW.gid;


No comments:

Post a Comment

arcpy - Changing output name when exporting data driven pages to JPG?

Is there a way to save the output JPG, changing the output file name to the page name, instead of page number? I mean changing the script fo...