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

Question

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!

Answer 1

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!

Answer 2

Example a query that I needed:

/*
ALTER TABLE point ADD COLUMN username CHAR(12);
ALTER TABLE point ADD COLUMN address CHAR(12);
ALTER TABLE point ADD COLUMN time TIMESTAMP WITHOUT TIME ZONE;
*/


--DROP TRIGGER point_insert_update_trigger ON point;
--DROP FUNCTION point_insert_update();


CREATE OR REPLACE FUNCTION point_insert_update()
RETURNS trigger AS
$BODY$
BEGIN
NEW.username:=session_user;    
NEW.address:=inet_client_addr(); 
NEW.time:=current_timestamp; 
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;


CREATE TRIGGER point_insert_update_trigger
BEFORE INSERT OR UPDATE
ON point
FOR EACH ROW
EXECUTE PROCEDURE point_insert_update();