简单来说,我的文章表:
id SERIAL, category VARCHAR FK, category_id INTid 列显然是PK它被用作所有文章的全局标识符。
类别列是好的..类别。 p>
category_id 用作类别中的 UNIQUE ID目前有一个 UNIQUE(category,category_id)约束。
然而,我也想要code> category_id to auto-increment 。
我想要这样,每次执行查询像
INSERT INTO文章(category)VALUES('stackoverflow');我希望 category_id 列自动根据stackoverflow类别的最新 category_id 填写。
在我的逻辑代码中实现这一点简单。我只是选择最新的num并插入+1,但涉及两个单独的查询。 我正在寻找可以在一个查询中执行所有这一切的SQL解决方案。
解决方案概念
至少有几种方法可以解决这个问题。首先,我想到:
在为每行执行的触发器中为 category_id 列分配一个值,
动作 通过覆盖 INSERT 语句的输入值这是 SQL小提琴 查看代码在行动
对于一个简单的测试,我正在创建文章表格持有类别及其 id ,它们对于每个类别应该是唯一的。我已经省略了约束创建 - 这与提出的点无关。
创建表文章(id serial,category varchar,category_id int )使用 generate_series()函数具有自动增量已经存在。
插入文章(category,category_id) select'stackoverflow',我来自generate_series(1,1)i union all select'stackexchange',我来自generate_series(1,3)i创建一个触发器函数,它将选择 MAX(category_id)并将其值增加 1 对于类别我们正在插入一行,然后覆盖该值,然后再继续使用实际的 INSERT 到表( BEFORE INSERT 触发器处理)。
创建或替换功能category_increment() RETURNS触发器 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; 如果v_category_inc为空THEN NEW.category_id = 1; ELSE NEW.category_id:= v_category_inc; END IF; 返回新; END; $$使用该功能作为触发器。
CREATE TRIGGER trg_category_increment BEFORE INSERT ON文章为每个ROW执行程序category_increment()为现有的类别和不存在的类别插入一些值(后触发器)。
INSERT INTO文章(category)VALUES ('stackoverflow'),('stackexchange') ,('nonexisting');查询用于选择数据:
选择类别,category_id从文章顺序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
I'm trying to make a blog system of sort and I ran into a slight problem.
Simply put, there's 3 columns in my article table:
id SERIAL, category VARCHAR FK, category_id INTid column is obviously the PK and it is used as a global identifier for all articles.
category column is well .. category.
category_id is used as a UNIQUE ID within a category so currently there is a UNIQUE(category, category_id) constraint in place.
However, I also want for category_id to auto-increment.
I want it so that every time I execute a query like
INSERT INTO article(category) VALUES ('stackoverflow');I want the category_id column to be automatically be filled according to the latest category_id of the 'stackoverflow' category.
Achieving this in my logic code is quite easy. I just select latest num and insert +1 of that but that involves two separate queries. I am looking for a SQL solution that can do all this in one query.
解决方案Concept
There are at least several ways to approach this. First one that comes to my mind:
Assign a value for category_id column inside a trigger executed for each row, by overwriting the input value from INSERT statement.
ActionHere's the SQL Fiddle to see the code in action
For a simple test, I'm creating article table holding categories and their id's that should be unique for each category. I have omitted constraint creation - that's not relevant to present the point.
create table article ( id serial, category varchar, category_id int )Inserting some values for two distinct categories using generate_series() function to have an auto-increment already in place.
insert into article(category, category_id) select 'stackoverflow', i from generate_series(1,1) i union all select 'stackexchange', i from generate_series(1,3) iCreating a trigger function, that would select MAX(category_id) and increment its value by 1 for a category we're inserting a row with and then overwrite the value right before moving on with the actual INSERT to table (BEFORE INSERT trigger takes care of that).
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; $$Using the function as a trigger.
CREATE TRIGGER trg_category_increment BEFORE INSERT ON article FOR EACH ROW EXECUTE PROCEDURE category_increment()Inserting some more values (post trigger appliance) for already existing categories and non-existing ones.
INSERT INTO article(category) VALUES ('stackoverflow'), ('stackexchange'), ('nonexisting');Query used to select data:
select category, category_id From article order by 1,2Result for initial inserts:
category category_id stackexchange 1 stackexchange 2 stackexchange 3 stackoverflow 1Result after final inserts:
category category_id nonexisting 1 stackexchange 1 stackexchange 2 stackexchange 3 stackexchange 4 stackoverflow 1 stackoverflow 2
更多推荐
每组值定制SERIAL /自动增量
发布评论