Postgres使用dblink返回record报错：需列定义列表的解决咨询

错误原因 #

• Postgres的dblink函数执行远程查询后返回的是匿名record类型，若不明确指定返回结果的列定义（列名、数据类型），数据库无法识别结果结构，因此抛出a column definition list is required for functions returning "record"错误。
• 原语句直接拼接变量region到SQL字符串中，存在SQL注入风险，可能导致数据安全问题。

解决方法 #

1. 添加列定义列表 #

在dblink调用的末尾，为返回结果指定与远程查询完全匹配的列名和数据类型，格式为T(列名1 类型1, 列名2 类型2, ...)。

2. 安全传递变量 #

避免直接拼接字符串，使用参数化方式传递变量，防止SQL注入。以下是两种可行的写法：

写法一：使用format函数转义变量

SELECT array_to_json(ARRAY_AGG(row_to_json(T)))
INTO _units
FROM dblink(
    db_string,
    format('SELECT id, code, name, region::smallint
            FROM "myorg.lib.schema".units
            WHERE region::smallint = %L', region)
) T(id int, code text, name text, region smallint);

%L会自动对变量进行转义处理，避免SQL注入。

写法二：使用带参数的dblink调用（推荐）

Postgres 9.1及以上版本支持为dblink传递参数数组，通过$1、$2等占位符引用参数：

SELECT array_to_json(ARRAY_AGG(row_to_json(T)))
INTO _units
FROM dblink(
    db_string,
    'SELECT id, code, name, region::smallint
     FROM "myorg.lib.schema".units
     WHERE region::smallint = $1',
    ARRAY[region]::int[]
) T(id int, code text, name text, region smallint);

前置要求 #

确保已在当前数据库中安装dblink扩展：

CREATE EXTENSION IF NOT EXISTS dblink;

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