如何从配置文件传递变量实现PostgreSQL删除脚本可配置化？

我来帮你搞定这个配置化的需求，不用每次改SQL脚本就能调整数据保留时长，有几种实用的方法，你可以根据自己的场景选择：

方法一：通过Shell脚本读取配置，用psql原生变量传递（推荐） #

这是最稳妥的方式，利用PostgreSQL的psql工具原生支持的变量传递功能，步骤如下：

1. 调整你的.config文件：去掉变量赋值的空格，避免Shell读取出错，改成：

# duration to delete the records from the database
DURATION="7 DAY"

2. 修改xyz.sh脚本：读取配置文件中的变量，然后用psql的-v参数把变量传递给SQL脚本：

# 读取配置文件中的变量
source ./.config

# 用psql的-v参数传递变量到SQL脚本，注意给DURATION加上单引号（因为INTERVAL需要字符串参数）
sudo -u user psql -d db -v DURATION="'$DURATION'" -f <<path>>/delete.sql

3. 修改delete.sql脚本：用:变量名的方式引用psql传递过来的变量：

DELETE from abc where eventtime::date <= (CURRENT_TIMESTAMP - INTERVAL :DURATION)::date;
COMMIT;

这个方法的优势是原生支持，不会有特殊字符转义的问题，而且不需要调整PostgreSQL的全局配置。

方法二：利用环境变量传递 #

如果你偏好通过环境变量传递参数，可以这样做：

1. 保持.config文件不变（同样要注意变量赋值没有多余空格）：

# duration to delete the records from the database
DURATION="7 DAY"

2. 修改xyz.sh脚本：读取配置后导出为环境变量，注意如果用sudo的话需要加上-E参数保留环境变量：

# 读取配置并导出为环境变量
source ./.config
export DURATION

# 用sudo -E保留环境变量，然后执行SQL脚本
sudo -E -u user psql -d db -f <<path>>/delete.sql

3. 修改delete.sql脚本：用current_setting('env.DURATION')读取环境变量：

DELETE from abc where eventtime::date <= (CURRENT_TIMESTAMP - INTERVAL current_setting('env.DURATION'))::date;
COMMIT;

⚠️ 注意：这种方法需要确保PostgreSQL允许读取这个环境变量，你可能需要修改postgresql.conf中的envlist配置，添加DURATION，比如：

envlist = 'DURATION,...'

修改后需要重启PostgreSQL服务生效。

方法三：用sed动态替换SQL脚本内容 #

如果不想改动SQL脚本的变量引用方式（还是想用$DURATION），可以用sed在执行前替换变量值：

1. 保持.config文件不变：

# duration to delete the records from the database
DURATION="7 DAY"

2. 修改xyz.sh脚本：用sed替换SQL脚本中的$DURATION，然后管道给psql执行：

source ./.config

# 替换SQL中的变量并执行
sed "s/\$DURATION/$DURATION/" <<path>>/delete.sql | sudo -u user psql -d db

3. 你的delete.sql可以保持原来的样子：

DELETE from abc where eventtime::date <=(CURRENT_TIMESTAMP-INTERVAL '$DURATION')::date; COMMIT;

这个方法的缺点是如果DURATION里包含特殊字符（比如斜杠、引号），需要额外处理转义，不如前两种方法可靠。

内容的提问来源于stack exchange，提问作者Lucifer