PG/PLSQL脚本匹配数据库名后未成功创建用户的问题排查

代码匹配规则失效的核心问题有两处：

• 对pg_catalog.pg_database的查询未加筛选条件：该系统表存储当前PostgreSQL实例下所有数据库的元数据，查询会返回多行结果。PL/pgSQL中SELECT INTO遇到多行返回时，只会将结果集第一行的datname赋值给目标变量，其余结果直接丢弃且不抛错，最终变量拿到的几乎不可能是你预期要匹配的dev库名。
• 取数逻辑冗余：要判断当前会话连接的数据库名，不需要查询系统表，直接调用内置函数即可，完全不会出现多行赋值的异常。

另外存在兼容隐患：如果monitor角色已经存在，直接执行CREATE ROLE会抛出“角色已存在”的错误，重复执行脚本时会中断。

根据你的实际需求二选一即可：

场景1：仅当脚本当前连接的是dev库时创建角色 #

这也是绝大多数同类校验场景的需求，直接用内置函数拿当前库名即可：

DO
$do$
DECLARE
    lc_s_db_name CONSTANT VARCHAR(30) := 'dev';
    lv_db_name  VARCHAR(100);
BEGIN
    lv_db_name := current_database();
     
    IF lv_db_name = lc_s_db_name
    THEN
        -- PG10及以上版本支持IF NOT EXISTS，避免重复执行报错
        CREATE ROLE IF NOT EXISTS monitor LOGIN PASSWORD 'monitor';
    END IF;
END
$do$;

场景2：只要实例中存在dev库，无论当前连哪个库都创建角色 #

注意PostgreSQL的ROLE是实例级对象，创建后所有库都可见，不需要重复创建，对应写法如下：

DO
$do$
BEGIN
    IF EXISTS(SELECT 1 FROM pg_catalog.pg_database WHERE datname = 'dev')
    THEN
        CREATE ROLE IF NOT EXISTS monitor LOGIN PASSWORD 'monitor';
    END IF;
END
$do$;

如果你的PostgreSQL版本低于10，不支持CREATE ROLE IF NOT EXISTS，可以先查pg_roles表判断角色是否存在，再执行创建语句即可。

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