Populating a sparse array piecemeal in plpgsql

From: "Eliot, Christopher" <christopher(dot)eliot(at)nagrastar(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Populating a sparse array piecemeal in plpgsql
Date: 2008-05-15 02:10:26
Message-ID: 5C27B2F8693FA3458E71B4A81551253F03646352@NSTAR-MAIL1.windows.nagrastar.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Pavel Stehule 2008-05-15 03:43:36 Re: postgres crash when select a record
Previous Message Greg Smith 2008-05-15 01:50:31 Re: convert very large unsigned numbers to base62?