How to assign array to JSONB variable in postgres script

62 views Asked by At

I want to adjust a JSONB column by adding a new array prop in an existing record, but I struggle with getting this updated record.

Something like this:

DO $$ 
DECLARE
  mt table1%ROWTYPE;
  arr TEXT[];
BEGIN
    FOR mt IN 
        SELECT * FROM table1
    LOOP
        arr := ARRAY(SELECT col1 FROM table2 WHERE id_ext=mt.id);
        
        raise notice 'arr=%', arr;   -- nice array printed here

        mt.column.arr_prop=arr;   -- !!!!! This does not work

        UPDATE table1 SET column=mt.column WHERE id=mt.id;

    END LOOP;
END $$;
0

There are 0 answers