如何实现返回动态类型列的PostgreSQL PL/pgSQL函数？

问题背景 #

需要编写PL/pgSQL函数，接收表名和列名参数，返回该列的所有数据，但列的类型是动态的（可能是int、float、numeric、timestamp等）。现有实现中：

• 已创建返回setof record的extract_column函数，查询时手动指定类型可正常运行，但无法从应用动态传入类型。
• 尝试编写get_type函数获取列类型，但无法在查询的别名定义中使用该函数，报错ERROR: type "get_type" does not exist。

核心问题 #

PostgreSQL在SQL解析阶段就需要确定返回结果的类型，而函数调用是在执行阶段才会计算结果，因此无法在as t(data get_type(...))这种语法中用函数动态指定类型。

修正get_type函数（可选） #

原get_type函数存在变量名冲突（table_name = table_name），且使用information_schema.columns的data_type可能不够精确（比如无法区分带精度的numeric类型），建议改用系统目录表获取精确类型：

create or replace function get_type(_table_name text, _col_name text)
returns regtype
language plpgsql as
$func$ 
declare column_type regtype;
begin 
    select atttypid::regtype
    from pg_attribute
    join pg_class on pg_attribute.attrelid = pg_class.oid
    join pg_namespace on pg_class.relnamespace = pg_namespace.oid
    where pg_class.relname = _table_name
      and pg_namespace.nspname = 'schema_name' -- 替换为你的目标schema
      and pg_attribute.attname = _col_name
      and pg_attribute.attnum > 0
      and not pg_attribute.attisdropped
    into column_type;

    return column_type;
end 
$func$;

解决方案 #

方案1：返回JSON类型（推荐） #

将列数据转换为JSON返回，应用层可自行解析为对应类型，无需关心原始类型：

create or replace function extract_column(_table_name text, _col_name text)
returns setof json
language plpgsql as
$func$
begin
    return query execute format(
        'select to_json(%I) FROM schema_name.%I',
        _col_name,
        _table_name
    );
end
$func$;

调用方式：

SELECT * FROM extract_column('my_table', 'column812');

方案2：应用层先获取类型再拼接查询 #

1. 先调用get_type获取列类型：

   SELECT get_type('my_table', 'column812');
   

2. 在应用层拼接出完整查询语句，示例：

   SELECT * FROM extract_column('my_table', 'column812') as t(data numeric); -- 替换为实际获取到的类型
   

方案3：使用多态函数返回原生类型 #

利用PostgreSQL的多态类型anyelement，让函数返回与列类型匹配的原生类型：

create or replace function extract_column(_table_name text, _col_name text, _sample anyelement default null)
returns setof anyelement
language plpgsql as
$func$
begin
    return query execute format(
        'select %I from schema_name.%I',
        _col_name,
        _table_name
    );
end
$func$;

调用时通过_sample参数指定类型（或直接cast结果）：

-- 方式1：通过_sample参数指定类型
SELECT * FROM extract_column('my_table', 'column812', 0::numeric);

-- 方式2：直接cast结果
SELECT extract_column('my_table', 'column812')::numeric;

方案4：使用游标返回动态结果 #

适合数据库端处理动态结果的场景：

create or replace function extract_column_cursor(_table_name text, _col_name text, out ref refcursor)
language plpgsql as
$func$
begin
    open ref for execute format(
        'select %I from schema_name.%I',
        _col_name,
        _table_name
    );
end
$func$;

调用方式：

begin;
select extract_column_cursor('my_table', 'column812');
fetch all from "<返回的游标名称>"; -- 替换为实际返回的游标名
commit;

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