当前位置: 动力学知识库 > 问答 > 编程问答 >

postgresql - Using Rule to Insert Into Secondary Table Auto-Increments Sequence

问题描述:

To automatically add a column in a second table to tie it to the first table via a unique index, I have a rule such as follows:

CREATE OR REPLACE RULE auto_insert AS ON INSERT TO user DO ALSO

INSERT INTO lastlogin (id) VALUES (NEW.userid);

This works fine if user.userid is an integer. However, if it is a sequence (e.g., type serial or bigserial), what is inserted into table lastlogin is the next sequence id. So this command:

INSERT INTO user (username) VALUES ('john');

would insert column [1, 'john', ...] into user but column [2, ...] into lastlogin. The following 2 workarounds do work except that the second one consumes twice as many serials since the sequence is still auto-incrementing:

CREATE OR REPLACE RULE auto_insert AS ON INSERT TO user DO ALSO

INSERT INTO lastlogin (id) VALUES (lastval());

CREATE OR REPLACE RULE auto_insert AS ON INSERT TO user DO ALSO

INSERT INTO lastlogin (id) VALUES (NEW.userid-1);

Unfortunately, the workarounds do not work if I'm inserting multiple rows:

INSERT INTO user (username) VALUES ('john'), ('mary');

The first workaround would use the same id, and the second workaround is all kind of screw-up.

Is it possible to do this via postgresql rules or should I simply do the 2nd insertion into lastlogin myself or use a row trigger? Actually, I think the row trigger would also auto-increment the sequence when I access NEW.userid.

网友答案:

Forget rules altogether. They're bad.

Triggers are way better for you. And in 99% of cases when someone thinks he needs a rule. Try this:

create table users (
  userid serial primary key,
  username text
);

create table lastlogin (
  userid int primary key references users(userid),
  lastlogin_time timestamp with time zone
);

create or replace function lastlogin_create_id() returns trigger as $$
  begin
    insert into lastlogin (userid) values (NEW.userid);
    return NEW;
  end;
$$
language plpgsql volatile;

create trigger lastlogin_create_id
  after insert on users for each row execute procedure lastlogin_create_id();

Then:

insert into users (username) values ('foo'),('bar');

select * from users;
 userid | username 
--------+----------
      1 | foo
      2 | bar
(2 rows)
select * from lastlogin;
 userid | lastlogin_time 
--------+----------------
      1 | 
      2 | 
(2 rows)
分享给朋友:
您可能感兴趣的文章:
随机阅读: