CODEWITHSUNDEEP
postgresqltriggers
NovumCoder
NovumCoder

Reputation: 4657

How to log delete queries on Postgresql?

I created a function which writes information about table deletions. And another function which simply adds a trigger call after delete. But I would like to store the whole row as string into my table. According to Postgresql Documentation it should work by adding "OLD.*" into a text based column. But it fails telling me that I try to put too many columns into this table. OLD is from type RECORD. And i want to have it in my text field like "value1,value2,value3" or it could be "colname:value,colname2:value". I dont care, I just want to see the row which has been deleted.

Another approach can be to log all delete queries from pg_stat_activity. But I don't know how to do that. Simply accessing pg_stat_activity every second would cause too much traffic I guess.

My table is simple:

create table delete_history (date timestamp, tablename varchar(100), data text);

This is my function:

CREATE or REPLACE FUNCTION ondelete() RETURNS TRIGGER AS $$
BEGIN
 INSERT INTO delete_history VALUES (CURRENT_TIMESTAMP, TG_TABLE_NAME, OLD.*);
 RETURN OLD;
END;
$$ LANGUAGE plpgsql;

This is my trigger:

CREATE OR REPLACE FUNCTION history_create_triggers() RETURNS void
AS $$
DECLARE
r RECORD;
BEGIN
FOR r IN SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' AND table_type='BASE TABLE' LOOP
    EXECUTE 'CREATE TRIGGER log_history AFTER DELETE ON public.' || r.table_name || ' FOR EACH ROW EXECUTE PROCEDURE ondelete();';
END LOOP;
END;
$$ LANGUAGE plpgsql;

Upvotes: 1

Views: 8016

Answers (1)

roman
roman

Reputation: 117475

You can convert type record into text:

CREATE or REPLACE FUNCTION ondelete() RETURNS TRIGGER AS $$
BEGIN
 INSERT INTO delete_history VALUES (CURRENT_TIMESTAMP, TG_TABLE_NAME, OLD::text);
 RETURN OLD;
END;
$$ LANGUAGE plpgsql;

sql fiddle demo

another approach could be converting your row into JSON with row_to_json function (if you have version 9.2):

CREATE or REPLACE FUNCTION ondelete() RETURNS TRIGGER AS $$
BEGIN
 INSERT INTO delete_history VALUES (CURRENT_TIMESTAMP, TG_TABLE_NAME, row_to_json(OLD));
 RETURN OLD;
END;
$$ LANGUAGE plpgsql;

sql fiddle demo

Another approach can be convert your data to hstore

CREATE or REPLACE FUNCTION ondelete() RETURNS TRIGGER AS $$
BEGIN
 INSERT INTO delete_history VALUES (CURRENT_TIMESTAMP, TG_TABLE_NAME, hstore(OLD));
 RETURN OLD;
END;
$$ LANGUAGE plpgsql;

I can't test it now - sqlfiddle is not allowing to use hstore.

Upvotes: 3

Related Questions