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 8D4EA9FA61D for ; Wed, 5 Jul 2006 14:56:38 -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 37417-08 for ; Wed, 5 Jul 2006 14:56:34 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.186]) by postgresql.org (Postfix) with ESMTP id 2AFD29FA4A8 for ; Wed, 5 Jul 2006 14:56:33 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id i2so1256516nfe for ; Wed, 05 Jul 2006 10:56:32 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=tcWrbUGSODusOS79Mem5ZFrvG1fmymqKqlSM55KQzHzoBFqiUq4hKer6xjPMC3p7CdyXqTwOVjzIvcWED8oxk/HAS/fPQ/iiw4dZjcZ6XWDn7CILxYeNEKU9hnWiCIbJflAcPbD9xyuXvo60x00Odq3rR1wXcRW2TEQg+mX/QeQ= Received: by 10.78.140.17 with SMTP id n17mr3912734hud; Wed, 05 Jul 2006 10:56:31 -0700 (PDT) Received: by 10.78.29.2 with HTTP; Wed, 5 Jul 2006 10:56:31 -0700 (PDT) Message-ID: Date: Wed, 5 Jul 2006 12:56:31 -0500 From: "Aaron Bono" To: "Erik Jones" Subject: Re: Problem with array subscripts in plpgsql trigger function Cc: "Postgres SQL language list" In-Reply-To: <44ABFA22.9060006@myemma.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_76805_21311061.1152122191375" References: <44ABEE3A.1030609@myemma.com> <44ABFA22.9060006@myemma.com> X-Google-Sender-Auth: 4bf16058f74a8485 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/47 X-Sequence-Number: 25621 ------=_Part_76805_21311061.1152122191375 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 7/5/06, Erik Jones wrote: > > Aaron Bono wrote: > > On 7/5/06, *Erik Jones* > > 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 ------=_Part_76805_21311061.1152122191375 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On 7/5/06, Erik Jones <erik@myemma.com> wrote:

Aaron Bono wrote:
> On 7/5/06, *Erik Jones* <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

------=_Part_76805_21311061.1152122191375--