Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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!

share|improve this question
1  
Where is the actual question here? There is a hint of a question in your statement about the INSERT function, but it is not explicit. The more specific and direct you are in your question, the more likely you are to receive an answer. –  Get Spatial May 24 '13 at 15:43
    
Your INSERT lines need to explicitly specify the columns, so something like: INSERT INTO roads (updated, nameuser) SELECT ... –  Micha May 24 '13 at 19:25
    
For future reference, reason why this didn't work was that NEW.* contains all colums in table, so, NEW.* , now() has now one more column than target table –  simplexio Oct 13 '13 at 17:44

2 Answers 2

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!

share|improve this answer
up vote 2 down vote accepted

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();
share|improve this answer
    
thanks , didn't that you can only RETURN NEW to write data into table (or i'm assuming that you can because you dont use INSERT there ?) Does that work in INSTEAD OF trigger in VIEWS ? –  simplexio Oct 13 '13 at 17:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.