如何在PostgreSQL中基于另一表的point数据动态创建目标表

实现方案 #

PostgreSQL的静态SQL要求查询返回结构在执行前确定，因此纯静态语句无法实现动态列的行转列需求，可通过动态SQL+PL/pgSQL结合crosstab实现，步骤如下：

前置准备 #

首先启用tablefunc扩展：

CREATE EXTENSION IF NOT EXISTS tablefunc;

假设你的源表名称为source_table，location字段格式固定为point x,y（x为行号，y为列号）。

方案1：直接生成动态查询语句 #

执行以下DO块会自动生成适配当前数据列的查询语句，复制输出的SQL直接运行即可得到结果：

DO $$
DECLARE
    col_list text;
    dynamic_sql text;
BEGIN
    -- 自动提取所有不重复的y坐标作为列，生成列定义
    SELECT string_agg(DISTINCT quote_ident(col_num::text) || ' text', ', ' ORDER BY quote_ident(col_num::text) || ' text')
    INTO col_list
    FROM (
        SELECT split_part(split_part(location, ' ', 2), ',', 2)::int AS col_num
        FROM source_table
    ) t;

    -- 拼接完整的crosstab动态查询
    dynamic_sql := format(
        'SELECT * FROM crosstab(
            ''SELECT split_part(split_part(location, '''' '''', 2), '''','''', 1)::int AS row_num,
                    split_part(split_part(location, '''' '''', 2), '''','''', 2)::int AS col_num,
                    data::text
             FROM source_table
             ORDER BY 1, 2''
        ) AS ct (%s);',
        col_list
    );

    -- 打印生成的SQL，复制后直接运行即可得到结果
    RAISE NOTICE '生成的动态查询语句：%', dynamic_sql;
END $$;

方案2：封装为可直接调用的函数 #

如果需要重复调用，可以封装为返回游标的函数：

CREATE OR REPLACE FUNCTION dynamic_pivot_source() RETURNS refcursor AS $$
DECLARE
    col_list text;
    dynamic_sql text;
    res_cursor refcursor := 'pivot_result';
BEGIN
    SELECT string_agg(DISTINCT quote_ident(col_num::text) || ' text', ', ' ORDER BY quote_ident(col_num::text) || ' text')
    INTO col_list
    FROM (
        SELECT split_part(split_part(location, ' ', 2), ',', 2)::int AS col_num
        FROM source_table
    ) t;

    dynamic_sql := format(
        'SELECT * FROM crosstab(
            ''SELECT split_part(split_part(location, '''' '''', 2), '''','''', 1)::int AS row_num,
                    split_part(split_part(location, '''' '''', 2), '''','''', 2)::int AS col_num,
                    data::text
             FROM source_table
             ORDER BY 1, 2''
        ) AS ct (%s);',
        col_list
    );

    OPEN res_cursor FOR EXECUTE dynamic_sql;
    RETURN res_cursor;
END $$ LANGUAGE plpgsql;

调用方法：

BEGIN;
SELECT dynamic_pivot_source();
FETCH ALL IN "pivot_result";
COMMIT;

特性说明 #

• 自动识别源表中所有的y坐标作为列名，按数值从小到大排序，新增y坐标会自动生成对应列，无需修改代码
• 所有列的类型统一为text，符合需求
• 自动匹配对应行号的x坐标值，空值位置会自动填充NULL

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