How to create a temporary table in a postgreSQL function which has columns names and amount that are generated dynamically by the function

Question

I have created a postgreSQL function which queries data from a time series database to provide a defined result. The function has two arguments: apartment id and day.

The function does the following:

• it finds the amount of rooms of a defined apartment
• it finds the ids of all the rooms of the apartment
• it finds the values related to a certain metric for the defined apartment and the defined day and calculates a SUM aggregate for each room
• it returns an array containing the calculated metric aggregate
• the room ids are contained in a separate array

The function is copied below:

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;
$$;

This is the result of running the function:

=> 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)

If I use this other command, I am able to get the result as a set of elements rather than as an array, which is a bit closer to the result I want to get:

=> 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)

However, I need to get the output of this function in table format as shown below in order to use it in my data visualization platform:

 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)

So, I would need to copy the result of the room_ids array into the column names of a temporary table and the content of the array rooms_energy into the values of the first row of such a table. Then I should change the table as the returning object of the function.

Is there a way to do this?

Any hint is appreciated!

Note that:

• each apartment might have a different number of rooms so the amount of columns is known only after the function has found it
• the columns names are knows only after the function has found them out
• the temporary table shall exist only for the time the function is running

I am running PostgreSQL 14.9 on Linux Ubuntu 22.04.

Thanks a lot in advance,

Bernardo

Answer 1

On relational databases more common way to handle data like that would be to have two columns roomid and room_energy.

And what's best there's even no need for complex loop logic in the function. This could be queried with a single query.

My best try to accomplish the same logic as your example:

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');

This produces the following result:

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