Обсуждение: Dereferencing a 2-dimensional array in plpgsql

I am having an issue with trying to dereference a 2-dimensional array in
plpgsql. The idea is to have an setup like:

DECLARE
myarray varchar[][];
myvar    char;
BEGIN
--stuff
myarray[1] := ''{value1,value2,value3}'';
myarray[2] := ''{valuea,valueb,valuec}'';

--If I then:

myvar := array[1][1];

--I get a subscript error generated. I have tried everycombination of
array[1:1][1], etc all to no avail.

I have also tried pre-initializing myarray with
myarray := ''{{}}'';

The docs seem to to indicate that a simple myarray[1][2] for example
should work, but that does not hold true in the plpgsql. Is there
another type of initialization that is needed to be done? Any advice?

Sven

Sven Willenberger <sven@dmv.com> writes:
> I am having an issue with trying to dereference a 2-dimensional array in
> plpgsql. The idea is to have an setup like:

> DECLARE
> myarray varchar[][];
> myvar    char;
> BEGIN
> --stuff
> myarray[1] := ''{value1,value2,value3}'';
> myarray[2] := ''{valuea,valueb,valuec}'';

> --If I then:

> myvar := array[1][1];

> --I get a subscript error generated.

That isn't a two-dimensional array, it's a one-dimensional array with
some curly braces in the element values.  Keep in mind that the number
of []s you write in the DECLARE is just decoration --- it's not enforced.
What's determining the actual array shape in this example is the
subscripts you write in the assignments.

            regards, tom lane

Tom Lane wrote:
> Sven Willenberger <sven@dmv.com> writes:
>
>>I am having an issue with trying to dereference a 2-dimensional array in
>>plpgsql. The idea is to have an setup like:
>
>
>>DECLARE
>>myarray varchar[][];
>>myvar    char;
>>BEGIN
>>--stuff
>>myarray[1] := ''{value1,value2,value3}'';
>>myarray[2] := ''{valuea,valueb,valuec}'';
>
>
>>--If I then:
>
>
>>myvar := array[1][1];
>
>
>>--I get a subscript error generated.
>
>
> That isn't a two-dimensional array, it's a one-dimensional array with
> some curly braces in the element values.  Keep in mind that the number
> of []s you write in the DECLARE is just decoration --- it's not enforced.
> What's determining the actual array shape in this example is the
> subscripts you write in the assignments.
>

The problem I seem to be having is initializing the array. For example
the following function:
create or replace function temp_keys() returns setof key_tuple as '
DECLARE
         myarray varchar[][];
         myother varchar;
         mytuple key_tuple;
         counter int;
BEGIN
         myarray[1][1] := ''sven'';
         myarray[1][2] := ''key18'';
         myarray[1][3] := ''A'';
         myarray[2][1] := ''dave'';
         myarray[2][2] := ''key18'';
         myarray[2][3] := ''B'';
         for counter in 1 .. 2 LOOP
                 myother := myarray[1][2];
                 RAISE NOTICE ''myother = %'',myother;
         END LOOP;
         mytuple.carrier := myarray[1][1];
         mytuple.prefix := myarray[1][2];
         mytuple.rate := myarray[1][3];
         RETURN NEXT mytuple;
         RETURN;
END;
' LANGUAGE plpgsql;

returns:

select * from temp_keys();
NOTICE:  myother = <NULL>
NOTICE:  myother = <NULL>
  holder | keynum | rating
--------+--------+--------
         |        |
(1 row)

However I have found that the following construct works, albeit very slowly:

DECLARE
    myarray varchar[][];
    subarray varchar[];
BEGIN
    --initialize the arrays
    myarray := ''{}'';
    subarray := ''{}'';
    myarray[1] := ''{sven,key18,A}'';
    myarray[2] := ''{dave,key18,b}'';
    subarray := myarray[1];
    RAISE NOTICE ''subarray = %'',subarray;
--snip


running this will return 'sven' in the NOTICE section.

The problem stems from being unable to assign values to an array without
first initializing the array in plpgsql. I can initialize
single-dimenstion arrays as noted, but any attempt to initaliaze and
populate 2-dimension arrays results in subscript and or <NULL> entry issues.

This is an offshoot of the moving backward/rewinding a cursor issue
about which I had inquired earlier and trying to load a table into an
array rather than reopening and closing a cursor thousands of times.
Turns out that using the construct above (with 2 arrays) works, but is
actually slower (??!!) than opening a cursor thousands of times.

Sven

Sven Willenberger <sven@dmv.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.

            regards, tom lane

Tom Lane wrote:
> Sven Willenberger <sven@dmv.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

Sven Willenberger <sven@dmv.com> writes:
> Tom Lane wrote:
>> 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:

[ looks at code... ]  Hmm, we changed the main executor but missed plpgsql.
I think this is a bug, since plpgsql is now inconsistent with what
happens in a SQL UPDATE command:

regression=# create table foo (f1 int[]);
CREATE TABLE
regression=# insert into foo values(null);
INSERT 155342 1
regression=# update foo set f1[1] = 33;
UPDATE 1
regression=# select * from foo;
  f1
------
 {33}
(1 row)

regression=#

I'll see about making plpgsql behave similarly for 8.0.2.

            regards, tom lane