使用规则插入辅助表自动增量序列 [英] 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);

如果 user.userid 是整数，则此方法很好。但是，如果它是一个序列（例如，键入 serial 或 bigserial ），则在表 lastlogin 中插入的是下一个序列ID。因此，此命令：

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');

将列[1，'john'，...]插入 user ，但将[2，...]列添加到 lastlogin 中。以下两种变通方法可以正常工作，除了第二种变通方法消耗的序列是两倍，因为该序列仍是自动递增的：

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');

第一个解决方法将使用相同的ID，第二个解决方法则是搞砸了。

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

是否可以通过postgresql规则执行此操作，还是我应该自己直接在 lastlogin 中进行第二次插入还是使用行触发器？实际上，我认为当我访问 NEW.userid 时，行触发器也会自动增加序列。

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.

推荐答案

完全忘记规则。它们不好。

触发器对您来说更好。而且在99％的情况下，有人认为自己需要规则。试试这个：

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();

然后：

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)

这篇关于使用规则插入辅助表自动增量序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！