DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=F1eYL2mwEjuz7RVhC3g/6ow6p1fSSHv9xf2ZWyEOejaucl4lQrQ5NdAR9emV+9JsI++w1nlvgkAgQsfzH+Rs2PRlbIrcOzt5OWdq1lsOBD8GbqeFbPqbbiFgdyHpYL22F8Y9WdIf8fvC2m6Z31R0yfNZxJFnxfKDT0iibKFrWXU=
Message-ID: <b54105080509281246297684b5@mail.gmail.com>
Date: Wed, 28 Sep 2005 15:46:07 -0400
From: Ian Meyer <ianmmeyer@gmail.com>
Reply-To: Ian Meyer <ianmmeyer@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: Problem with function and trigger...
In-Reply-To: <7747.1127929968@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: <b541050805092810357cac86c0@mail.gmail.com>
	<7747.1127929968@sss.pgh.pa.us>

On 9/28/05, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Ian Meyer <ianmmeyer@gmail.com> writes:
> >   IF TG_OP =3D 'DELETE' AND OLD.deleted =3D FALSE THEN
>
> > ERROR:  record "old" is not assigned yet
> > DETAIL:  The tuple structure of a not-yet-assigned record is indetermin=
ate.
> > CONTEXT:  PL/pgSQL function "thread_sync" line 2 at if
>
> > What am I failing to understand with this?
>
> We don't guarantee short-circuit evaluation of boolean expressions.
> You'll have to break that into two IFs, ie,
>
>         IF TG_OP =3D 'DELETE' THEN
>                 IF ... test on OLD.something ...
>
>                         regards, tom lane
>

Ah ha.. that makes sense. I fixed it to be:

CREATE OR REPLACE FUNCTION thread_sync() RETURNS trigger AS $$
BEGIN
  IF TG_OP =3D 'DELETE' THEN
    UPDATE member SET total_threads=3Dtotal_threads-1 WHERE id=3DOLD.member=
_id;
    RETURN OLD;
  ELSEIF TG_OP =3D 'INSERT' THEN
    UPDATE member SET total_threads=3Dtotal_threads+1 WHERE id=3DNEW.member=
_id;
    RETURN NEW;
  ELSEIF TG_OP =3D 'UPDATE' THEN
    IF NEW.deleted !=3D OLD.deleted THEN
      IF NEW.deleted =3D TRUE THEN
        UPDATE member SET total_threads=3Dtotal_threads-1 WHERE id=3DNEW.me=
mber_id;
        RETURN NEW;
      ELSEIF NEW.deleted =3D FALSE then
        UPDATE member SET total_threads=3Dtotal_threads+1 WHERE id=3DNEW.me=
mber_id;
        RETURN NEW;
      END IF;
      RETURN NULL;
    END IF;
    RETURN NULL;
  END IF;
END;
$$ LANGUAGE plpgsql;

Here's my next question(s)... If i have 2 rows in the thread table and
I delete 1 of the rows with: delete from thread where id=3D26; it ends
up decrementing the total_threads value by 2, instead of one.

bcodev=3D> select id, total_threads from member;
 id | total_threads
----+---------------
  1 |             2
(1 row)

bcodev=3D> delete from thread where id=3D37;
DELETE 1
bcodev=3D> select id, total_threads from member;
 id | total_threads
----+---------------
  1 |             0
(1 row)

I'm confused, to say the least.

Thanks for all the help.

Ian