Inside functions (plpgsql) and code blocks i tested that pgsql really doesn't need that arrays are initialized.
Try this: I just test array length before and after having appended an element.
do $$
declare ai int[];
begin
--BEFORE
raise notice 'ai length: %', array_length(ai,1);
--append the element (to the NON initialized array)
ai = array_append(ai,'2016');
--AFTER
raise notice 'ai length: %', array_length(ai,1);
end;
$$
No errors, and the result is:
NOTICE: ai length: <NULL>
NOTICE: ai length: 1
You can even access an out-of-bounds element wihtout exception thrown:
begin
raise notice 'ai length: %', array_length(ai,1);
raise notice 'element 99: %', ai[99];
Output:
NOTICE: ai length: NULL
NOTICE: element 99: NULL
So you start to think that arrays are somehow lazy-initialized, just use them. But that's not always the case, consider this code:
do $$
declare ai int[];
declare i int;
begin
foreach i in array ai loop
raise notice 'Element: %', i;
end loop;
end; $$
This does throw an error! The following:
ERROR: FOREACH expression must not be null
So, in some cases arrays have to be initialized. Let's check, and let's discover what the difference is between initialized and not:
do $$
declare ai int[];
declare x int;
declare i int;
begin
raise notice 'Array length: %', array_length(ai,1);
ai = array[] :: int[];
raise notice 'Array length: %', array_length(ai,1);
foreach i in array ai loop
raise notice 'Element: %', i;
end loop;
end;
$$
Output:
NOTICE: Array length: NULL
NOTICE: Array length: NULL
So: the line
ai = array[] :: int[];
works, infact the for loop doesn't throw the exception any more.
But, after initialization,
raise notice 'Array length: %', array_length(ai,1);
still gives 'null', an that is very strange.
The questions are: is it correct the way i initialize the array?
Is there an explanation for this strange behavior?
(Postgres Version: 9.5)
