作者
digoal
日期
2019-08-17
标签
PostgreSQL , autoinc , 更新了多少次
背景
跟踪记录被更新了多少次？
plpgsql 触发器例子
```
create or replace function tg1() returns trigger as $$
declare
begin
NEW.updcnt := OLD.updcnt+1;
return new;
end;
$$ language plpgsql strict;
create table t (id int, updcnt int default 0);
CREATE TRIGGER tg1
BEFORE UPDATE ON t
FOR EACH ROW
EXECUTE PROCEDURE tg1();
postgres=# insert into t values (1);
INSERT 0 1
postgres=# select * from t;
id | updcnt
----+--------
1 | 0
(1 row)
postgres=# update t set id=2;
UPDATE 1
postgres=# select * from t;
id | updcnt
----+--------
2 | 1
(1 row)
postgres=# update t set id=3;
UPDATE 1
postgres=# select * from t;
id | updcnt
----+--------
3 | 2
(1 row)
```
C 触发器例子
实际上并不是设置这条记录被更新了多少次，而是将字段的值设置为指定序列的值（插入时，更新时）。
F.36.2. autoinc — Functions for Autoincrementing Fields
autoinc() is a trigger that stores the next value of a sequence into an integer field. This has some overlap with the built-in “serial column” feature, but it is not the same: autoinc() will override attempts to substitute a different field value during inserts, and optionally it can be used to increment the field during updates, too.
To use, create a BEFORE INSERT (or optionally BEFORE INSERT OR UPDATE) trigger using this function. Specify two trigger arguments: the name of the integer column to be modified, and the name of the sequence object that will supply values. (Actually, you can specify any number of pairs of such names, if you'd like to update more than one autoincrementing column.)
There is an example in autoinc.example.
```
create extension autoinc;
DROP SEQUENCE next_id;
DROP TABLE ids;
CREATE SEQUENCE next_id START -2 MINVALUE -2;
CREATE TABLE ids (
id int4,
idesc text
);
CREATE TRIGGER ids_nextid
BEFORE INSERT OR UPDATE ON ids
FOR EACH ROW
EXECUTE PROCEDURE autoinc (id, next_id);
INSERT INTO ids VALUES (0, 'first (-2 ?)');
INSERT INTO ids VALUES (null, 'second (-1 ?)');
INSERT INTO ids(idesc) VALUES ('third (1 ?!)');
SELECT * FROM ids;
id | idesc
----+---------------
-2 | first (-2 ?)
-1 | second (-1 ?)
1 | third (1 ?!)
(3 rows)
UPDATE ids SET id = null, idesc = 'first: -2 --> 2'
WHERE idesc = 'first (-2 ?)';
UPDATE ids SET id = 0, idesc = 'second: -1 --> 3'
WHERE id = -1;
UPDATE ids SET id = 4, idesc = 'third: 1 --> 4'
WHERE id = 1;
SELECT * FROM ids;
id | idesc
----+------------------
2 | first: -2 --> 2
3 | second: -1 --> 3
4 | third: 1 --> 4
(3 rows)
SELECT 'Wasn''t it 4 ?' as nextval, nextval ('next_id') as value;
nextval | value
---------------+-------
Wasn't it 4 ? | 4
(1 row)
insert into ids (idesc) select textcat (idesc, '. Copy.') from ids;
SELECT * FROM ids;
id | idesc
----+-------------------------
2 | first: -2 --> 2
3 | second: -1 --> 3
4 | third: 1 --> 4
5 | first: -2 --> 2. Copy.
6 | second: -1 --> 3. Copy.
7 | third: 1 --> 4. Copy.
(6 rows)
```
参考
https://www.postgresql.org/docs/devel/contrib-spi.html
contrib/spi/autoinc.example
https://www.percona.com/blog/2019/08/15/faster-lightweight-trigger-function-in-c-for-postgresql/
https://www.percona.com/blog/2019/07/31/postgresql-simple-c-extension-development-for-a-novice-user/
https://www.percona.com/blog/2019/04/05/writing-postgresql-extensions-is-fun-c-language/
https://www.postgresql.org/docs/12/plpgsql-trigger.html#PLPGSQL-DML-TRIGGER
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
