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 по дате отправления:

Предыдущее
От: Charles Hauser
Дата:
Сообщение: Re: Problems Formulating a SELECT
Следующее
От: Keith Gray
Дата:
Сообщение: Re: IN, EXISTS or ANY?