Skip site navigation (1) Skip section navigation (2)

Re: Dereferencing a 2-dimensional array in plpgsql

From: Sven Willenberger <sven(at)dmv(dot)com>
To: pgsql-general(at)postgresql(dot)org
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: Re: Dereferencing a 2-dimensional array in plpgsql
Date: 2005-01-31 18:49:44
Message-ID: 41FE7DC8.9070505@dmv.com (view raw or flat)
Thread:
Lists: pgsql-general

Tom Lane wrote:
> Sven Willenberger <sven(at)dmv(dot)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

In response to

Responses

pgsql-general by date

Next:From: Shawn HarrisonDate: 2005-01-31 18:55:22
Subject: Re: Allowing update of column only from trigger
Previous:From: Shawn HarrisonDate: 2005-01-31 18:35:35
Subject: Re: Allowing update of column only from trigger

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group