How do I create a cron job to run an postgres SQL function?
You just need to think of cronjob as running a shell command at a specified time or day.
So your first job is to work out how to run your shell command.
psql --host host.example.com --port 12345 --dbname nameofdatabase --username postgres < my.sql
You can then just add this to your crontab (I recommend you use crontab -e
to avoid breaking things)
# runs your command at 00:00 every day## min hour wday month mday command-to-run 0 0 * * * psql --host host.example.com --port 12345 --dbname nameofdatabase < my.sql
In most cases you can put all of the sql source in a shell 'here document'. The nice thing about here documents is that the shell's ${MY_VAR} are expanded even within single quotes, e.g:
#!/bin/shTHE_DATABASE=personnelMY_TABLE=employeeTHE_DATE_VARIABLE_NAME=hire_dateTHE_MONTH=10THE_DAY=01psql ${THE_DATABASE} <<THE_END SELECT COUNT(*) FROM ${MY_TABLE} WHERE ${THE_DATE_VARIABLE_NAME} >= '2011-${THE_MONTH}-${THE_DAY}'::DATETHE_END
YMMV
Check this
http://archives.postgresql.org/pgsql-admin/2000-10/msg00026.phpandhttp://www.dbforums.com/postgresql/340741-cron-jobs-postgresql.html
or you can just create a bash script to include your coding and call it from crontab