问题
要实现的功能:
根据插入数据的ptype,如果是1就按从10001开始递增,如果是0就从20001开始递增。
问题解决:
1.先分别创建两个序列:
CREATE SEQUENCE sq_sbc_id
START WITH 10001
INCREMENT BY 1
MAXVALUE 20000
CACHE 20;
CREATE SEQUENCE sq_sys_id
START WITH 20001
INCREMENT BY 1
MAXVALUE 30000
CACHE 20;
2.创建触发器函数
CREATE OR REPLACE FUNCTION public.tri_places_insert()
RETURNS trigger
LANGUAGE plpgsql
NOT FENCED NOT SHIPPABLE
AS $$ DECLARE
BEGIN
IF NEW.ptype = 0 THEN
NEW.place_id = nextval('sq_sys_id');
END IF;
IF NEW.ptype = 1 THEN
NEW.place_id = nextval('sq_sbc_id');
END IF;
RETURN NEW;
END;
$$
3.创建触发器
CREATE TRIGGER before_places_insert
BEFORE INSERT ON places
FOR EACH ROW
EXECUTE PROCEDURE tri_places_insert() ;
然后执行语句
insert into places (ptype,name,place,phone) VALUES
(1,'设备处三','实验四楼101','101003');
insert into places (ptype,name,place,phone) VALUES
(0,'生物实验室一','实验三楼312','401003');
就会分别在各自的序列上递增了。