我正在尝试建立一个排序的博客系统,我遇到了一个小问题.
简单地说,我的文章表中有3列：
id SERIAL,category VARCHAR FK,category_id INT
id列显然是PK,它被用作所有文章的全局标识符.
类别栏很好..类别.
category_id用作类别中的唯一ID,因此当前存在UNIQUE(category,category_id)约束.
我想要它,以便我每次执行像这样的查询
INSERT INTO article(category) VALUES ('stackoverflow');
我希望根据’stackoverflow’类别的最新category_id自动填充category_id列.
在我的逻辑代码中实现这一点非常容易.我只选择最新的num并插入1,但这涉及两个单独的查询.
我正在寻找一个可以在一个查询中完成所有这些操作的sql解决方案.
解决方法
概念
通过覆盖INSERT语句中的输入值,为每行执行的触发器中的category_id列分配值.
行动
这是SQL Fiddle,看看代码在行动
对于一个简单的测试,我正在创建文章表保存类别及其ID应该对每个类别都是唯一的.我省略了约束创建 – 这与提出要点无关.
create table article ( id serial,category varchar,category_id int )
使用generate_series()函数为两个不同的类别插入一些值,以便自动增量.
insert into article(category,category_id) select 'stackoverflow',i from generate_series(1,1) i union all select 'stackexchange',3) i
创建一个触发器函数,它将选择MAX(category_id)并将其值增加1,用于我们正在插入行的类别,然后在将实际INSERT移动到表之前覆盖该值(BEFORE INSERT触发器处理那).
CREATE OR REPLACE FUNCTION category_increment() RETURNS trigger LANGUAGE plpgsql AS $$ DECLARE v_category_inc int := 0; BEGIN SELECT MAX(category_id) + 1 INTO v_category_inc FROM article WHERE category = NEW.category; IF v_category_inc is null THEN NEW.category_id := 1; ELSE NEW.category_id := v_category_inc; END IF; RETURN NEW; END; $$
使用该功能作为触发器.
CREATE TRIGGER trg_category_increment BEFORE INSERT ON article FOR EACH ROW EXECUTE PROCEDURE category_increment()
为现有类别和不存在的类别插入更多值(后触发设备).
INSERT INTO article(category) VALUES
('stackoverflow'),('stackexchange'),('nonexisting');
用于选择数据的查询：
select category,category_id From article order by 1,2
初始插入的结果：
category category_id stackexchange 1 stackexchange 2 stackexchange 3 stackoverflow 1
最终插入后的结果：
category category_id nonexisting 1 stackexchange 1 stackexchange 2 stackexchange 3 stackexchange 4 stackoverflow 1 stackoverflow 2
