From: | Ian Meyer <ianmmeyer(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Problem with function and trigger... |
Date: | 2005-09-28 19:46:07 |
Message-ID: | b54105080509281246297684b5@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On 9/28/05, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Ian Meyer <ianmmeyer(at)gmail(dot)com> writes:
> > IF TG_OP = 'DELETE' AND OLD.deleted = FALSE THEN
>
> > ERROR: record "old" is not assigned yet
> > DETAIL: The tuple structure of a not-yet-assigned record is indeterminate.
> > 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 = '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 = 'DELETE' THEN
UPDATE member SET total_threads=total_threads-1 WHERE id=OLD.member_id;
RETURN OLD;
ELSEIF TG_OP = 'INSERT' THEN
UPDATE member SET total_threads=total_threads+1 WHERE id=NEW.member_id;
RETURN NEW;
ELSEIF TG_OP = 'UPDATE' THEN
IF NEW.deleted != OLD.deleted THEN
IF NEW.deleted = TRUE THEN
UPDATE member SET total_threads=total_threads-1 WHERE id=NEW.member_id;
RETURN NEW;
ELSEIF NEW.deleted = FALSE then
UPDATE member SET total_threads=total_threads+1 WHERE id=NEW.member_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=26; it ends
up decrementing the total_threads value by 2, instead of one.
bcodev=> select id, total_threads from member;
id | total_threads
----+---------------
1 | 2
(1 row)
bcodev=> delete from thread where id=37;
DELETE 1
bcodev=> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Glaesemann | 2005-09-28 21:28:01 | Re: changing a column's position in table, how do you do that |
Previous Message | Daryl Richter | 2005-09-28 19:38:05 | Re: changing a column's position in table, how do you do that |