I have the following 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 etag cities when the city table changes. If no lines affect the insert, update, or delete statement, I would like to avoid changing etag cities, so I wrote the following instruction 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 since it always returns 0.
If I do the following:
DELETE FROM cities;
The following is displayed:
NOTICE: affected cities: 0 Request successfully returned: 3 lines lead time 47 ms.
Is there a way to find out how many lines affect the statement that fires a trigger in a PostgreSQL instruction-level trigger?
source share