run queries periodically (ala crontab) from a postgresql database

Question

I am trying to replicate (a mininimal) crontab functionality in a postgresql database table with crontab-like semantics as follows below (indices not shown, for simiplicity):

create table schedule (
id int not null primary key,
command text
);

create table schedule_detail(
schedule_id int not null references schedule(id),
sd_minute smallint not null,
sd_hour smallint not null,
sd_day smallint not null,
sd_month smallint not null,
sd_year smallint not null,
sd_dayofweek smallint not null
);

I have only got as far as the schema design. I would like some ideas on how I can implement this. In particular, how to implement:

1. The 'periodic polling' of the schedule table
2. How to determine which schedule to be run

Does postgresql have any native (inbuilt) functionality that I can build upon to achieve this behaviour?

Answer 1

pgAgent will do the job. A complete setup can be found at Postgresonline.com.

Answer 2

I have been using psql + crontab for it. Like this :

This crontab entry will delete content older than 60 days on rolling basis.

0 16 * * * PGPASSWORD=mypassword psql -Umyuser -dmyschema -c "delete from mytable where created_at < now() - interval '60 days'"