How to add time-dependent trigger in PostgreSQL?

Question

I am confused and do not know how:

• Add trigger in postgresql db after installing db, i.e. add trigger by clicking hyperlink
• Write down the trigger function to delete a specific line after a week has passed since the trigger was started.

This sequence of events is just for clarity.

• The administrator sees the entry in db through the created html PHP page.
• The administrator clicks the “approve” hyperlink next to the db entry.
• This creates a trigger in db to delete this particular row after 1 week.

I need help with step 3

Would there be an alternative way to do this in order to have a trigger in the table, so when you insert a row, a new trigger is created that checks the value of the "approved" column for that row. When the administrator clicks “approve” on the html page, the word “yes” is inserted into the “approved” column for this row. Does the trigger detect this and add a new trigger to delete the row in a week?

As you can say, I really don’t know which way to do it and how to do it so that any feedback is highly appreciated. Thanks.

Answer 1

Simple: save the expiration date of the URL in your db, just retrieve the current valid entries (you can use the view for this) in your choice and from time to time delete the outdated URLs.

You cannot use a trigger. pgAgent or cron can be used to start the cleanup process.