CODEWITHSUNDEEP
sqlpostgresqlpivotgrafanasupabase
Jakub Szlaur
Jakub Szlaur

Reputation: 2132

How to dynamically add columns in PostgresSQL select?

I have this table:

+------+----+-------+
| Time | Id | Value |
+------+----+-------+
| 1    | 1  | 0     |
+------+----+-------+
| 2    | 1  | 10    |
+------+----+-------+
| 3    | 1  | 20    |
+------+----+-------+
| 1    | 2  | 0     |
+------+----+-------+
| 2    | 2  | -10   |
+------+----+-------+
| 3    | 2  | -20   |
+------+----+-------+
| 1    | 3  | 0     |
+------+----+-------+
| 2    | 3  | 0     |
+------+----+-------+
| 3    | 3  | 0     |
+------+----+-------+

I want to create an SQL select statement that would return me this table:

+------+--------+--------+--------+
| Time | Value1 | Value2 | Value3 |
+------+--------+--------+--------+
| 1    | 0      | 0      | 0      |
+------+--------+--------+--------+
| 2    | 10     | -10    | 0      |
+------+--------+--------+--------+
| 3    | 20     | -20    | 0      |
+------+--------+--------+--------+

What I want to achieve? The SQL statement should be working with the ID's dynamically (meaning that the number of returned columns will change based on how many different ID's there are).

What I have tried? I have tried various SQL statements but my knowledge is fairly limited and I can't do it on my own ... Group by time gives me ID and Value column in only aggregate form.

Other info:

Upvotes: 0

Views: 1099

Answers (1)

Edouard
Edouard

Reputation: 7065

Displaying a various list of columns whose number and labels are known only at the runtime is possible but not so obvious with postgres. You need some dynamic sql code.

Here is a full dynamic solution which relies on the creation of a user-defined composite type and on the standard functions jsonb_populate_record and jsonb_object_agg :

First you create the procedure that will create / update the list of ids as a composite type :

CREATE OR REPLACE PROCEDURE id_list () LANGUAGE plpgsql AS
$$
DECLARE id_list text ;
BEGIN
  SELECT string_agg(DISTINCT quote_ident('value' || id :: text) || ' integer', ',')
    INTO id_list
    FROM your_table ;

  EXECUTE 'DROP TYPE IF EXISTS id_list ' ;
  EXECUTE 'CREATE TYPE id_list AS (' || COALESCE(id_list, '') || ')' ;
END ;
$$ ;
CALL id_list () ; -- set up the type id_list with the existing list of ids

Then you can update the type id_list () by trigger when the list of ids is supposed to be modified in the database :

CREATE OR REPLACE FUNCTION your_table_insert_update()
RETURNS trigger LANGUAGE plpgsql VOLATILE AS
$$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM your_table WHERE id = NEW.id)
  THEN EXECUTE 'ALTER TYPE id_list ADD ATTRIBUTE ' || quote_ident('value' || NEW.id) || ' integer' ;
  END IF ;
  RETURN NEW ;
END ;
$$ ;

CREATE OR REPLACE TRIGGER your_table_insert_update BEFORE INSERT OR UPDATE OF id ON your_table 
FOR EACH ROW EXECUTE FUNCTION your_table_insert_update() ;

CREATE OR REPLACE FUNCTION your_table_delete()
RETURNS trigger LANGUAGE plpgsql VOLATILE AS
$$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM your_table WHERE id = OLD.id)
  THEN EXECUTE 'ALTER TYPE id_list DROP ATTRIBUTE IF EXISTS ' || quote_ident('value' || OLD.id) || ' integer' ;
  END IF ;
  RETURN OLD ;
END ;
$$ ;

CREATE OR REPLACE TRIGGER your_table_delete AFTER DELETE ON your_table 
FOR EACH ROW EXECUTE FUNCTION your_table_delete() ;

Finally, you should get the expected result with the following query :

SELECT time
     , (jsonb_populate_record(NULL :: id_list, jsonb_object_agg('value' || id :: text, value))).*
  FROM your_table AS t
 GROUP BY time
 ORDER BY time

full test result in dbfiddle.

Upvotes: 1

Related Questions