Trigger function at particular time after inserting record in a table

[bhanubtech35]

Hi all,
I'm new to Postgres, and I have a requirement something like after inserting the record in a table I need to remove the record from that same table after configured time interval. So can anyone please let me know how can I achieve this?

Thanks,
Bhanu.

[mcptr]

Interesting use case. You will have to handle it in your application instead. There is no job scheduling in the database, and triggers are executed in the same transaction as the statement.

I'm attaching a little snippet. I create the table for my data, another table to copy the data into (using a trigger), I add a trigger function and create a trigger.
The trigger works as intended, but then I add a unique constraint on the table and try to insert an existing value, in which case the transaction will get aborted. As a next example I remove that constraint and raise exception from within the trigger ("boom"). You can clearly see that the trigger fails and the "commit" statement is never executed. The transaction gets rolled back instead.

You might achieve your goal by using listen/notify in postgres, but i believe the right approach will be to introduce job scheduling (or background jobs facility) at the application level.

test=> CREATE TABLE IF NOT EXISTS trigger_test(
test(>        id SERIAL PRIMARY KEY,
test(>        content TEXT NOT NULL
test(> );
CREATE TABLE
test=> 
test=> CREATE TABLE IF NOT EXISTS trigger_log(
test(>        id SERIAL PRIMARY KEY,
test(>        content TEXT NOT NULL,
test(>        logtime TIMESTAMP NOT NULL DEFAULT NOW()
test(> );
CREATE TABLE
test=> 
test=> CREATE OR REPLACE FUNCTION trg_log_content()
test-> RETURNS TRIGGER AS $$
test$> BEGIN
test$>  INSERT INTO trigger_log(content)
test$> VALUES(NEW.content);
test$> 
test$>  RETURN NEW;
test$> END $$
test-> LANGUAGE plpgsql;
CREATE FUNCTION
test=> 
test=> 
test=> CREATE TRIGGER trigger_demo AFTER INSERT ON trigger_test
test-> FOR EACH ROW EXECUTE PROCEDURE trg_log_content();
CREATE TRIGGER
test=> insert into trigger_test(content) values('one');
INSERT 0 1
test=> select * from trigger_log;
 id | content |          logtime           
----+---------+----------------------------
  1 | one     | 2018-04-22 19:16:29.357786
(1 row)
 
test=> insert into trigger_test(content) values('two');
INSERT 0 1
test=> select * from trigger_log;
 id | content |          logtime           
----+---------+----------------------------
  1 | one     | 2018-04-22 19:16:29.357786
  2 | two     | 2018-04-22 19:16:40.012248
(2 rows)
 
test=> create unique index on trigger_test (content);
CREATE INDEX
test=> insert into trigger_test(content) values('two');
ERROR:  duplicate key value violates unique constraint "trigger_test_content_idx"
DETAIL:  Key (content)=(two) already exists.
test=> drop index trigger_test_content_idx ;
DROP INDEX
test=> CREATE OR REPLACE FUNCTION trg_log_content()
test-> RETURNS TRIGGER AS $$
test$> BEGIN
test$>  INSERT INTO trigger_log(content)
test$> VALUES(NEW.content);
test$> 
test$>  RAISE EXCEPTION 'boom';
test$>  RETURN NEW;
test$> END $$
test-> LANGUAGE plpgsql;
CREATE FUNCTION
test=> insert into trigger_test(content) values('two');
ERROR:  boom
CONTEXT:  PL/pgSQL function trg_log_content() line 6 at RAISE