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