CODEWITHSUNDEEP
sqlpostgresqldatabase
Patrick
Patrick

Reputation: 73

Trigger to delete past records in postgresql

I want only to maintain present 1 month records log details. need to delete past record log details.I tried this code however could not work this,

create sequence userseq1;

CREATE TABLE users
( id integer NOT NULL DEFAULT nextval('userseq1'::regclass) 
);

INSERT INTO users VALUES(126);

CREATE TABLE History
( userid integer
, createdate timestamp
);

CREATE OR REPLACE FUNCTION recordcreatetime() RETURNS trigger language plpgsql
AS $$
DECLARE
BEGIN
    INSERT INTO History values(new.id,NOW() );
    RETURN NEW;
END;
$$;

CREATE TRIGGER user_hist
BEFORE INSERT ON users
FOR EACH ROW
EXECUTE procedure recordcreatetime();

However it is working to insert values sequencing one by one adding.I want to delete the previous 1 month record Log details.I tried this below code and it is not working

CREATE OR REPLACE FUNCTION trf_keep_row_number_steady()
RETURNS TRIGGER AS 
$body$
DECLARE
BEGIN
        IF (SELECT count(createdate) FROM history) > rownum_limit
    THEN 
        DELETE FROM history
        WHERE createdate = (SELECT min(createdate) FROM history);        
    END IF;
END;
$body$
LANGUAGE plpgsql;

CREATE TRIGGER tr_keep_row_number_steady 
AFTER INSERT ON history
FOR EACH ROW EXECUTE PROCEDURE trf_keep_row_number_steady();

Upvotes: 0

Views: 395

Answers (1)

Rachcha
Rachcha

Reputation: 8816

I can see in your second code block, you have a trigger on history table and you are trying to DELETE FROM history in that same trigger.

Insert / Update / Delete on a table through a trigger on the same table is not allowed. Please think of some other alternative, e.g., running a separate DELETE statement for the required cleanup of rows before or after your main INSERT statement.

Upvotes: 1

Related Questions