postgresql - Postgres 计数插入/更新的记录

Question

我正在尝试跟踪我们与之同步的客户数据库。我需要将records_added(INSERTs) 和records_updated(UPDATEs) 记录到我们的表中。

我正在使用一个UPSERT来处理同步，并使用一个触发器来更新一个跟踪插入/更新的表。

问题是计算已更新的记录。我有 40 多列要检查，我必须将所有这些都放在我的检查逻辑中吗？有没有更优雅的方式？

有问题的代码部分：

select
    case
        when old.uuid = new.uuid
        and (
            old.another_field != new.another_field,
            old.and_another_field != new.and_another_field,
            -- many more columns here                         << This is particularly painful
            ) then 1
        else 0
    end into update_count;

可重现的例子：

-- create tables
CREATE TABLE IF NOT EXISTS example (uuid serial primary key, another_field int, and_another_field int);
CREATE TABLE IF NOT EXISTS tracker_table (
    records_added integer DEFAULT 0,
    records_updated integer DEFAULT 0,
    created_at date unique
);

-- create function
CREATE OR REPLACE FUNCTION update_records_inserted () RETURNS TRIGGER AS $body$ 

DECLARE update_count INT;
DECLARE insert_count INT;

BEGIN
-- ---------------- START OF BLOCK IN QUESTION -----------------
select
    case
        when old.uuid = new.uuid
        and (
            old.another_field != new.another_field
            -- many more columns here 
            ) then 1
        else 0
    end into update_count;

-- ------------------ END OF BLOCK IN QUESTION ------------------
-- count INSERTs
select
    case
        when old.uuid is null
        and new.uuid is not null then 1
        else 0
    end into insert_count;


-- --log the counts
-- raise notice 'update %', update_count;
-- raise notice 'insert %', insert_count;

-- insert or update count to tracker table
insert into
    tracker_table(
        created_at,
        records_added,
        records_updated
    )
VALUES
    (CURRENT_DATE, insert_count, update_count) ON CONFLICT (created_at) DO
UPDATE
SET
    records_added = tracker_table.records_added + insert_count,
    records_updated = tracker_table.records_updated + update_count;

RETURN NEW;

END;

$body$ LANGUAGE plpgsql;

-- Trigger
DROP TRIGGER IF EXISTS example_trigger ON example;

CREATE TRIGGER example_trigger
AFTER
INSERT
    OR
UPDATE
    ON example FOR EACH ROW EXECUTE PROCEDURE update_records_inserted ();

-- A query to insert, then update when number of uses > 1
insert into example(whatever) values (2, 3) ON CONFLICT(uuid) DO UPDATE SET another_field=excluded.another_field+1;

标签： postgresqlplpgsql