6

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 never 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;
1

You can set a default value on a view. This will be applied on the DB and can be read and understood by QGIS.

ALTER VIEW "PGHP"."infra_portoes"
  ALTER gid
  SET DEFAULT nextval(pg_get_serial_sequence('PGHP.infraestruturas', 'gid'));
|improve this answer|||||
0

If you want to create an editable writable functional Postgresql's view, to use in Qgis for example, I propose an easy didactical demo, you can to copy and paste this code below directly in Postgresql shell:

/*Places table*/
create table myschema.places
(id serial primary key,
nameplace character varying(100),
sectornumber integer);
select AddGeometryColumn('myschema','places','geom',3064,'POLYGON',2);

/*Site table*/
create table myschema.site
(id serial primary key,
verified boolean,
name character varying(100),
address character varying(100),
note text);
select AddGeometryColumn('myschema','site','geom',3064,'POINT',2);
create index idx_site_geom on myschema.site using gist (geom);


/*Myview view*/
create view myschema.myview as
select
id,
geom,
verified,
name,
address,
note,
(select p.nameplace from myschema.places p where st_intersects(myschema.site.geom, p.geom)) as place,
(select p.sectornumber from myschema.places p where st_intersects(myschema.site.geom, p.geom)) as sector,
st_astext(st_transform(geom,4326)) as wgs84_ddd
from myschema.site;


/*Rule for Deleting*/
create or replace rule site_delete as
on delete to myschema.myview do instead
delete from myschema.site where site.id = old.id;

/*Rule for Updating*/
create or replace rule site_update as
on update to myschema.myview do instead
update myschema.site
set
verified = new.verified,
name = new.name,
address = new.address,
note = new.note,
geom = new.geom
where site.id = new.id;

/*Rule for Inserting*/
create or replace rule site_insert as
on insert to myschema.myview do instead
insert into myschema.site (id, verified, name, address, note, geom)
values
(nextval('myschema.site_id_seq'::regclass), new.verified, new.name, new.address, new.note, st_force2d(new.geom))
returning /*list the table's fields ordered by view's order*/
site.id,
site.geom,
site.verified,
site.name,
site.address,
site.note,
''::character varying(100) as place,
null::integer as sector,
''::text as wgs84_ddd;
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.