PostgreSQL 关于实现每次插入新值时重置序列的计数器

最新推荐文章于 2024-09-15 08:03:51 发布

ChauncyS

最新推荐文章于 2024-09-15 08:03:51 发布

阅读量142

点赞数 1

分类专栏： PostgreSQL 文章标签： postgresql 数据库

本文链接：https://blog.csdn.net/ChauncyS/article/details/134718607

版权

PostgreSQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

背景：

需要实现每次上传的数据，用上传日期和序号来规定主键。即序列+日期格式。

因业务需要新的字段需要用自增主键和日期进行拼接，因此需要在ODS层的数据表中加入一个自增列，并与某个日期字段进行合并，然后赋值作为新的id主键。

PostgreSQL直接使用触发器实现，每次上传数据量在300条左右。‘

最终代码：

再解释(手写过来的可能有点问题自己更正一下即可)

CREATE OR REPLACE FUNCTION xxx.table_name()
    RETURNS trigger
    LANGUAGE plpgsql
AS $function$
    declare old_tag numeric := 0;
BEGIN
    select count(coalesce(日期字段,0)) into old_tag 
    from table_name
    where 日期字段 = NEW.日期字段 limit 1;
    
    if old_tag = 0 then
        ALTER SEQUENCE table_name对应的序列 RESTART 1;
    end if;
    RETURN NULL;
END;
$function$
;


CREATE TRIGGER 触发器名字 before insert on table_name 
FOR EACH ROW
EXECUTE procedure xxx.table_name()；

解释

PostgreSQL的触发器格式：

CREATE TRIGGER
CREATE TRIGGER — 定义一个新触发器
大纲
CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event
 [ OR ... ] }
 ON table_name
 [ FROM referenced_table_name ]
 [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY
 DEFERRED ] ]
 [ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ ... ] ]
 [ FOR [ EACH ] { ROW | STATEMENT } ]
 [ WHEN ( condition ) ]
 EXECUTE PROCEDURE function_name ( arguments )
这里的event可以是下列之一：
 INSERT
 UPDATE [ OF column_name [, ... ] ]
 DELETE
 TRUNCATE

描述

CREATE TRIGGER创建一个新触发器。该触发器将被关联到指定的表、视图或者外部表并且在某些操作在该表上执行时将执行指定的函数function_name。

该触发器可以被指定为在一行上尝试该操作之前触发（在约束被检查并且INSERT、UPDATE或者DELETE被尝试之前）；也可以在该操作完成之后触发（在约束被检查并且INSERT、UPDATE或者DELETE完成之后）；或者取代该操作（在对一个视图插入、更新或删除的情况中）。如果该触发器在事件之前触发或者取代事件，该触发器可以跳过对当前行的操作或者改变正在被插入的行（只对INSERT以及UPDATE操作）。如果该触发器在事件之后触发，所有更改（包括其他触发器的效果）对该触发器“可见”。

一个被标记为FOR EACH ROW的触发器会对该操作修改的每一行都调用一次。例如，一个影响 10 行的DELETE将导致在目标关系上的任何ON DELETE触发器被独立调用 10 次，也就是为每一个被删除的行调用一次。

与此相反，一个被标记为FOR EACH STATEMENT的触发器只会为任何给定的操作执行一次，不管该操作修改多少行（特别地，一个修改零行的操作将仍会导致任何可用的FOR EACH STATEMENT触发器被执行）。

被指定为要触发INSTEAD OF触发器事件的触发器必须被标记为FOR EACH ROW，并且只能被定义在视图上。一个视图上的BEFORE和AFTER触发器必须被标记为FOR EACH STATEMENT。此外，触发器可以被定义成为TRUNCATE触发，但只能是FOR EACH STATEMENT。下面的表格总结了哪些触发器类型可以被用在表、视图和外部表上：

何时	事件	行级	语句级
BEFORE	NSERT/UPDATE/ DELETE	表和外部表	表、视图和外部表
BEFORE	TRUNCATE	—	表
AFTER	INSERT/UPDATE/ DELETE	表和外部表	表、视图和外部表
AFTER	TRUNCATE	—	表
INSTEAD OF	INSERT/UPDATE/ DELETE	视图	—
INSTEAD OF	TRUNCATE	—	—

还有，一个触发器定义可以指定一个布尔的WHEN条件，它将被测试来看看该触发器是否应该被触发。在行级触发器中，WHEN条件可以检查该行的列的新旧值。语句级触发器也可以有WHEN条件，尽管该特性对于它们不是很有用（因为条件不能引用表中的任何值）。

如果有多个同种触发器被定义为相同事件触发，它们将按照名称的字母表顺序被触发。

当CONSTRAINT选项被指定，这个命令会创建一个约束触发器。这和一个常规触发器相同，不过触发该触发器的时机可以使用SET CONSTRAINTS调整。约束触发器必须是普通表（不是外部表）上的AFTER ROW触发器。它们可以在导致触发器事件的语句末尾被引发或者在包含该语句的事务末尾被引发。在后一种情况中，它们被称作是被延迟。一个待处理的延迟触发器的引发也可以使用SET CONSTRAINTS立即强制发生。当约束触发器实现的约束被违背时，约束触发器应该抛出一个异常。

REFERENCING选项可以收集转换关系，它们是包含由当前SQL语句插入、删除或修改的所有行的行集。此功能可让触发器查看语句所做的操作的全局视图，而不是一次一行。该选项仅适用于不是约束触发器的AFTER触发器；另外，如果触发器是一个UPDATE触发器，它不能指定一个 column_name列表。 OLD TABLE只能指定一次，并且只能用于触发UPDATE 或DELETE的触发器；它会创建一个包含由语句更新或删除的所有行的 before-images的转换关系。同样，NEW TABLE只能指定一次，并且只能针对可以在UPDATE或INSERT上触发的触发器；它会创建一个包含由语句更新或插入的所有行的after-images的转换关系。

SELECT不修改任何行，因此你无法创建SELECT触发器。规则和视图可以为似乎需要SELECT触发器的问题提供可行的解决方案。

参数

name

给新触发器的名称。这必须与同一个表上的任何其他触发器相区别。名称不能是模式限定的 — 该触发器会继承它所在表的模式。对于一个约束触发器，这也是使用SET CONSTRAINTS修改触发器行为时要用到的名字。 BEFORE AFTER INSTEAD OF 决定该函数是要在事件之前、之后被调用还是会取代该事件。一个约束触发器也能被指定为AFTER。