计算更新次数

问题描述

我每个人都创建了此触发函数，以计算受update影响的行数.

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();

我要做的是使用GET DIAGNOSTICS将test的更新行数存储在smt中，但是当我读取smt表的内容时，保留用于存储row_count的字段甚至为0更新后.如果您有任何想法请与我联系.亲切地

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.

推荐答案

只是获取大量更新(并且可能在比较某些语句之前和之后进行比较，您只需

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'

否则，您可以将GET DIAGNOSTICS直接用于更新(对于plpgsql)，将update ... returning与CTE和count(1)用于sql.使用触发器，将金额保存到表中并选择它确实确实有开销

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

这篇关于计算更新次数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！