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 29AB89FA61D for ; Wed, 5 Jul 2006 14:36:23 -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 29996-10 for ; Wed, 5 Jul 2006 14:36:19 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.190]) by postgresql.org (Postfix) with ESMTP id 275DC9FA4A8 for ; Wed, 5 Jul 2006 14:36:17 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id h2so47953nfe for ; Wed, 05 Jul 2006 10:36:16 -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=E+J/aPX4rccfCEP4ld3LAk3DGmVt4No3tYL1QSBNOxPaktGqNsxiWQF+RJE4LV0a0ckuCGYi0eNSlyS3kkUAqH4D5JJUeiw9vary72S4bLr2dvq/NVGJdczc6iiSQF6HqxWnaJMWBuUSOb5Oy6EBW2EYwbDFWUrhiiLUzmOt5kE= Received: by 10.78.117.10 with SMTP id p10mr2484526huc; Wed, 05 Jul 2006 10:36:16 -0700 (PDT) Received: by 10.78.29.2 with HTTP; Wed, 5 Jul 2006 10:36:16 -0700 (PDT) Message-ID: Date: Wed, 5 Jul 2006 12:36:16 -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: <44ABEE3A.1030609@myemma.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_76405_6899266.1152120976426" References: <44ABEE3A.1030609@myemma.com> X-Google-Sender-Auth: d531d25c42f62737 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/45 X-Sequence-Number: 25619 ------=_Part_76405_6899266.1152120976426 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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? ------=_Part_76405_6899266.1152120976426 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:
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? 

------=_Part_76405_6899266.1152120976426--