I'll add a simple example of what I think you need; I assume that
- a first set of users will update the 'static table' (attributes only) in QGIS and save back to DB
- a second set of users updates the 'dynamic view's base layers and its geometry independently
- the edits of the first user group must not not affect the geometry; edits to it will be ignored
DELETE statements need to be treated with care; you'd have to decide what to do with non-matching
ids in the 'dynamic view'...and work out a similar approach with it if you want to be able to alter that views underlying data.
If you are not interested in the ability to update attributes and save changes to the DB by the first group of users, this is overkill...instead, simply create the view below without any triggers and load that into QGIS.
First off, let's set up a (very limited) log table to keep track of changes made to the 'static table':
CREATE TABLE _composite_log(
_uid SERIAL PRIMARY KEY,
Then set up the actual view that will be the working layer for the first user group (I'll call it and prefix all related items with
CREATE OR REPLACE VIEW composite AS
FROM <static_table> AS a
JOIN <dynamic_view> AS b
ON a.id = b.id
Then create the trigger procedure...:
CREATE OR REPLACE FUNCTION composite_delupsert_row()
RETURNS TRIGGER AS
IF (TG_OP = 'UPDATE') THEN
SET (<col1>, <col2>, ...) = (NEW.<col1>, NEW.<col2>, ...)
WHERE id = OLD.id;
INSERT INTO _composite_log(op_user, op_type, op_time, id_ref)
VALUES (USER, 'UPDATE', NOW(), OLD.id);
ELSIF (TG_OP = 'INSERT') THEN
ELSIF (TG_OP = 'DELETE') THEN
...and the trigger on
CREATE TRIGGER composite_delupsert
INSTEAD OF UPDATE OR INSERT OR DELETE
FOR EACH ROW
EXECUTE PROCEDURE composite_delupsert_row()
The role that owns the trigger needs all relevant privileges on the 'static table'!
The trigger will catch any
DELETE, but will currently simply ignore them; add the respective functionality yourself if you need) statements on the
composite view and redirects the data to
<static_table> only; you need to refer each relevant column in the
SET command, all not mentioned columns will stay as they were, and the
NEW.geom value, even if changed, will be dropped silently if not handled elsewhere.
Additionally, the name of the operating user, the operation type, the timestamp and the (old) id of the changed row will be added to the
Now you can add
composite as your first groups working layer, and they can alter any attribute that is present in the 'static table' (and is mentioned in the
SET column list) via QGIS Edit functionality. The geometry is 'permanently' joined, cannot be updated by the first group (edits to it will be ignored), but will reflect all changes made to it by the second group (in QGIS as soon as the canvas is updated, e.g. via zoom, pan, ...).
This is a very basic implementation, and if you are new to this the whole thing gets very large pretty soon. Take your time with it to see what's happening before implementing it in production. You can find some more basic examples (including views) in the docs, starting with 'Example 43.5'.