Search code examples
postgresqlforeachplpgsqlrowtypepostgres-14

How to loop through columns within a row

I have a row, let it be in this format

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;

And I need to insert one row per column into t2

t2 TABLE 
column_name TEXT, value JSONB

Excepted result:

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

How can I do it?

Solution

  • No need for PL/pgSQL or a loop. You can convert the row from t1 to a JSON value, then turn those key/value pairs into rows:

    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;