1

I want to add a function like trigger to my Postgres database. The problem is that this function should executed one time each day automatically and change values in the specified table.

Is it possible to carry out this by SQL syntax or is it better to write a bash script with some queries I need?

2
  • 2
    you need to write a SQL script and then schedule that through e.g. cron May 3, 2019 at 10:32
  • 1
    Depending on the data maniputation you want to do, you could do this. It's a pity that PostgreSQL doesn't have GENERATED or COMPUTED fields (one of the few negatives of PG vs. MySQL). Note that the link referred advises that complex data manipulation be carried out in triggers!
    – Vérace
    May 3, 2019 at 13:25

1 Answer 1

5

PostgreSQL does not have time-based triggers.

If you want to store as much of your logic as possible in the database, you could create an ordinary function (not a trigger function) and then invoke it from your favorite job scheduler (cron for me). If you want to use the database for storing data, not logic, then you could put the queries you want to run in a .sql file and use your favorite job schedule to invoke that file using psql -f for example. Or, if you want all of your application logic in, for example, a Perl module, you could write a function in your Perl module which connects to the database (reusing whatever connection logic your module already has) and does what you want, then use your favorite job scheduler to invoke that function.

There are many ways to do it, the key is to pick a method which fits in with the your overall design.

It would probably be possible to put the logic into a trigger on a dummy table, then have your favorite job scheduler do a dummy operation on the dummy table to get the trigger to fire. Pointless, but possible.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.