基于另一列的PostgreSQL序列 [英] PostgreSQL sequence based on another column
问题描述
让我们说我有一张桌子:
Lets say I have a table as such:
Column | Type | Notes
---------+------------ +----------------------------------------------------------
id | integer | An ID that's FK to some other table
seq | integer | Each ID gets its own seq number
data | text | Just some text, totally irrelevant.
id
+ seq
是组合键.
我想看的是:
ID | SEQ | DATA
----+------ +----------------------------------------------
1 | 1 | Quick brown fox, lorem ipsum, lazy dog, etc etc.
1 | 2 | Quick brown fox, lorem ipsum, lazy dog, etc etc.
1 | 3 | Quick brown fox, lorem ipsum, lazy dog, etc etc.
1 | 4 | Quick brown fox, lorem ipsum, lazy dog, etc etc.
2 | 1 | Quick brown fox, lorem ipsum, lazy dog, etc etc.
3 | 1 | Quick brown fox, lorem ipsum, lazy dog, etc etc.
3 | 2 | Quick brown fox, lorem ipsum, lazy dog, etc etc.
3 | 3 | Quick brown fox, lorem ipsum, lazy dog, etc etc.
3 | 4 | Quick brown fox, lorem ipsum, lazy dog, etc etc.
如您所见,id
和seq
的组合是唯一的.
As you can see, a combination of id
and seq
is unique.
我不确定如何设置表(或插入语句?)来执行此操作.我想插入id
和data
,导致seq
是依赖于id
的子序列.
I'm not sure how to set up my table (or insert statement?) to do this. I'd like to insert id
and data
, resulting in seq
being a sub-sequence dependent on id
.
推荐答案
没问题!我们将创建两个表things
和stuff
. stuff
将是您在问题中描述的表格,而things
是它所指的表格:
No problem! We're going to make two tables, things
and stuff
. stuff
will be the table you describe in your question, and things
is the one it refers to:
CREATE TABLE things (
id serial primary key,
name text
);
CREATE TABLE stuff (
id integer references things,
seq integer NOT NULL,
notes text,
primary key (id, seq)
);
然后我们将设置things
的触发器,该触发器将在每次创建一行时创建一个新序列:
Then we'll set things
up with a trigger that will create a new sequence every time a row is created:
CREATE FUNCTION make_thing_seq() RETURNS trigger
LANGUAGE plpgsql
AS $$
begin
execute format('create sequence thing_seq_%s', NEW.id);
return NEW;
end
$$;
CREATE TRIGGER make_thing_seq AFTER INSERT ON things FOR EACH ROW EXECUTE PROCEDURE make_thing_seq();
现在我们将得到thing_seq_1
,thing_seq_2
等,等等...
Now we'll end up with thing_seq_1
, thing_seq_2
, etc, etc...
在stuff
上添加另一个触发器,以便每次使用正确的顺序:
Now another trigger on stuff
so that it uses the right sequence each time:
CREATE FUNCTION fill_in_stuff_seq() RETURNS trigger
LANGUAGE plpgsql
AS $$
begin
NEW.seq := nextval('thing_seq_' || NEW.id);
RETURN NEW;
end
$$;
CREATE TRIGGER fill_in_stuff_seq BEFORE INSERT ON stuff FOR EACH ROW EXECUTE PROCEDURE fill_in_stuff_seq();
这将确保当行进入stuff
时,使用id
列查找正确的序列以调用nextval
.
That'll ensure that when rows go into stuff
, the id
column is used to find the right sequence to call nextval
on.
这是一个示范:
test=# insert into things (name) values ('Joe');
INSERT 0 1
test=# insert into things (name) values ('Bob');
INSERT 0 1
test=# select * from things;
id | name
----+------
1 | Joe
2 | Bob
(2 rows)
test=# \d
List of relations
Schema | Name | Type | Owner
--------+---------------+----------+----------
public | stuff | table | jkominek
public | thing_seq_1 | sequence | jkominek
public | thing_seq_2 | sequence | jkominek
public | things | table | jkominek
public | things_id_seq | sequence | jkominek
(5 rows)
test=# insert into stuff (id, notes) values (1, 'Keychain');
INSERT 0 1
test=# insert into stuff (id, notes) values (1, 'Pet goat');
INSERT 0 1
test=# insert into stuff (id, notes) values (2, 'Family photo');
INSERT 0 1
test=# insert into stuff (id, notes) values (1, 'Redundant lawnmower');
INSERT 0 1
test=# select * from stuff;
id | seq | notes
----+-----+---------------------
1 | 1 | Keychain
1 | 2 | Pet goat
2 | 1 | Family photo
1 | 3 | Redundant lawnmower
(4 rows)
test=#
这篇关于基于另一列的PostgreSQL序列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!