[GIS] Trigger to fill attributes (data, client ip) in table for each new objects, PostGIS

attribute-tablepostgis-2.0qgistrigger

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:

CREATE TABLE point (
    id serial primary key,
    anything varchar
);

-- Add field names with default values as desired:

ALTER TABLE point ADD COLUMN username varchar DEFAULT "current_user"();
ALTER TABLE point ADD COLUMN address varchar DEFAULT "inet_client_addr"();
ALTER TABLE point ADD COLUMN time timestamp without time zone default now();

-- Now let's feed the table:

INSERT INTO point (anything) VALUES ('something'), ('something else');

-- See if it works:

SELECT * FROM point;
 id |    anything    | username |     address     |            time            
----+----------------+----------+-----------------+----------------------------
  1 | something      | lougit   | 192.168.1.29/32 | 2013-10-15 12:34:46.427796
  2 | something else | lougit   | 192.168.1.29/32 | 2013-10-15 12:34:46.427796
(2 rows)

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!

Related Question