How do I create a cron job to run an postgres SQL function?

Question

I assume that all I need to do is to:

1. Create an sql file e.g. nameofsqlfile.sql contents:

   perform proc_my_sql_funtion();

2. Execute this as a cron job.

However, I don't know the commands that I'd need to write to get this cron job executed as a postgres function for a specified host,port,database, user & his password...?

Answer 1

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

Answer 2

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/sh

THE_DATABASE=personnel
MY_TABLE=employee
THE_DATE_VARIABLE_NAME=hire_date
THE_MONTH=10
THE_DAY=01

psql ${THE_DATABASE} <<THE_END
  SELECT COUNT(*) FROM ${MY_TABLE}
  WHERE ${THE_DATE_VARIABLE_NAME} >= '2011-${THE_MONTH}-${THE_DAY}'::DATE
THE_END

YMMV