Thread: Problem with function and trigger...

I have a function declared as such:

CREATE OR REPLACE FUNCTION thread_sync() RETURNS trigger AS $$
BEGIN IF TG_OP = 'DELETE' AND OLD.deleted = FALSE 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' AND NEW.deleted = TRUE THEN   UPDATE member SET
total_threads=total_threads-1WHERE id=NEW.member_id;   RETURN NEW; ELSEIF TG_OP = 'UPDATE' AND NEW.deleted = FALSE THEN
 UPDATE member SET total_threads=total_threads+1 WHERE id=NEW.member_id;   RETURN NEW; END IF; RETURN NULL; 
END;
$$ LANGUAGE plpgsql;

And the trigger for it:

CREATE TRIGGER thread_sync AFTER INSERT OR DELETE OR UPDATE ON thread
FOR EACH ROW EXECUTE PROCEDURE thread_sync();

creating the function works fine, as well as creating the trigger, but
when I go to insert a row, I get the following message:

bcodev=> insert into thread
(member_id,subject,category_id,last_member_id) values (1,'hi there
this is a test',1,1);
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?

Ian Meyer <ianmmeyer@gmail.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

On 9/28/05, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Ian Meyer <ianmmeyer@gmail.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;
ELSEIFTG_OP = 'INSERT' THEN   UPDATE member SET total_threads=total_threads+1 WHERE id=NEW.member_id;   RETURN NEW;
ELSEIFTG_OP = 'UPDATE' THEN   IF NEW.deleted != OLD.deleted THEN     IF NEW.deleted = TRUE THEN       UPDATE member SET
total_threads=total_threads-1WHERE id=NEW.member_id;       RETURN NEW;     ELSEIF NEW.deleted = FALSE then       UPDATE
memberSET 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