Skip site navigation (1) Skip section navigation (2)

Sequences & rules

From: Pierre-Frédéric Caillaud <lists(at)boutiquenumerique(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Sequences & rules
Date: 2004-07-27 06:52:20
Message-ID: opsbsapimwcq72hf@musicbox (view raw or flat)
Thread:
Lists: pgsql-general
	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 ?














	

In response to

Responses

pgsql-general by date

Next:From: rubenDate: 2004-07-27 07:08:04
Subject: open of /usr/lib/pgsql/data/pg_clog/0F3E failed
Previous:From: Pierre-Frédéric CaillaudDate: 2004-07-27 06:37:23
Subject: Re: Sql injection attacks

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group