I have these tables:
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());
I want to update the cities etag when the cities table changes. If no rows are affected by the insert, update or delete statement, I'd like to avoid to change the cities etag, so I wrote the following statement level trigger:
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');
However GET DIAGNOSTICS record_count = ROW_COUNT;
doesn't work for me, as it always returns 0.
If I execute the following:
DELETE FROM cities;
The following is output:
NOTICE: affected cities:0 Query returned successfully: 3 rows affected, 47 msec execution time.
Is there a way to figure out how many rows are affected by the statement that triggers the trigger in a PostgreSQL statement-level trigger?
Version 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
Solves it:
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 %:%:%', vetag_name,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'), ('Paris'), ('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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With