PostgreSQL WHILE循环中使用psql传入参数的语法问题

问题原因 #

psql通过-v传入的变量，在美元引号（$do$）包裹的PL/pgSQL代码块中无法直接解析：

• 写:myVar会报错，因为psql不会对美元引号内的内容做变量插值，服务器收到的代码里:myVar属于非法语法；
• 直接写myVar会被PL/pgSQL误认为是列名，因此报“myVar列不存在”。

两种可行解决方案 #

方案1：通过psql变量转换实现替换 #

先利用psql的\set命令将传入的变量转换为可插入PL/pgSQL的格式，再在代码块中引用：

-- 先通过psql命令行传入参数：psql -v myVar=5 你的数据库名
\set myVar_val :myVar

DO
$do$
DECLARE
    i integer = 1;
    max_val integer = :myVar_val;
BEGIN
    WHILE i <= max_val LOOP
        -- 替换为你的业务操作，示例：打印当前i值
        RAISE NOTICE '当前循环值: %', i;
        i := i + 1;
    END LOOP;
END;
$do$;

\set myVar_val :myVar会让psql把myVar的实际值（比如5）赋值给myVar_val，后续:myVar_val会被替换为数值，最终服务器收到的代码中max_val会被正确初始化为5。

方案2：使用DO块的参数传递（PostgreSQL 11+推荐） #

PostgreSQL 11及以上版本支持DO ... USING语法传递参数，这是更规范的方式：

-- 通过psql命令行传入参数：psql -v myVar=5 你的数据库名
DO
$do$
DECLARE
    i integer = 1;
    max_val integer := $1;
BEGIN
    WHILE i <= max_val LOOP
        -- 替换为你的业务操作，示例：打印当前i值
        RAISE NOTICE '当前循环值: %', i;
        i := i + 1;
    END LOOP;
END;
$do$ USING :myVar;

USING :myVar会让psql将传入的myVar值作为参数传递给DO块，在PL/pgSQL中用$1引用该参数，赋值给max_val后即可在循环条件中正常使用。

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