Postgres/psql 13：SQL脚本中如何分隔变量名与后缀生成角色

在psql中，当变量名后紧跟其他字符时，必须明确变量的边界，否则psql会将变量名与后续字符视为一个整体，导致变量识别错误。以下是两种可靠的解决方案：

方案一：用花括号包裹变量名（推荐） #

编写SQL脚本（如create_roles.sql），通过${rname}明确变量边界，直接拼接后缀：

-- 创建带前缀的角色，${rname}会被psql解析为传入的前缀变量
CREATE ROLE ${rname}_admin WITH LOGIN PASSWORD :'admin_pwd';
CREATE ROLE ${rname}_owner WITH LOGIN PASSWORD :'owner_pwd';
CREATE ROLE ${rname}_user WITH LOGIN PASSWORD :'user_pwd';

-- 可选：给admin角色添加创建数据库权限
ALTER ROLE ${rname}_admin CREATEDB;

然后编写Shell循环脚本批量执行：

#!/bin/bash

# 定义需要批量处理的前缀列表
PREFIXES=("XXX" "YYY" "ZZZ")
# 建议从环境变量读取密码，避免明文泄露
ADMIN_PWD=$ADMIN_DB_PWD
OWNER_PWD=$OWNER_DB_PWD
USER_PWD=$USER_DB_PWD

# 遍历前缀，调用psql执行SQL脚本
for prefix in "${PREFIXES[@]}"
do
  psql -U postgres -d your_target_db \
    -v rname="$prefix" \
    -v admin_pwd="$ADMIN_PWD" \
    -v owner_pwd="$OWNER_PWD" \
    -v user_pwd="$USER_PWD" \
    -f create_roles.sql
done

方案二：用psql的\set命令拼接完整角色名 #

先在SQL脚本中通过\set将前缀与后缀拼接成完整的角色名变量，再引用这些变量：

-- 拼接角色名变量
\set admin_role :rname '_admin'
\set owner_role :rname '_owner'
\set user_role :rname '_user'

-- 创建角色
CREATE ROLE :admin_role WITH LOGIN PASSWORD :'admin_pwd';
CREATE ROLE :owner_role WITH LOGIN PASSWORD :'owner_pwd';
CREATE ROLE :user_role WITH LOGIN PASSWORD :'user_pwd';

Shell执行脚本与方案一一致，只需传入rname和密码变量即可。

验证结果 #

执行Shell脚本后，登录psql输入\du命令，即可看到XXX_admin、YYY_owner等角色已成功创建。

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