Postgresql plpgsql. How to expand the second dimension of a two-dimensional array?

Question

Good afternoon. How to expand the second dimension of a two-dimensional array by standard means? The size of the array is not known in advance, so array_fill (null :: text, array [2,3]) is not applicable. I expand the first dimension with the standard "||". experimental code:

DO 
$$declare
    p_StrArr text[][];
begin
--    p_StrArr:=array[['a1', 'b1', 'c1'], ['a2', 'b2', 'c2'] ];
--    p_StrArr:=array_fill(null::text, array[2,3]);
    p_StrArr[1][1]:='a1';
    p_StrArr:=p_StrArr || array['a2'];
    p_StrArr:=p_StrArr || array['a3'];
-- if you uncomment, then
-- ERROR:  array subscript out of range
--    p_StrArr[1][2]:='b1';
    raise notice 'p_Str=%', p_StrArr; 
    raise notice 'p_Str=%', p_StrArr[1][1]; 
    raise notice 'p_Str=%', p_StrArr[2][1]; 
    raise notice 'c1=%', array_length(p_StrArr,1); 
    raise notice 'c2=%', array_length(p_StrArr,2); 
END$$;

the solution through a temporary array seems resource-intensive to me:

DO 
$$declare
    p_StrArr text[][];
    p_TmpArr text[][];
begin
    p_StrArr:=array[['a1', 'b1', 'c1'], ['a2', 'b2', 'c2'] ];
    raise notice 'p_Str=%', p_StrArr; 
    p_TmpArr:=array_fill(null::text, 
        array[array_length(p_StrArr,1),array_length(p_StrArr,2)+1]);
    for i in 1..array_length(p_StrArr,1) loop
        for j in 1..array_length(p_StrArr,2) loop
            p_TmpArr[i][j]:=p_StrArr[i][j];
        end loop;
    end loop;
    p_StrArr:=p_TmpArr;
    p_StrArr[1][4]:='d1';
    raise notice 'p_Str=%', p_StrArr; 
    raise notice 'c1=%', array_length(p_StrArr,1); 
    raise notice 'c2=%', array_length(p_StrArr,2); 
END$$;

is there a standard means?

score 0 · Accepted Answer · answered Aug 24 '21 at 08:56

There is not any buildin functionality for extending and array (you remember, the array have to be rectangular in any moment). I think so there are two possibilities, how to do it:

solution based on foreach slice clause:

DO
postgres=# do $$
declare 
  a int[];
  b int[];
  x int[];
begin
  a := array[[10,20],[30,40]];
  b := '{}';
  foreach x slice 1 in array a
  loop
      b := b || array[x || NULL::int];
  end loop;
  raise notice 'b %', b;
end;
$$;

NOTICE:  b {{10,20,NULL},{30,40,NULL}}
DO

In Postgres 14 (will be released next month), you can do rich array assignments:

postgres=# do $$
declare 
  a int[];
  b int[];
begin
  a := array[[10,20],[30,40]];
  b := array_fill(NULL::int, array[2,3]);
  b[1:2][1:2] := a;
  raise notice 'a = %', a;
  raise notice 'b = %', b;
end;
$$
;
NOTICE:  a = {{10,20},{30,40}}
NOTICE:  b = {{10,20,NULL},{30,40,NULL}}
DO

Thanks. your code is much more efficient than the one suggested by me with element-by-element copying (for 1000 cycles my code spends 946.801 ms, yours 26.929 ms). — MichaelN, Aug 24 '21 at 09:19

Postgresql plpgsql. How to expand the second dimension of a two-dimensional array?

1 Answers1