如何在PostgreSQL函数中创建列名数量动态生成的临时表

需求说明 #

基于PostgreSQL 14.9编写的PL/pgSQL函数fetch_rooms_energy，接收公寓ID（apartment）和日期（day）两个参数，需从时序表periodic_measurements中查询指定公寓的房间能耗总和，并以房间ID为列名的动态表格式返回结果，列数与列名随公寓房间数量动态变化，无需持久化临时表（仅函数运行期间有效）。

现有函数代码 #

CREATE FUNCTION fetch_rooms_energy (apartment INTEGER, day TEXT)
RETURNS FLOAT []
LANGUAGE plpgsql
AS
$$
DECLARE
    number_of_rooms INTEGER;
    room_ids INTEGER[];
    room_energy FLOAT(6);
    rooms_energy FLOAT(6) [];
BEGIN
    SELECT count(DISTINCT roomid) INTO number_of_rooms FROM periodic_measurements WHERE apartmentid = $1 AND roomid != 0;
    SELECT ARRAY(SELECT DISTINCT roomid INTO room_ids FROM periodic_measurements WHERE apartmentid = $1 AND roomid != 0 ORDER BY roomid);
    FOR counter IN 1..number_of_rooms LOOP
        SELECT SUM(mvalue) INTO room_energy FROM periodic_measurements WHERE (apartmentid = $1 AND roomid = room_ids[counter] AND metric = 5 AND mtimestamp::TEXT LIKE $2);
        rooms_energy = ARRAY_APPEND(rooms_energy, room_energy);
    END LOOP;
    RAISE NOTICE 'room ids: %', room_ids;
    RAISE NOTICE 'rooms energy: %', rooms_energy;
    RETURN rooms_energy;
END;
$$;

期望输出格式 #

room 18360 |      room 18361      |      room 18362       |        room 18363         |        room 18364         |      room 18365      |      room 18366       | room 18367 
------------+----------------------+-----------------------+---------------------------+---------------------------+----------------------+-----------------------+------------
          0 | 21340.55396146954036 | 21559.265370118024581 | 60753.0229418996635052412 | 46735.5408051978938452581 | 14539.59331341666434 | 22325.669210315860839 |           
(1 row)

解决方案 #

要实现动态列返回，需使用动态SQL构建行转列（PIVOT）逻辑，直接返回结果集而非数组，无需创建临时表（函数执行结束后结果集自动失效）。修改后的函数如下：

CREATE OR REPLACE FUNCTION fetch_rooms_energy(apartment INTEGER, day TEXT)
RETURNS SETOF RECORD
LANGUAGE plpgsql
AS $$
DECLARE
    room_columns TEXT;
    pivot_sql TEXT;
BEGIN
    -- 1. 动态生成列定义：将每个房间ID转为"room {id}"格式的列，对应能耗总和
    SELECT string_agg(
        format('SUM(CASE WHEN roomid = %s THEN mvalue ELSE NULL END) AS "room %s"', roomid, roomid),
        ', '
    ) INTO room_columns
    FROM (SELECT DISTINCT roomid FROM periodic_measurements WHERE apartmentid = $1 AND roomid != 0 ORDER BY roomid) AS rooms;

    -- 2. 构建完整的动态SQL语句，优化日期查询逻辑（避免LIKE匹配文本）
    pivot_sql := format(
        'SELECT %s
         FROM periodic_measurements
         WHERE apartmentid = $1 
           AND roomid != 0 
           AND metric = 5 
           AND mtimestamp >= $2::DATE 
           AND mtimestamp < $2::DATE + INTERVAL ''1 day''
         GROUP BY apartmentid',
        room_columns
    );

    -- 3. 执行动态SQL并返回结果集
    RETURN QUERY EXECUTE pivot_sql USING apartment, day;
END;
$$;

关键说明 #

1. 动态列生成：通过string_agg和format函数，将查询到的房间ID拼接为SUM(CASE...) AS "room {id}"的列表达式，实现列名和数量的动态化。
2. 日期查询优化：将原mtimestamp::TEXT LIKE $2改为日期范围查询，避免类型转换导致的索引失效，提升查询性能。
3. 安全参数传递：使用USING子句传递参数，避免SQL注入风险，同时简化动态SQL的参数拼接。
4. 调用方式：使用SETOF RECORD作为返回类型，调用时需指定列定义，例如：

   SELECT * FROM fetch_rooms_energy(123, '2024-05-20') 
   AS ("room 18360" FLOAT, "room 18361" FLOAT, ...);
   

   该方案无需临时表，函数执行结束后结果集自动失效，完全符合临时结果的需求。

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