1

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!

3
  • 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. May 24, 2013 at 15:43
  • Your INSERT lines need to explicitly specify the columns, so something like: INSERT INTO roads (updated, nameuser) SELECT ...
    – Micha
    May 24, 2013 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 Oct 13, 2013 at 17:44

2 Answers 2

5
+25

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!

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();
1
  • 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 ? Oct 13, 2013 at 17:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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