Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

how to get the affected base table row count in a statement level trigger

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?

like image 859
Jesús López Avatar asked Jan 28 '16 17:01

Jesús López


1 Answers

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)
like image 183
cske Avatar answered Nov 07 '22 01:11

cske