如何循环穿过一排的列

Question

我有一行，让它以这种格式

DECLARE
    a t1%ROWTYPE;
BEGIN 
    SELECT * INTO a FROM t1 WHERE id=<some_id> 
    -- a = id: <some_id>, name: "some_name", description: "some_descr"
END;

，我需要将每列的一行插入t2

t2 TABLE 
column_name TEXT, value JSONB

异常结果：

column_name | value
--------------------
id          | '"some_id"'
name        | '"some_name"'
description | '"some_descr"'

我该怎么做？

Answer 1

无需PL/PGSQL或循环。您可以将行从T1转换为JSON值，然后将这些密钥/值对转换为行：

insert into t2 (column_name, value)
select x.col, to_jsonb(x.val)
from t1 
   cross join jsonb_each_text(to_jsonb(t1)) as x(col, val)
where t1.id = 42;