Dear Community.
I want to create editable views in postgis and track the edit history on this view when editing with QGIS. In my example I want to track changes on my table test.vulkan that contains volcanoes of the world. The edits (delete, update, insert) shall be archived in table test.vulkan_update with additional infomation on date, ipaddress and type of transaction. The volcanoes are edited with an editable view (test.volcano_edit) and handled with rules so that data edited is transfered to the update table. So far my example worked on the QGIS 2.x branch but under QGIS 3.6 the insert rule does not work anymore. Below you find the create statements for the tables, the view definition and the rules to handle the editable view in QGIS. Somehow, the problem relates to the new insert widgets in QGIS 3.6 that does not support a null id that should be handled with postges on the basis of a sequence(select nextval(‘test.vulkan_id_seq’)). The sequence itself is shared between both tables vulkan and vulkan_update. I deaktivated under properties the enforce null and enforce integrity buttons but this did not help. I would welcome your help and support
CREATE TABLE test.vulkan
(
id integer NOT NULL DEFAULT nextval('test.vulkan_id_seq'::regclass),
geom geometry(1107468),
name_ character varying(9) COLLATE pg_catalog."default",
status character varying(9) COLLATE pg_catalog."default",
objectid integer,
CONSTRAINT vulkan_pkey PRIMARY KEY (id)
);
CREATE TABLE test.vulkan_update
(
id integer DEFAULT nextval('test.vulkan_id_seq'::regclass),
objectid integer,
geom geometry,
name_ character varying(20) COLLATE pg_catalog."default",
status character varying(20) COLLATE pg_catalog."default",
type_ character varying(20) COLLATE pg_catalog."default",
transact character varying(10),
address text,
datum date
);
create or replace view test.volcano_edit as
select id, objectid, geom, name_,type_,status from test.vulkan
where objectid not in (select objectid from test.vulkan_update where objectid is not null)
union
(select id, objectid, geom, name_,type_,status from test.vulkan_update
where transact <> 'delete')
;
--delete rule
create or replace rule "delete_vulkan" as
on delete to test.volcano_edit
do instead
insert into test.vulkan_update (id,objectid,geom,name_,type_,status,transact,address,datum)
values((select nextval('test.vulkan_id_seq')),old.objectid,old.geom,old.name_,old.type_,old.status,'delete',(select inet_client_addr()),CURRENT_DATE);
--insert rule
create or replace rule "insert_vulkan" as
on insert to test.volcano_edit do instead
insert into test.vulkan_update (id,objectid,geom,name_,type_,status,transact,address,datum)
values((select nextval('test.vulkan_id_seq')),null,new.geom,new.name_,new.type_,new.status,'insert',(select inet_client_addr()),CURRENT_DATE);
--update rule
create or replace rule "update_vulkan" as
on update to test.volcano_edit do instead
insert into test.vulkan_update (objectid,geom,name_,type_,status,transact,address,datum)
values(old.objectid,new.geom,new.name_,new.type_,new.status,'update',(select inet_client_addr()),CURRENT_DATE);