In PostgreSQL/PostGIS database stores shapefiles. Client users software – QGIS. Please help me write PostgreSQL/PostGIS trigger, which for each newly added/updated feature in layer added 'time create' and 'client ip address' in attribute table.
Attribute table contain next fields: 'gid' – primary key, 'updated' – data create/update feature, 'nameuser' – client ip address.
I write next trigger:
CREATE OR REPLACE FUNCTION process_audit() RETURNS TRIGGER AS $audit_roads$
BEGIN
IF (TG_OP = 'INSERT') THEN
INSERT INTO roads SELECT now(), inet_client_addr(), NEW.*;
RETURN NEW;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO roads SELECT now(), inet_client_addr(), NEW.*;
RETURN NEW;
END IF;
RETURN NULL;
END;
$audit_roads$ LANGUAGE plpgsql;
CREATE TRIGGER audit_roads
AFTER INSERT OR UPDATE OR DELETE ON roads FOR EACH ROW EXECUTE PROCEDURE process_audit();
but when I add new feature in QGIS and save them, I receive message: "INSERT has more expressions than target columns"
Tracking Edit History using Triggers – there is example of Tracking Edit History using Triggers, but history saved in nyc_streets_history table (not in nyc_streets)
Thanks!
Best Answer
Something even simpler:
-- I just create a dumb table:
-- Add field names with default values as desired:
-- Now let's feed the table:
-- See if it works:
I recently had a similar problem; but using a TRIGGER led to an overhead which was a bit too high for the (small) size of the postgresql server. Large amounts of data were to be inserted in the table, and it induced some latency. So, instead, I simply used default values, as shown above. And it worked well.
Hope it helps!