CODEWITHSUNDEEP
sqlpostgresqlplpgsqldatabase-trigger
zakaria mouqcit
zakaria mouqcit

Reputation: 393

Counting number of updates

Hi Every one I created this trigger function to count number of rows affected by an update . 

create table smt (
id serial primary key,
    num int
)
CREATE OR REPLACE FUNCTION count_updated()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    AS $BODY$
DECLARE 
    n int;
BEGIN 
    IF(TG_OP = 'UPDATE') THEN       
        get diagnostics n = row_count;
        insert into smt (num) values (n);
        return null;
    END IF;
END;
$BODY$;

CREATE TRIGGER count_updt
AFTER UPDATE  ON test
    FOR EACH ROW EXECUTE PROCEDURE count_updated();

what I want to do is store the number of updated rows of test in smt using GET DIAGNOSTICS, but when I read the content of smt table, the field which is reserved to stock the row_count is 0 even after an update. If you have any idea in-light me. Cordially.

Upvotes: 0

Views: 517

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51466

to just get amount of updates (and possibly compare before and after some statements, you can just 

select n_tup_upd from pg_stat_all_tables where relname = 'test'

Otherwise you can use GET DIAGNOSTICS straight with update (for plpgsql) and update ... returning with CTE and count(1) for sql. using trigger, saving amount to table and selecting it looks an overhead indeed

Upvotes: 1

Related Questions