如何在PostgreSQL函数中创建一个临时表，该表具有由函数动态生成的列名和数量

我创建了一个PostgreSQL函数，它从时间序列数据库中查询数据，以提供定义的结果。该函数有两个参数：公寓ID和日期。
该函数执行以下操作：

它计算一个已定义的公寓的房间数
它找到公寓所有房间的ID
它查找与定义的公寓和定义的日期的特定度量相关的值，并计算每个房间的SUM聚合
它返回一个数组，其中包含计算的度量聚合
房间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;
$$;

这是运行函数的结果：

=> SELECT fetch_rooms_energy (1931, '2023-04-01%');

NOTICE:  room ids: {18360,18361,18362,18363,18364,18365,18366,18367}
NOTICE:  rooms energy: {0,21340.555,21559.266,60753.023,46735.54,14539.594,22325.67,NULL}
                                     fetch_rooms_energy                                      
---------------------------------------------------------------------------------------------
 {0,21340.5546875,21559.265625,60753.0234375,46735.5390625,14539.59375,22325.669921875,NULL}
(1 row)

如果我使用另一个命令，我可以得到一组元素的结果，而不是一个数组，这更接近我想要得到的结果：

=> SELECT UNNEST(fetch_rooms_energy (1931, '2023-04-01%'));

NOTICE:  room ids: {18360,18361,18362,18363,18364,18365,18366,18367}
NOTICE:  rooms energy: {0,21340.555,21559.266,60753.023,46735.54,14539.594,22325.67,NULL}
     unnest      
-----------------
               0
   21340.5546875
    21559.265625
   60753.0234375
   46735.5390625
     14539.59375
 22325.669921875
                
(8 rows)

但是，我需要以表格形式获取此函数的输出，如下所示，以便在我的数据可视化平台中使用它：

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)

因此，我需要将room_ids数组的结果复制到临时表的列名中，并将rooms_energy数组的内容复制到这样一个表的第一行的值中。然后我应该将表更改为函数的返回对象。
有没有办法做到这一点？
任何提示是赞赏！
请注意：

每个公寓可能有不同数量的房间，因此只有在函数找到它之后才知道列的数量
*只有在函数找到它们之后才知道列名
*临时表仅在函数运行时存在

我在Linux Ubuntu 22.04上运行PostgreSQL 14.9。
先谢谢你了，
Bernardo

在关系型数据库中，更常见的处理此类数据的方法是使用两列roomid和room_energy。
最好的是，函数中甚至不需要复杂的循环逻辑。这可以用一个查询来查询。
我最好尝试实现与您的示例相同的逻辑：

CREATE FUNCTION fetch_rooms_energy(apartment int, day text)
RETURNS TABLE(roomid int, energy float)
LANGUAGE sql
AS
$$
  SELECT
    DISTINCT ON (roomid)
    roomid,
    (
      SELECT sum(mvalue)
      FROM periodic_measurements b
      WHERE
        a.apartmentid = b.apartmentid
        AND a.roomid = b.roomid
        AND metric = 5
        AND mtimestamp::TEXT LIKE $2
    ) room_energy
  FROM
    periodic_measurements a
  WHERE
    apartmentid = $1
    AND roomid != 0
  ;
$$
;

SELECT * FROM fetch_rooms_energy_(1, '2023-09-22 12:00:00');

这将产生以下结果：

roomid | energy
--------+--------
      1 |
      2 |    8.5
      3 |    8.5

如何在PostgreSQL函数中创建一个临时表，该表具有由函数动态生成的列名和数量

1条答案

相关问题

热门标签

最新问答