PostgreSQL表的实现历史 [英] Implementing history of PostgreSQL table

问题描述

我想实现PostgreSQL表更改的历史记录。该表是通过以下方式定义的：

I want to implement history of changes of PostgreSQL table. The table is defined the following way:

CREATE TABLE "ps_counters"
(
    "psid" integer NOT NULL,
    "counter" bigint[] NOT NULL
);

我希望历史记录表看起来像这样：

I want the history table to look like:

CREATE TABLE "ps_counters_history"
(
    "timestamp" timestamp NOT NULL,
    "psid" integer NOT NULL,
    "counter" bigint[] NOT NULL
);

我需要一个触发器和一个存储过程，该触发器和存储过程在<$ c中进行每次更改（插入或更新） $ c> ps_couneters 插入 ps_counters_history ，但除​​了防止 ps_counters_history 插入太大了，我想每月对 ps_counters_history 表进行分区。

I need a trigger and a stored procedure which on every change (insertion or update) in ps_couneters to insert in ps_counters_history, but in addition to prevent ps_counters_history to became too big I want partitioning of ps_counters_history table on every month.

推荐答案

我设法实现了它。

CREATE TABLE "ps_counters_history"
(
  "id" serial PRIMARY KEY,
  "timestamp" timestamp NOT NULL DEFAULT clock_timestamp(),
  "psid" integer NOT NULL,
  "counter" bigint[] NOT NULL
);

CREATE OR REPLACE FUNCTION ps_counters_history_trigger()
  RETURNS trigger AS
$BODY$
  DECLARE
    table_name text;
  BEGIN
    table_name := 'ps_counters_history_' || to_char(CURRENT_DATE, 'yyyy_mm');
    IF NOT EXISTS (SELECT 1 FROM pg_class WHERE relname = table_name)
    THEN
      EXECUTE 'CREATE TABLE IF NOT EXISTS ' || table_name ||
              ' () INHERITS (ps_counters_history);';
    END IF;
    EXECUTE 'INSERT INTO ' || table_name ||
            '(psid, counter) VALUES ($1.psid, $1.counter);' USING NEW;
    RETURN NEW;
  END
$BODY$
  LANGUAGE plpgsql;

CREATE TRIGGER ps_counters_history_trigger
AFTER INSERT OR UPDATE ON ps_counters FOR EACH ROW
EXECUTE PROCEDURE ps_counters_history_trigger();

这篇关于PostgreSQL表的实现历史的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！