pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedProblem with function and trigger...
3+ messages / 2 participants
[nested] [flat]
* Problem with function and trigger...
@ 2005-09-28 17:35 Ian Meyer <ianmmeyer@gmail.com>
2005-09-28 17:52 ` Re: Problem with function and trigger... Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Ian Meyer @ 2005-09-28 17:35 UTC (permalink / raw)
To: pgsql-sql
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-1 WHERE 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?
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Problem with function and trigger...
2005-09-28 17:35 Problem with function and trigger... Ian Meyer <ianmmeyer@gmail.com>
@ 2005-09-28 17:52 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-09-28 19:46 ` Re: Problem with function and trigger... Ian Meyer <ianmmeyer@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2005-09-28 17:52 UTC (permalink / raw)
To: Ian Meyer <ianmmeyer@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Problem with function and trigger...
2005-09-28 17:35 Problem with function and trigger... Ian Meyer <ianmmeyer@gmail.com>
2005-09-28 17:52 ` Re: Problem with function and trigger... Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-09-28 19:46 ` Ian Meyer <ianmmeyer@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Ian Meyer @ 2005-09-28 19:46 UTC (permalink / raw)
To: pgsql-sql
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;
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-09-28 19:46 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-09-28 17:35 Problem with function and trigger... Ian Meyer <ianmmeyer@gmail.com>
2005-09-28 17:52 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-09-28 19:46 ` Ian Meyer <ianmmeyer@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox