PostgreSQL中如何实现按列位置批量选取列的便捷函数？

PostgreSQL没有内置函数直接支持SELECT FUNCTION(21, 65) FROM table这种按列位置范围查询的语法——因为SQL本身是基于列名而非位置设计的。但可以通过自定义PL/pgSQL函数结合动态SQL优化现有方案，让调用更简洁。

1. 简化参数的自定义函数（推荐） #

创建一个函数，仅需传入列索引范围和表名（schema可选，默认使用当前schema），自动生成并执行查询：

CREATE OR REPLACE FUNCTION get_columns_by_pos(start_idx INT, end_idx INT, table_name TEXT, schema_name TEXT DEFAULT current_schema())
RETURNS SETOF RECORD
LANGUAGE plpgsql
AS $$
DECLARE
    col_list TEXT;
BEGIN
    -- 获取指定位置范围内的列名，按位置排序
    SELECT string_agg(quote_ident(column_name), ', ')
    INTO col_list
    FROM information_schema.columns
    WHERE table_schema = schema_name
      AND table_name = table_name
      AND ordinal_position BETWEEN start_idx AND end_idx
    ORDER BY ordinal_position;

    -- 动态执行查询并返回结果
    RETURN QUERY EXECUTE format('SELECT %s FROM %I.%I', col_list, schema_name, table_name);
END;
$$;

调用示例 #

如果表在当前schema下：

-- 需要指定返回的列类型（因为返回SETOF RECORD）
SELECT * FROM get_columns_by_pos(21, 65, 'your_table') AS t(col21 INT, col22 TEXT, col23 DATE, ...);

如果表在其他schema下：

SELECT * FROM get_columns_by_pos(21, 65, 'your_table', 'target_schema') AS t(...);

2. 返回JSON格式避免指定列类型 #

如果不想手动指定返回列的类型，可以让函数返回JSON格式，调用更灵活：

CREATE OR REPLACE FUNCTION columns_by_pos_json(start_idx INT, end_idx INT, table_name TEXT, schema_name TEXT DEFAULT current_schema())
RETURNS SETOF JSON
LANGUAGE plpgsql
AS $$
DECLARE
    col_list TEXT;
BEGIN
    SELECT string_agg(quote_ident(column_name), ', ')
    INTO col_list
    FROM information_schema.columns
    WHERE table_schema = schema_name
      AND table_name = table_name
      AND ordinal_position BETWEEN start_idx AND end_idx
    ORDER BY ordinal_position;

    RETURN QUERY EXECUTE format('SELECT row_to_json(t) FROM (SELECT %s FROM %I.%I) t', col_list, schema_name, table_name);
END;
$$;

调用示例 #

SELECT columns_by_pos_json(21, 65, 'your_table');

3. 注意事项 #

• 健壮性问题：列位置会随表结构变更（如添加、删除列）改变，可能导致查询结果不符合预期，建议优先使用列名查询。
• 权限要求：执行函数的用户需要拥有目标表的查询权限，以及information_schema.columns的访问权限。
• 动态SQL风险：函数使用动态SQL，上述代码通过quote_ident和format函数已做安全处理，避免SQL注入风险。

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