Re: Problem with array subscripts in plpgsql trigger function

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)