postgresql - 每个唯一值执行一次函数

Question

我有两个表，其中包含与日常业务相关的数据:

CREATE TABLE main_table (
  main_id serial,
  cola text,
  colb text,
  colc text,
  CONSTRAINT main_table_pkey PRIMARY KEY (main_id)
);

CREATE TABLE second_table (
  second_id serial,
  main_id integer,
  cold text,
  CONSTRAINT second_table_pkey PRIMARY KEY (second_id),
  CONSTRAINT second_table_fkey FOREIGN KEY (main_id)
    REFERENCES main_table (main_id) MATCH SIMPLE
    ON UPDATE NO ACTION ON DELETE NO ACTION
);

我们需要知道这些表中的某些数据何时更新，以便可以生成导出并将其推送给第三方。我创建了第三个表来保存更新信息:

CREATE TYPE field AS ENUM ('cola', 'colb', 'colc', 'cold');
CREATE TABLE table_updates (
  main_id int,
  field field
  updated_on date NOT NULL DEFAULT NOW(),
  CONSTRAINT table_updates_fkey FOREIGN KEY (main_id)
    REFERENCES main_table (main_id) MATCH SIMPLE
    ON UPDATE NO ACTION ON DELETE NO ACTION
);

main_table 在UPDATE 查询之前有一个触发器来更新table_updates，这满足了跟踪四列更新中的三列的需要。

我可以轻松地将相同类型的触发器添加到 second_table，但是因为 main_id 不是唯一的，所以函数可以针对单个 main_id< 执行多次 值，这是不可取的。

如何创建一个函数，在更新 second_table 中的多行时，每个 main_id 只执行一次？

Answer 1

How can I create a function that, when updating several rows in second_table, executes only once per main_id?

如果您的插入是按 main_id 批量插入的，即 INSERT INTO tbl (main_id...) VALUES (main_id ...),(main_id ...),(main_id ...)你可以使用rule system为 INSERT 或 UPDATE

触发一次

For the things that can be implemented by both, which is best depends on the usage of the database. A trigger is fired once for each affected row. A rule modifies the query or generates an additional query. So if many rows are affected in one statement, a rule issuing one extra command is likely to be faster than a trigger that is called for every single row and must re-determine what to do many times. However, the trigger approach is conceptually far simpler than the rule approach, and is easier for novices to get right.

除此之外，您可能还想查看正常的 LISTEN , 和 NOTIFY .这使您能够使用异步操作。如果那是你的事，并且你决定保留触发方法，请考虑通过 tcn 触发更改通知模块。 .

我的建议是尽可能在应用程序中(在数据库之外)执行此操作。请记住，在 PostgreSQL 中，temp 表是 session 的本地表。所以你可以让每个加载器 session 做这样的事情，

BEGIN
  CREATE TEMP TABLE UNLOGGED etl_inventory;
  COPY foo FROM stdin;
  -- Are they different, if so `NOTIFY`
  -- UPSERT
COMMIT;

然后有一个守护进程在接收到 NOTIFY 事件时将导出添加到导出队列。