PostgreSQL查询中声明变量检查数组包含性时遇对象未找到问题求解

报错原因 #

报错The specified object could not be found是因为你在创建临时表时使用了ON COMMIT DROP选项，再加上DO语句会在独立事务中执行——当DO块执行完毕，事务自动提交，临时表会被立即销毁，后续的SELECT语句自然找不到这个表。

解决方案 #

这里提供几种可行的解决方式，你可以根据需求选择：

方案1：移除临时表的自动销毁选项

去掉ON COMMIT DROP，让临时表在当前会话结束前保持存在，这样后续查询就能正常访问：

DO $$
DECLARE 
    thetag1 text = 'tag1';
    thetag2 text = 'tag2';
BEGIN 
    CREATE TEMP TABLE tmp_table AS
    SELECT DISTINCT 
        p.id
    FROM table_1 p
    WHERE thetag1 = ANY (p.tags)
      AND thetag2 = ANY (p.tags);
END $$;
SELECT * FROM tmp_table;

注：原代码中p.tags::text[]是多余的，p.tags本身就是character varying[]类型，PostgreSQL会自动处理与text变量的匹配，无需额外转换。

方案2：不用DO块，直接用CTE定义变量

这种方式更简洁，不需要创建临时表，直接返回查询结果：

WITH vars AS (
    SELECT 'tag1'::text AS thetag1, 'tag2'::text AS thetag2
)
SELECT DISTINCT p.id
FROM table_1 p, vars v
WHERE v.thetag1 = ANY (p.tags)
  AND v.thetag2 = ANY (p.tags);

方案3：在同一事务中执行DO块与查询

如果需要临时表自动销毁，可将DO块和查询放在同一个事务中，确保查询执行时临时表还存在，事务提交后自动删除：

BEGIN;
DO $$
DECLARE 
    thetag1 text = 'tag1';
    thetag2 text = 'tag2';
BEGIN 
    CREATE TEMP TABLE tmp_table ON COMMIT DROP AS
    SELECT DISTINCT 
        p.id
    FROM table_1 p
    WHERE thetag1 = ANY (p.tags)
      AND thetag2 = ANY (p.tags);
END $$;
SELECT * FROM tmp_table;
COMMIT;

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