psql function: append one dimensional array to a two dimensional array using array_append

Question

I tried to append an one dimensional array to a two dimensional array using array_append in psql function. I defined count_values as output by out count_values integer[][], cout_value as variable in declare section by count_value integer[], and tried to use count_values := array_append(count_values, count_value), but the error function array_append(integer[], integer[]) does not exist poped up.

 if ...
 else
 count_value:= array_append(count_value, 0);
 end if;
 index:= index + 1;
 end loop;
 
 count_values:= array_append(count_values, count_value);
 end loop;
...

• Gerard H. Pille: "An array can also be constructed by using the functions array_prepend, array_append, or array_cat. The first two only support one-dimensional arrays, but array_cat supports multidimensional arrays. " (postgresql.org/docs/13/arrays.html)

Answer 1

See this fiddle

create function f() returns integer[][]
as $$
declare
 count_value integer[] = '{5,6}';
 count_values integer[][] = '{{1,2},{3,4}}';
begin
 return Array_cat(count_values,count_value);
end; $$
language plpgsql;

or, starting with an empty array,

create function f() returns integer[][]
as $$
declare
 count_value integer[] = '{5,6}';
 count_values integer[][] ;
 new_cvs integer[][];
begin
 if count_values is null
 then new_cvs = array_cat(ARRAY[]::integer[][],ARRAY[count_value]);
 else new_cvs = Array_cat(count_values,count_value);
 end if ;
 return new_cvs;
end; $$
language plpgsql;

see another fiddle

• Erwin Zangwill: The problem is that, my two dimensional array will be empty at first beginning, and psql seems could not recognize that it is two dimensional
• Gerard H. Pille: Then there is the possibility of telling postgresql that your array is two dimensional. Have you tried that?
• Gerard H. Pille: Made the example a little smarter.
• Erwin Zangwill: Am I wrong with the cursor statements?
• Gerard H. Pille: Can we try solving one problem at a time?