postgresql – 如何在语句级触发器中获取受影响的基表行数

概述我有这些表： CREATE EXTENSION citext;CREATE EXTENSION "uuid-ossp";CREATE TABLE cities( city_id serial PRIMARY KEY, city_name citext NOT NULL UNIQUE);INSERT INTO cities(city_name) VALUES('New 我有这些表：

CREATE EXTENSION citext;CREATE EXTENSION "uuID-ossp";CREATE table citIEs(    city_ID serial PRIMARY KEY,city_name citext NOT NulL UNIQUE);INSERT INTO citIEs(city_name) VALUES('New York'),('Paris'),('MadrID');CREATE table eTags(    etag_name varchar(128) PRIMARY KEY,etag_value uuID);INSERT INTO eTags(etag_name,etag_value)VALUES ('citIEs',uuID_generate_v4());

我想在城市表更改时更新城市etag.如果插入,更新或删除语句没有影响行,我想避免更改城市etag,所以我编写了以下语句级别触发器：

CREATE OR REPLACE FUNCTION update_etag()  RETURNS trigger AS$BODY$    DECLARE         record_count integer;        vetag_name varchar(128);    BEGIN    GET DIAGNOSTICS record_count = ROW_COUNT;    vetag_name := TG_ARGV[0];    RAISE NOTICE 'affected %:%',vetag_name,record_count;    IF record_count = 0 THEN         RETURN NulL;    END IF;    UPDATE eTags SET etag_value = uuID_generate_v4()     WHERE etag_name = vetag_name;    RETURN null;    END;$BODY$  LANGUAGE plpgsql VolATILE;CREATE TRIGGER update_citIEs_etag_trigger  AFTER INSERT OR UPDATE OR DELETE  ON citIEs  FOR EACH STATEMENT  EXECUTE PROCEDURE update_etag('citIEs');

但是GET DIAGNOSTICS record_count = ROW_COUNT;对我不起作用,因为它总是返回0.

如果我执行以下 *** 作：

DELETE FROM citIEs;

输出如下：

NOTICE: affected citIEs:0 query returned successfully: 3 rows
affected,47 msec execution time.

有没有办法弄清楚在PostgreSQL语句级触发器中触发触发器的语句会影响多少行？

版本10

CREATE TRIGGER

...[ REFERENCING { { olD | NEW } table [ AS ] Transition_relation_name } [ ... ] ]...

https://www.postgresql.org/docs/current/static/release-10.html

Add AFTER trigger Transition tables to record changed rows (Kevin
Grittner,Thomas Munro)

Transition tables are accessible from triggers written in server-sIDe
languages.

Example

解决它：

CREATE OR REPLACE FUNCTION update_etag()  RETURNS trigger AS$BODY$    DECLARE         record_count integer;        vetag_name varchar(128);    begin    IF (TG_OP = 'DELETE') or (TG_OP = 'UPDATE') THEN       select count(*) from oldtbl into record_count ;    ELSE       select count(*) from newtbl into record_count ;    END IF;        vetag_name := TG_ARGV[0];    RAISE NOTICE 'affected %:%:%',TG_OP,record_count;    IF record_count = 0 THEN         RETURN NulL;    END IF;    UPDATE eTags SET etag_value = uuID_generate_v4()     WHERE etag_name = vetag_name;    RETURN null;    END;$BODY$  LANGUAGE plpgsql VolATILE;CREATE TRIGGER update_ins_citIEs_etag_trigger  AFTER INSERT  ON citIEs  REFERENCING NEW table AS newtbl   FOR EACH STATEMENT  EXECUTE PROCEDURE update_etag('citIEs'); CREATE TRIGGER update_upd_citIEs_etag_trigger  AFTER UPDATE  ON citIEs  REFERENCING olD table AS oldtbl   FOR EACH STATEMENT  EXECUTE PROCEDURE update_etag('citIEs');  CREATE TRIGGER update_del_citIEs_etag_trigger  AFTER DELETE  ON citIEs  REFERENCING olD table AS oldtbl   FOR EACH STATEMENT  EXECUTE PROCEDURE update_etag('citIEs');  so=# INSERT INTO citIEs(city_name) VALUESso-# ('New York'),('MadrID');NOTICE:  affected citIEs:INSERT:3INSERT 0 3so=# select * from eTags; etag_name |              etag_value              -----------+-------------------------------------- citIEs    | dc7d1525-eea7-4822-b736-5141a20764f8(1 row)so=# insert into citIEs(city_name) values ('Budapest');NOTICE:  affected citIEs:INSERT:1INSERT 0 1so=# select * from eTags; etag_name |              etag_value              -----------+-------------------------------------- citIEs    | df835f44-dada-4a94-bb62-5890f2316103(1 row)so=# delete from citIEs where city_ID > 42;NOTICE:  affected citIEs:DELETE:0DELETE 0so=# select * from eTags; etag_name |              etag_value              -----------+-------------------------------------- citIEs    | df835f44-dada-4a94-bb62-5890f2316103(1 row)

总结

以上是内存溢出为你收集整理的postgresql – 如何在语句级触发器中获取受影响的基表行数全部内容，希望文章能够帮你解决postgresql – 如何在语句级触发器中获取受影响的基表行数所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。