Re: Problem with array subscripts in plpgsql trigger function

Aaron Bono wrote:
> On 7/5/06, *Erik Jones* <erik(at)myemma(dot)com <mailto:erik(at)myemma(dot)com>> wrote:
>
> Aaron Bono wrote:
> > On 7/5/06, *Erik Jones* <erik(at)myemma(dot)com
> <mailto:erik(at)myemma(dot)com> <mailto:erik(at)myemma(dot)com
> <mailto:erik(at)myemma(dot)com>>> wrote:
> >
> > Ok, I have a trigger set up on the following (stripped down)
> table:
> >
> > CREATE TABLE members (
> > member_id bigint,
> > member_status_id smallint,
> > member_is_deleted boolean
> > );
> >
> > Here's a shortened version of the trigger function:
> >
> > CREATE OR REPLACE FUNCTION update_member() RETURNS TRIGGER
> AS $um$
> > DECLARE
> > status_deltas integer[];
> > BEGIN
> > IF(NEW.member_status_id != OLD.member_status_id AND
> > NEW.member_is_deleted IS NOT TRUE) THEN
> > status_deltas[NEW.member_status_id] := 1;
> > status_deltas[OLD.member_status_id] := -1;
> > END IF;
> > /*and after a couple more such conditional assignments I
> use the
> > values in status_deltas to update another table holding status
> > totals here*/
> > END;
> > $um$ LANGUAGE plpgsql;
> >
> > on the two lines that access set array values I'm getting the
> > following
> > error:
> >
> > ERROR: invalid array subscripts
> >
> > What gives?
> >
> >
> >
> > What values are being used for member_status_id?
> >
> 1, 2, and 3
>
>
> I did some digging through the documentation and cannot find any
> examples of using arrays like this. Do you have to initialize the
> array before you use it?
>
> Does anyone know where to look for informaiton about using arrays in
> stored procedures?
>
> -Aaron
>
Ok, that was apparently it. I found <a
href="http://archives.postgresql.org/pgsql-general/2005-02/msg01270.php">this</a>
thread in the archive which indicated to me that without first
initializing the array the bounds weren't set and thus a subscript error
if each new index isn't sequential.

--
erik jones <erik(at)myemma(dot)com>
software development
emma(r)