PostgreSQL 13.5+DBeaver中如何用变量替代重复表名？

一、原生SQL是否支持？ #

PostgreSQL的原生静态SQL不支持直接用变量替换表名。因为SQL在解析阶段需要确定所有对象（如表、列）的存在性，表名属于标识符，无法通过普通SET设置的会话变量直接替换——这类变量仅能用于值的替换，不能替换标识符。

二、DBeaver提供的便捷方式 #

DBeaver内置了变量替换功能，无需编写动态SQL就能实现需求，有两种常用方式：

1. 执行时手动指定表名 #

在查询中用${TABLENAME}标记需要替换的表名，执行时DBeaver会自动弹出输入框让你指定目标表名：

select 
    rr.jdoc as child_node, json_agg(parent_rr.jdoc)::jsonb as parent_node, array_length(array_agg(parent_rr.jdoc)::jsonb[], 1) as count
from ${TABLENAME} rr, ${TABLENAME} parent_rr
where 
   parent_rr.jdoc @> (rr.jdoc->'somefield')::jsonb 
group by rr.jdoc

UNION 

select rr.jdoc, NULL as parent_id, null as pcount
from ${TABLENAME} rr where 
not (rr.jdoc ?? 'somefield') 
and ((rr.jdoc->'crazyfield'->>'doublecrazyfield')<>'gotyou')

2. 提前定义会话变量 #

先执行以下语句设置变量（仅当前会话有效）：

SET @TABLENAME = 'CUS_DELTA';

再修改查询使用@TABLENAME作为表名占位符，执行时DBeaver会自动替换为设置的值：

select 
    rr.jdoc as child_node, json_agg(parent_rr.jdoc)::jsonb as parent_node, array_length(array_agg(parent_rr.jdoc)::jsonb[], 1) as count
from @TABLENAME rr, @TABLENAME parent_rr
where 
   parent_rr.jdoc @> (rr.jdoc->'somefield')::jsonb 
group by rr.jdoc

UNION 

select rr.jdoc, NULL as parent_id, null as pcount
from @TABLENAME rr where 
not (rr.jdoc ?? 'somefield') 
and ((rr.jdoc->'crazyfield'->>'doublecrazyfield')<>'gotyou')

三、PostgreSQL动态SQL/PLSQL实现方案 #

如果需要在数据库层面实现（不依赖客户端工具），可以创建一个返回结果集的函数，或者用临时执行的DO块：

1. 创建可复用的函数 #

该函数接收表名参数，动态生成并执行查询，返回与原查询一致的结果结构：

CREATE OR REPLACE FUNCTION get_node_info(p_tablename text)
RETURNS TABLE(
    child_node jsonb,
    parent_node jsonb,
    count integer
) AS $$
BEGIN
    RETURN QUERY EXECUTE format('
        select 
            rr.jdoc as child_node, json_agg(parent_rr.jdoc)::jsonb as parent_node, array_length(array_agg(parent_rr.jdoc)::jsonb[], 1) as count
        from %I rr, %I parent_rr
        where 
           parent_rr.jdoc @> (rr.jdoc->''somefield'')::jsonb 
        group by rr.jdoc

        UNION 

        select rr.jdoc, NULL as parent_node, null as count
        from %I rr where 
        not (rr.jdoc ?? ''somefield'') 
        and ((rr.jdoc->''crazyfield''->>''doublecrazyfield'')<>''gotyou'')
    ', p_tablename, p_tablename, p_tablename);
END;
$$ LANGUAGE plpgsql;

• 说明：%I是format函数的安全占位符，用于格式化表名这类标识符，避免SQL注入风险。

调用函数

只需传入目标表名即可获取结果：

-- 查询CUS_DELTA表
SELECT * FROM get_node_info('CUS_DELTA');

-- 查询其他表，如OTHER_TABLE
SELECT * FROM get_node_info('OTHER_TABLE');

2. 临时执行动态SQL（DO块） #

如果只是临时执行不需要保存函数，可以用DO块将结果存入临时表后查询：

DO $$
DECLARE
    v_tablename text := 'CUS_DELTA';
BEGIN
    -- 创建临时表存储结果，会话结束自动删除
    CREATE TEMP TABLE temp_node_info ON COMMIT DROP AS
    EXECUTE format('
        select 
            rr.jdoc as child_node, json_agg(parent_rr.jdoc)::jsonb as parent_node, array_length(array_agg(parent_rr.jdoc)::jsonb[], 1) as count
        from %I rr, %I parent_rr
        where 
           parent_rr.jdoc @> (rr.jdoc->''somefield'')::jsonb 
        group by rr.jdoc

        UNION 

        select rr.jdoc, NULL as parent_node, null as count
        from %I rr where 
        not (rr.jdoc ?? ''somefield'') 
        and ((rr.jdoc->''crazyfield''->>''doublecrazyfield'')<>''gotyou'')
    ', v_tablename, v_tablename, v_tablename);
END $$;

-- 查看临时表结果
SELECT * FROM temp_node_info;

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