Thread: Populating a sparse array piecemeal in plpgsql

I have to count up a whole lot of things in a hurry.  But in counting them, I have to do a bit of analysis on each.

Each goober that I'm counting can be considered to have four characteristics.  The first three are binary:  it's either male or female, rich or poor, strong or weak.  The last characteristic in principal can be any integer value (let's call it age), but is bounded to be between 1 and 200.  In fact initially I am guaranteed that all the goobers have the same value for this fourth characteristic, so if necessary I can make a simplifying assumption in the short run.

What I need to generate is a count of the number of goobers in each category -- the number of male rich weak 27-year-old goobers, the number of female rich strong 30-year-old goobers, etc.  Unfortunately determining the values of these characteristics for each goober is not trivial; folding it all into a single SELECT would be pretty horrendous.

To get zippy performance, I'm writing a stored procedure.  Basically I want to select a whole lot of goobers, and then for each, determine the values of the four characteristic and increment the value of an element in a four-dimensional array.  However, in trying to implement even the simplest functionality with arrays, I run into errors like this:

CREATE FUNCTION func1()
RETURNS VOID AS $$
DECLARE
        a INTEGER[2][2][2][200];
BEGIN
        a[1][2][1][33] = 0;
        a[2][1][1][33] = 0;
END;
$$ LANGUAGE PLPGSQL;

When I run this function, I get:
ERROR:  array subscript out of range.

It's as if the first time I touch the array, it locks it down to being just that one particular slice, and no other elements.

(BTW I tried using index values of 0 and 1 instead of 1 and 2; no difference).  I've messed around with this a lot of different ways and keep getting told that I have a subscript out of range the second time I try to touch the array.

Sure, I could use a single-dimension array and do the indexing arithmetic myself, but surely I shouldn't have to.

Clearly I'm missing something here.

Topher
[]

"Eliot, Christopher" <christopher.eliot@nagrastar.com> writes:
> CREATE FUNCTION func1()
> RETURNS VOID AS $$
> DECLARE
>     a INTEGER[2][2][2][200];
> BEGIN
>     a[1][2][1][33] = 0;
>     a[2][1][1][33] = 0;
> END;
> $$ LANGUAGE PLPGSQL;

> When I run this function, I get:
> ERROR:  array subscript out of range.

Yeah.  I'm afraid that declaration is basically just noise: it doesn't
do anything for you that "DECLARE a integer[];" wouldn't do.  If you
want the array actually filled out to the indicated dimensions then
you have to do that by hand.

            regards, tom lane

Thank you for your reply, but I don't really understand how to use this
information.
My problem is that I can put one value into this array, and that's it.
Any subsequent attempts to put another value elsewhere in the array are
rebuffed, saying that the subscripts are out of range.  I don't
understand what I would do "by hand" that would help this.

Topher Eliot
christopher.eliot@nagrastar.com
[]

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Thursday, May 15, 2008 12:30 AM
> To: Eliot, Christopher
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Populating a sparse array piecemeal in plpgsql
>
> "Eliot, Christopher" <christopher.eliot@nagrastar.com> writes:
> > CREATE FUNCTION func1()
> > RETURNS VOID AS $$
> > DECLARE
> >     a INTEGER[2][2][2][200];
> > BEGIN
> >     a[1][2][1][33] = 0;
> >     a[2][1][1][33] = 0;
> > END;
> > $$ LANGUAGE PLPGSQL;
>
> > When I run this function, I get:
> > ERROR:  array subscript out of range.
>
> Yeah.  I'm afraid that declaration is basically just noise:
> it doesn't do anything for you that "DECLARE a integer[];"
> wouldn't do.  If you want the array actually filled out to
> the indicated dimensions then you have to do that by hand.
>
>             regards, tom lane
>

"Eliot, Christopher" <christopher.eliot@nagrastar.com> writes:
> Thank you for your reply, but I don't really understand how to use this
> information.
> My problem is that I can put one value into this array, and that's it.
> Any subsequent attempts to put another value elsewhere in the array are
> rebuffed, saying that the subscripts are out of range.  I don't
> understand what I would do "by hand" that would help this.

Well, the point is that you need to initialize the array as a whole to
have the dimensions you want; PG won't guess about this.  For instance
you could do something like

    declare a integer[];
    begin
      a := '{{null,null,null},{null,null,null},{null,null,null}}';

which sets up a null-filled 3x3 array that you can then manipulate
individual elements of.  For the sorts of dimensions you were showing,
writing out the initial value as a constant would be pretty dang
tedious, but you could build up the textual value in a loop and then
assign it.

To be honest, I wonder whether you are working in the wrong language.
PG (and therefore plpgsql) isn't very good with multidimensional arrays,
and especially not large ones.  Perhaps you ought to be pushing your
data around in pl/R or some such.

            regards, tom lane

Thanks, that worked.  I actually initialized it to zeros (so that my
incrementing would work).

Thanks for the example.  I wouldn't have figured out to put quote marks
around the initialization value otherwise.

Topher Eliot
christopher.eliot@nagrastar.com
[]