Table Rule - Mailing list pgsql-sql

From Rudi Starcevic
Subject Table Rule
Date
Msg-id 3DA37648.5080702@oasis.net.au
Whole thread Raw
List pgsql-sql
Hi,

I have a Postgresql Rule question I'd love some help with thanks.

I have a table, sysmessages, I'd like to keep a journal of.
So I create a rule that says on insert or update to this table do insert 
or update
into my sysmessges_log table.

My problem is this:
sysmessages table has it's own primary key.
When inserting into the sysmessages_log table the key inserted from the 
sysmessages
table is incremented. I don't want the incremented id but the same id in 
the sysmessages table.

Here's and example:

sysmessages row:
id    user_id    date               priority    message    status
1    93395    2002-10-10    3    test message    A

What's inserted in the the sysmessages table is
log_id    id    user_id    date               priority    message    status
1        2    93395    2002-10-10    3    test message    A

See how the id field is 2 in the sysmessages table not 1 like in 
sysmessages.
I want the id field to be 1 not 2.

I've include the sql below. It's all nice and tidy so if your keen you can
insert it and test out my rule.

Thanks very much for your time.

sql:

-- DROP RULE sysmessages_insert_rule;
-- DROP RULE sysmessages_update_rule;

-- DROP SEQUENCE sysmessages_id_seq;
-- DROP TABLE sysmessages;

-- DROP SEQUENCE sysmessages_log_log_id_seq;
-- DROP TABLE sysmessages_log;

create table sysmessages
(   id serial PRIMARY KEY,   user_id integer NOT NULL, -- ref. integrity removed for this example 
sql code.   message_date date DEFAULT now() NOT NULL,   message_priority char(1) CHECK( message_priority IN 
(1,2,3,4,5,6,7,8,9) ) NOT NULL,   message text NOT NULL,   status char(1) CHECK( status IN ('A','P','N') ) NOT NULL
);

create table sysmessages_log
(   log_id serial PRIMARY KEY,   id integer NOT NULL, -- no ref. integrity. we keep all records   user_id integer NOT
NULL,-- no need to use ref. integrity. allow 
 
ex-users to be in this table.   message_date date DEFAULT now() NOT NULL,   message_priority char(1) CHECK(
message_priorityIN 
 
(1,2,3,4,5,6,7,8,9) ) NOT NULL,   message text NOT NULL,   status char(1) CHECK( status IN ('A','P','N') ) NOT NULL
);

CREATE RULE        sysmessages_insert_rule AS
ON INSERT TO    sysmessages
DO INSERT INTO    sysmessages_log 
(id,user_id,message_date,message_priority,message,status) VALUES 
(new.id,new.user_id,new.message_date,new.message_priority,new.message,new.status);

CREATE RULE        sysmessages_update_rule AS
ON UPDATE TO    sysmessages
DO INSERT INTO    sysmessages_log 
(id,user_id,message_date,message_priority,message,status) VALUES 
(new.id,new.user_id,new.message_date,new.message_priority,new.message,new.status);









pgsql-sql by date:

Previous
From: Charles Hauser
Date:
Subject: Re: Problems Formulating a SELECT
Next
From: Keith Gray
Date:
Subject: Re: IN, EXISTS or ANY?