Tom Lane wrote:
Sven Willenberger <sven@xxxxxxx> 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
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
