0

In my Postgres database, I have one stored procedure, say sp_1(). Now I have a table called dispatch_details. Insertion to this table happens once in a while. So if data gets updated in this table during daytime, I want to schedule sp_1 at 8pm on that day. To be more specific, I don't want to schedule sp_1() if dispatch_details table is not updated.

How can I accomplish it? Please help.

(Note : I don't want to run sp_1 as and when dispatch_details is updated, so that I will not be able to use normal trigger here.)

Asunez
  • 2,327
  • 1
  • 23
  • 46
Ambily
  • 11
  • 2
  • Hello and welcome to SO community. I have edited your question to be more clear (it may take some time until it is peer reviewed to be visible). When asking questions here please spare some time to make them easily readable and add as much information possible. You may also want to add what you have tried so far. – Asunez Jul 07 '17 at 12:00

1 Answers1

0

I don't know why you do not want to use a trigger for this task. In my eyes, a trigger would be the easiest way. Another option would be to program sp_1() in such a way that it is ignored in case there was no update in the table in the previous day, by means of tokens stored in another table: 

CREATE TABLE foo (last_update DATE, last_pgagent_check DATE);

This token (last_update) would be updated via a trigger (ha!) every time the table gets updated. Then your SP would first check if last_update is greater than last_pgagent_check, and if so, execute the function it is supposed to, otherwise just update last_pgagent_check with SELECT CURRENT_DATE.

It's a huge workaround and imho not advisable, but would do what you want.

Jim Jones
  • 18,404
  • 3
  • 35
  • 44