I have a table, which was loaded dynamically from an external job. This table has a changing number of coulmns from time to time.
I want to
unnest all the columns with the coulmn name as 'Key' and the value as 'Value':
SELECT unnest(array[(select array_agg( column_name::text )from information_schema.columns where table_name = 'tableName')]) AS "Keys", unnest(array[tableName.col1, tablename.col2, ...]) AS "Values" FROM tableName ;
The problem that I'm facing is that I need something to replace the second
unnest Statement, because I don't know the names of the columns.
I thought I just use
unnest(array[tableName.*]) as "Values"but that does not work.
Any ideas welcome.
create table mytest ( col_1 varchar(30), col_2 varchar(30)); insert into mytest values ('a1','b1'); insert into mytest values ('a2','b2'); insert into mytest values ('a3','b3'); SELECT unnest(array[(select array_agg( column_name::text )from information_schema.columns where table_name = 'mytest')]) AS "Key", unnest(array[mytest.col_1, mytest.col_2]) AS "Value" FROM mytest ;
"Key" "Value" col_1 a1 col_2 b1 col_1 a2 col_2 b2 col_1 a3 col_2 b3
which is fine.
But when I get a table of unknown numbers of columns I need something to put into the second unnest statement (
unnest(array[tableName.col1, tablename.col2, ...])) so I thought somethng like
tablename.* instead of