PostgreSQL结合dblink：如何处理多字段合并返回问题？

我编写了如下SQL查询，用于从所有包含Operacao表的本地数据库中收集数据：

select
    db_name, 
    unnest((select *
     from dblink(conn_string, 
        'select array_agg(row(opr.id, opr.descricao))
         from Operacao opr') 
    as (result text array))) as result
from (
    select 
        db_name, 
        table_name,
        conn_string,
        (select table_exists
         from dblink(db.conn_string, 
         '(select Count(*) > 0 from information_schema.tables where table_name = ''' || db.table_name || ''')') as (table_exists Boolean))
    from (
        SELECT 
            datname as db_name,
            'user=postgres password=postgres dbname=' || datname as conn_string,
            'operacao'::text as table_name
        FROM pg_database
        WHERE datistemplate = false
        limit 4
    ) db
) db
where table_exists;

该查询能正常运行，但id和descricao字段被合并为单个列。我尝试过修改result text array的多种形式，却无法将结果转换为记录。请问除了字符串处理外，是否有其他方法拆分这些列？

解决方案：使用明确的类型接收dblink返回值 #

核心问题是用text array接收行类型数组时，行被强制转为文本格式合并成单一值。可以通过以下两种非字符串处理的方式解决：

方法1：定义临时记录类型匹配字段 #

先创建和Operacao表返回字段匹配的临时类型，再用该类型的数组接收结果：

-- 定义临时类型，需与Operacao表的id、descricao字段类型一致（示例为INT和TEXT，可按需调整）
CREATE TYPE operacao_row AS (id INT, descricao TEXT);

select
    db_name, 
    -- unnest后直接拆分出id和descricao字段
    (unnest((select *
     from dblink(conn_string, 
        'select array_agg((opr.id, opr.descricao)::operacao_row)
         from Operacao opr') 
    as (result operacao_row[])))).*
from (
    select 
        db_name, 
        table_name,
        conn_string,
        (select table_exists
         from dblink(db.conn_string, 
         '(select Count(*) > 0 from information_schema.tables where table_name = ''' || db.table_name || ''')') as (table_exists Boolean))
    from (
        SELECT 
            datname as db_name,
            'user=postgres password=postgres dbname=' || datname as conn_string,
            'operacao'::text as table_name
        FROM pg_database
        WHERE datistemplate = false
        limit 4
    ) db
) db
where table_exists;

-- 可选：用完后删除临时类型
DROP TYPE operacao_row;

方法2：直接关联dblink返回的行集合 #

无需使用array_agg，直接将dblink返回的多行结果作为表关联查询，写法更简洁：

select
    db.db_name,
    opr.id,
    opr.descricao
from (
    select 
        db_name, 
        conn_string,
        table_name
    from (
        SELECT 
            datname as db_name,
            'user=postgres password=postgres dbname=' || datname as conn_string,
            'operacao'::text as table_name
        FROM pg_database
        WHERE datistemplate = false
        limit 4
    ) db
    where (select table_exists
           from dblink(db.conn_string, 
           '(select Count(*) > 0 from information_schema.tables where table_name = ''' || db.table_name || ''')') as (table_exists Boolean))
) db
-- 直接关联dblink的查询结果，指定字段类型
join dblink(db.conn_string, 'select id, descricao from Operacao') 
    as opr(id INT, descricao TEXT) on true;

说明 #

• 方法1适合需要批量处理数组的场景，自定义类型必须和目标字段类型严格匹配；
• 方法2更直观，避免了数组转换的复杂操作，是更推荐的常规写法。

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