Linux 如何创建 cron 作业来运行 postgres SQL 函数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7805414/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I create a cron job to run an postgres SQL function?
提问by Phil
I assume that all I need to do is to:
我认为我需要做的就是：
Create an sql file e.g. nameofsqlfile.sql contents:
perform proc_my_sql_funtion();
Execute this as a cron job.
创建一个 sql 文件，例如 nameofsqlfile.sql 内容：
执行 proc_my_sql_funtion();
将此作为 cron 作业执行。
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...?
但是，我不知道我需要编写哪些命令才能将此 cron 作业作为指定主机、端口、数据库、用户及其密码的 postgres 函数执行...？
回答by WebDevPT
Check this
检查这个
http://archives.postgresql.org/pgsql-admin/2000-10/msg00026.phpand http://www.dbforums.com/postgresql/340741-cron-jobs-postgresql.html
http://archives.postgresql.org/pgsql-admin/2000-10/msg00026.php和 http://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
或者您可以创建一个 bash 脚本来包含您的编码并从 crontab 中调用它
回答by Matthew Rudy
You just need to think of cronjob as running a shell command at a specified time or day.
您只需要将 cronjob 视为在指定的时间或日期运行 shell 命令。
So your first job is to work out how to run your shell command.
所以你的第一份工作是弄清楚如何运行你的 shell 命令。
psql --host host.example.com --port 12345 --dbname nameofdatabase < my.sql
You can then just add this to your crontab (I recommend you use crontab -eto avoid breaking things)
然后您可以将其添加到您的 crontab （我建议您使用crontab -e以避免破坏事物）
# 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
回答by wildplasser
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:
在大多数情况下，您可以将所有 sql 源代码放在 shell 的“此处文档”中。这里文档的好处是 shell 的 ${MY_VAR}甚至在单引号内也被扩展，例如：
#!/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
青年会
