Search Postgresql Archives

Populating a sparse array piecemeal in plpgsql

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux