Table Rule
От | Rudi Starcevic |
---|---|
Тема | Table Rule |
Дата | |
Msg-id | 3DA37648.5080702@oasis.net.au обсуждение исходный текст |
Список | 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 по дате отправления: