By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,824 Members | 2,410 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,824 IT Pros & Developers. It's quick & easy.

Trigger function at particular time after inserting record in a table

P: 1
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?

Apr 4 '18 #1
Share this Question
Share on Google+
1 Reply

P: 5
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.

Expand|Select|Wrap|Line Numbers
  1. test=> CREATE TABLE IF NOT EXISTS trigger_test(
  2. test(>        id SERIAL PRIMARY KEY,
  3. test(>        content TEXT NOT NULL
  4. test(> );
  6. test=> 
  7. test=> CREATE TABLE IF NOT EXISTS trigger_log(
  8. test(>        id SERIAL PRIMARY KEY,
  9. test(>        content TEXT NOT NULL,
  10. test(>        logtime TIMESTAMP NOT NULL DEFAULT NOW()
  11. test(> );
  13. test=> 
  14. test=> CREATE OR REPLACE FUNCTION trg_log_content()
  15. test-> RETURNS TRIGGER AS $$
  16. test$> BEGIN
  17. test$>  INSERT INTO trigger_log(content)
  18. test$> VALUES(NEW.content);
  19. test$> 
  20. test$>  RETURN NEW;
  21. test$> END $$
  22. test-> LANGUAGE plpgsql;
  24. test=> 
  25. test=> 
  26. test=> CREATE TRIGGER trigger_demo AFTER INSERT ON trigger_test
  27. test-> FOR EACH ROW EXECUTE PROCEDURE trg_log_content();
  29. test=> insert into trigger_test(content) values('one');
  30. INSERT 0 1
  31. test=> select * from trigger_log;
  32.  id | content |          logtime           
  33. ----+---------+----------------------------
  34.   1 | one     | 2018-04-22 19:16:29.357786
  35. (1 row)
  37. test=> insert into trigger_test(content) values('two');
  38. INSERT 0 1
  39. test=> select * from trigger_log;
  40.  id | content |          logtime           
  41. ----+---------+----------------------------
  42.   1 | one     | 2018-04-22 19:16:29.357786
  43.   2 | two     | 2018-04-22 19:16:40.012248
  44. (2 rows)
  46. test=> create unique index on trigger_test (content);
  48. test=> insert into trigger_test(content) values('two');
  49. ERROR:  duplicate key value violates unique constraint "trigger_test_content_idx"
  50. DETAIL:  Key (content)=(two) already exists.
  51. test=> drop index trigger_test_content_idx ;
  53. test=> CREATE OR REPLACE FUNCTION trg_log_content()
  54. test-> RETURNS TRIGGER AS $$
  55. test$> BEGIN
  56. test$>  INSERT INTO trigger_log(content)
  57. test$> VALUES(NEW.content);
  58. test$> 
  59. test$>  RAISE EXCEPTION 'boom';
  60. test$>  RETURN NEW;
  61. test$> END $$
  62. test-> LANGUAGE plpgsql;
  64. test=> insert into trigger_test(content) values('two');
  65. ERROR:  boom
  66. CONTEXT:  PL/pgSQL function trg_log_content() line 6 at RAISE
Apr 22 '18 #2

Post your reply

Sign in to post your reply or Sign up for a free account.