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

发布时间：2020-12-13 18:10:03 所属栏目：百科来源：网络整理

导读：我有这些表： 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( e

我有这些表：

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) VALUES
so-# ('New York'),('Madrid');
NOTICE:  affected cities:INSERT:3
INSERT 0 3
so=# 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:1
INSERT 0 1
so=# 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:0
DELETE 0
so=# select * from etags;
 etag_name |              etag_value              
-----------+--------------------------------------
 cities    | df835f44-dada-4a94-bb62-5890f2316103
(1 row)

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!