Unnest array values from multiple columns with dynamic unpivot

Question

I have been given several tables in wide form that I would like to convert to long form. Unfortunately, they have about 60 columns.

Example input:

CREATE TABLE tbl_wide (
 p_key integer PRIMARY KEY,
 col_a integer,
 col_b integer,
 col_c integer);
 
INSERT INTO tbl_wide (p_key, col_a, col_b, col_c)
VALUES (1,10,20,30),(2,50,10,5);

p_key	col_a	col_b	col_c
1	10	20	30
2	50	10	5

Desired output:

f_key	col	val
1	a	10
1	b	20
1	c	30
2	a	50
2	b	10
2	c	5

Unnesting the arrays produces the correct result:

SELECT p_key as f_key,
 unnest(array['a','b','c']) col,
 unnest(array[col_a, col_b, col_c]) val
FROM tbl_wide

However, given the number of columns and tables to convert I would like to use a reference table:

CREATE TABLE tbl_reference (
 col_identifier text primary key,
 list_of_cols text);
 
INSERT INTO tbl_reference (col_identifier, list_of_cols)
VALUES ('a','col_a'),('b','col_b'),('c','col_c');

col_identifier	list_of_cols
a	col_a
b	col_b
c	col_c

So my query becomes something like:

SELECT p_key as f_key,
 unnest((SELECT array_agg(col_identifier) FROM tbl_reference)) col,
 unnest((SELECT array_agg(list_of_cols) FROM tbl_reference)) val
FROM tbl_wide

However, I can't figure out how to unnest the column values. The column names as strings are returned instead:

f_key	col	val
1	a	col_a
1	b	col_b
1	c	col_c
2	a	col_a
2	b	col_b
2	c	col_c

The following works but I the point is I want to avoid having to write out the column names each time:

SELECT p_key as f_key,
 unnest((SELECT array_agg(col_identifier) FROM tbl_reference)) col,
 unnest(array[col_a, col_b, col_c]) val
FROM tbl_wide

Answer 1

Figured it out:

SELECT p_key AS f_key, col_identifier AS col, val::int
FROM tbl_wide
 CROSS JOIN LATERAL jsonb_each_text(to_jsonb(tbl_wide) - 'p_key') AS un(col_name, val)
 JOIN tbl_reference ON col_name = list_of_cols
 ORDER BY f_key, col;