How to log delete queries on Postgresql?

Question

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;

roman · Accepted Answer · 2013-08-23 10:39:03Z

3

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.

edited Aug 23, 2013 at 10:39

answered Aug 23, 2013 at 10:27

roman

118k30 gold badges205 silver badges209 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

roman Over a year ago

Try hstore, it can help you parse values easily in the future

Iman Rosstin Over a year ago

Do not forget to add its extension create extension hstore;

Collectives™ on Stack Overflow

How to log delete queries on Postgresql?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related