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