每组值定制SERIAL /自动增量 [英] Custom SERIAL / autoincrement per group of values

问题描述

简单来说，我的文章表：

  id SERIAL，
 category VARCHAR FK，
 category_id INT 
  

id 列显然是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 INT

id 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.

Action

Here'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) i

Creating 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,2

Result for initial inserts:

category    category_id
stackexchange   1
stackexchange   2
stackexchange   3
stackoverflow   1

Result after final inserts:

category    category_id
nonexisting     1
stackexchange   1
stackexchange   2
stackexchange   3
stackexchange   4
stackoverflow   1
stackoverflow   2

这篇关于每组值定制SERIAL /自动增量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！