PostgreSQL中使用脚本变量生成存储过程时，FORMAT块内数据类型变量替换失效的问题

我明白你遇到的问题了——用psql的\set设置类型变量后，函数参数的类型能正确替换，但EXECUTE FORMAT里的类型总是出问题，要么报错要么变量名直接显示成字符串。这其实是psql预处理器和PL/pgSQL运行时处理逻辑的冲突导致的，咱们一步步解决它。

问题根源分析 #

psql的变量替换是在脚本发送给PostgreSQL服务器之前完成的预替换，而PL/pgSQL里的EXECUTE FORMAT是在服务器端运行时执行的。你之前的写法把psql变量和PL/pgSQL的字符串拼接混在一起了：

• 直接用:bucket_data_type的话，psql替换后会生成无单引号的类型名（比如DATE），导致PL/pgSQL里出现'|| DATE ||'这种语法错误（DATE是类型，不是字符串常量）
• 没处理好变量引用规则的话，psql会把:bucket_data_type当作普通字符串保留，不会替换。

解决方法一：修正PL/pgSQL内的字符串拼接 #

核心是用psql的特殊语法:'bucket_data_type'引用变量，这样psql会把变量值用单引号包裹后再替换，PL/pgSQL就能正确拼接成类型名。

示例脚本：

-- 1. 设置目标数据类型
\set bucket_data_type DATE

-- 2. 创建函数（注意：insert是SQL关键字，用双引号括起来避免冲突）
CREATE OR REPLACE FUNCTION "insert"(
    ids BIGINT[],
    types TEXT[],
    buckets :bucket_data_type[]
) RETURNS VOID LANGUAGE PLPGSQL AS $$
BEGIN
    EXECUTE FORMAT(
        'INSERT INTO myTable(id, type, buckets)
         SELECT _.id, _.type, _.bucket
         FROM(
             SELECT unnest(%L::bigint[]) AS monitor_id,
                    unnest(%L::text[]) AS feature_type,
                    unnest(%L::'|| :'bucket_data_type' ||'[]) AS bucket
         ) _ ON CONFLICT DO NOTHING;',
        ids, types, buckets
    );
END $$;

效果说明：
psql会把:'bucket_data_type'替换成'DATE'，PL/pgSQL拼接后会生成unnest(%L::DATE[]) AS bucket，完全符合你的预期。切换成TIMESTAMP类型时，只需要重新执行\set bucket_data_type TIMESTAMP再运行创建脚本即可。

解决方法二：用psql预生成完整的FORMAT SQL字符串 #

如果觉得PL/pgSQL内的拼接太绕，还可以让psql直接生成完整的FORMAT SQL字符串，再传入函数定义，逻辑更清晰：

-- 1. 设置目标数据类型
\set bucket_data_type TIMESTAMP

-- 2. 预生成FORMAT要用的SQL字符串（psql会直接替换变量）
\set format_sql 'INSERT INTO myTable(id, type, buckets) SELECT _.id, _.type, _.bucket FROM( SELECT unnest(%L::bigint[]) AS monitor_id, unnest(%L::text[]) AS feature_type, unnest(%L::' :bucket_data_type '[]) AS bucket ) _ ON CONFLICT DO NOTHING;'

-- 3. 创建函数
CREATE OR REPLACE FUNCTION "insert"(
    ids BIGINT[],
    types TEXT[],
    buckets :bucket_data_type[]
) RETURNS VOID LANGUAGE PLPGSQL AS $$
BEGIN
    EXECUTE FORMAT(:format_sql, ids, types, buckets);
END $$;

效果说明：
psql会先把:format_sql替换成包含正确类型的完整SQL字符串（比如TIMESTAMP版本会生成unnest(%L::TIMESTAMP[])），函数里直接使用这个预生成的字符串，完全避免了PL/pgSQL内的拼接错误。

验证结果 #

当你运行\set bucket_data_type DATE再执行脚本，生成的函数里FORMAT块内的代码会是：

INSERT INTO myTable(id, type, buckets) SELECT _.id, _.type, _.bucket FROM( SELECT unnest(%L::bigint[]) AS monitor_id, unnest(%L::text[]) AS feature_type, unnest(%L::DATE[]) AS bucket ) _ ON CONFLICT DO NOTHING;

完全符合你预期的结果。

内容来源于stack exchange