I created a table to hold some forum messages :
table messages (
id serial primary key,
parent_folder integer not null
references folders(id), --in another table
-- for replies, this stores the message which we reply to
reply_to_id integer null
references messages( id ),
thread_depth integer not null default 0
other fields :
message_title, message_text, author, etc.
);
I want to know which depth the messages in a thread are, simply to shift
them a little more to the right in the display, and I want to store this
in the "thread_depth" field so I don't have to compute it at each SELECT I
do.
My first thought was to use an "ON INSERT" rule which would change the
"thread_depth" of messages inserted with a not-null "reply_to_id" by
inserting an extra UPDATE after the INSERT. Problem was, the "NEW.id"
which got passed to me by postgresql contained something like :
(nextval('messages_id_seq')::text)::integer
Aside from the funky double cast, trying to read the value incremented
the sequence and the UPDATE failed because it tried to update a
non-existent row.
So, NEW contained unevaluated default values, BUT reading it evaluates
the values (of course) so it consumes an extra sequence number...
Is this normal ?
In the end I used a trigger which looks cleaner :
create or replace function vf.messages_set_depth()
returns trigger
as 'begin
if new.reply_to_id is not null then
new.thread_depth=1+(select thread_depth from messages where
id=new.reply_to_id limit 1);
end if;
return new;
end;'
language plpgsql;
create trigger messages_set_depth_trigger
before insert or update on messages
for each row execute procedure messages_set_depth();
And it works well this way. I don't intend to do mass message insertion
so the fact that the trigger is called on every insert does not disturb
me. However, the strange behaviour of the rule still bothers me.
Any comments ?
