Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table my_table("US tank" text,	"DS tank" text,	"US batch n°" integer,	"DS batch n°" integer) ;

insert into my_table values
('citerne',	'B430',	122,	55),
('B430',	'K4',	55,	603);

CREATE TABLE



INSERT 0 2


SELECT * FROM my_table


US tank
DS tank
US batch n°
DS batch n°


citerne
B430
122
55

B430
K4
55
603



SELECT 2


CREATE OR REPLACE PROCEDURE create_composite_type() LANGUAGE plpgsql AS $$
DECLARE
  column_list text ;
BEGIN
  SELECT string_agg(DISTINCT quote_ident(t.tank) || ' text', ',')
    INTO column_list
    FROM ( SELECT "US tank" FROM my_table
           UNION ALL 
           SELECT "DS tank" FROM my_table
         ) AS t(tank) ;
  
  EXECUTE 'DROP TYPE IF EXISTS composite_type' ;
  EXECUTE 'CREATE TYPE composite_type AS (' || column_list || ')' ;
END ;
$$ ;

CREATE PROCEDURE


CALL create_composite_type() ;

SELECT (jsonb_populate_record( null :: composite_type
                             , jsonb_object_agg(t.tank, t.batch)
                             )
       ).*
 FROM ( SELECT "US tank" AS tank, "US batch n°" AS batch FROM my_table
        UNION 
        SELECT "DS tank", "DS batch n°" FROM my_table
      ) AS t ;

CALL




B430
citerne
K4


55
122
603



SELECT 1