X-Original-To: pgsql-sql-postgresql.org@postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id CA5679FB1C1
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed,  5 Jul 2006 15:11:06 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 83134-04 for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed,  5 Jul 2006 15:10:54 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from myemma.com (db1.e2ma.net [66.179.147.163])
	by postgresql.org (Postfix) with ESMTP id DAF109FA4A8
	for <pgsql-sql@postgresql.org>; Wed,  5 Jul 2006 15:10:53 -0300 (ADT)
Received: from localhost (localhost.localdomain [127.0.0.1])
	by myemma.com (Postfix) with ESMTP id 56A5023CCD;
	Wed,  5 Jul 2006 13:52:23 -0500 (CDT)
Received: from myemma.com ([127.0.0.1])
	by localhost (velma [127.0.0.1]) (amavisd-new, port 10024) with ESMTP
	id 13818-06; Wed, 5 Jul 2006 13:52:23 -0500 (CDT)
Received: from [192.168.1.68] (65.107.156.170.ptr.us.xo.net [65.107.156.170])
	by myemma.com (Postfix) with ESMTP id 0934023CCA;
	Wed,  5 Jul 2006 13:52:22 -0500 (CDT)
Message-ID: <44AC00AD.6080603@myemma.com>
Date: Wed, 05 Jul 2006 13:10:53 -0500
From: Erik Jones <erik@myemma.com>
User-Agent: Thunderbird 1.5.0.4 (Windows/20060516)
MIME-Version: 1.0
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
References: <44ABEE3A.1030609@myemma.com>	
	<bf05e51c0607051036j1a548b1as604529d41dc7aa6a@mail.gmail.com>	
	<44ABFA22.9060006@myemma.com>
	<bf05e51c0607051056w493c6394r7f3601ab2107d9e@mail.gmail.com>
In-Reply-To: <bf05e51c0607051056w493c6394r7f3601ab2107d9e@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new-20030616-p10 (Debian) at myemma.com
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200607/49
X-Sequence-Number: 25623

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)