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:
> 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,nul l}}';

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general