使用動態 unpivot 從多列中取消嵌套數組值

我收到了幾張寬格式的表格，我想將它們轉換為長格式。不幸的是，它們有大約 60 列。

範例輸入：

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

期望的輸出：

取消嵌套數組會產生正確的結果：

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

但是，考慮到要轉換的列和表的數量，我想使用參考表：

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

所以我的查詢變成了這樣：

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

但是，我不知道如何取消嵌套列值。而是返回作為字元串的列名：

以下工作，但我的重點是我想避免每次都寫出列名：

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

弄清楚了：

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;

引用自：https://dba.stackexchange.com/questions/300390