From: Erik Jones <erik@myemma.com>
To: Aaron Bono <postgresql@aranya.com>
Cc: Postgres SQL language list <pgsql-sql@postgresql.org>
Subject: Re: Problem with array subscripts in plpgsql trigger function
Date: Wed, 05 Jul 2006 13:10:53 -0500
Message-ID: <44AC00AD.6080603@myemma.com> (raw)
In-Reply-To: <bf05e51c0607051056w493c6394r7f3601ab2107d9e@mail.gmail.com>
References: <44ABEE3A.1030609@myemma.com>
<bf05e51c0607051036j1a548b1as604529d41dc7aa6a@mail.gmail.com>
<44ABFA22.9060006@myemma.com>
<bf05e51c0607051056w493c6394r7f3601ab2107d9e@mail.gmail.com>
Aaron Bono wrote:
> On 7/5/06, *Erik Jones* <erik@myemma.com <mailto:erik@myemma.com>> wrote:
>
> Aaron Bono wrote:
> > On 7/5/06, *Erik Jones* <erik@myemma.com
> <mailto:erik@myemma.com> <mailto:erik@myemma.com
> <mailto:erik@myemma.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@myemma.com>
software development
emma(r)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: erik@myemma.com, postgresql@aranya.com
Subject: Re: Problem with array subscripts in plpgsql trigger function
In-Reply-To: <44AC00AD.6080603@myemma.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox