Re: Dereferencing a 2-dimensional array in plpgsql

Tom Lane wrote:
> Sven Willenberger <sven(at)dmv(dot)com> writes:
>
>>The problem stems from being unable to assign values to an array without
>>first initializing the array in plpgsql.
>
>
> I think we changed this in 8.0. Before 8.0, trying to assign to an
> element of a NULL array yielded a NULL result array, but I think now
> we let you produce a one-element array that way.
>
>
Using a 8.0 testbox I find that the arrays still need to be initialized:

DECLARE
blah varchar[];
foo varchar;
BEGIN
blah = ''{}'';
blah[1] := ''bar'';
foo := blah[1];
RAISE NOTICE ''blah[1] = %'',foo;
RETURN NULL;
END;

Will raise notice containing "bar".

DECLARE
blah varchar[];
foo varchar;
BEGIN
blah[1] := ''sven'';
foo := blah[1];
RAISE NOTICE ''blah[1] = %'',foo;
RETURN NULL;
END;

Will raise notice containing <null>.

Leaving the subscript off will initialize the variable with empty braces
or values within the braces; failure to have them results in "array
value must start with "{" or dimension information". Also, this only
applies to single-dimension arrays; I cannot find how to initialize
2-dimension arrays. As as a result, the only way I have seen to do this
then is to create 2 arrays, and having one array point to each row, one
by one, of the large master array. Keep in mind this is all in plpgsql.

Sven