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
[]
