postgresql 如何在postgres中更新VIEW更改触发的表

ao218c7q  于 2023-08-04  发布在  PostgreSQL
关注(0)|答案(1)|浏览(155)

在posterre中,我在两个表上有一个联合VIEW。其中一个表有timestamp列,另一个表没有(我们无法更改此列!).
现在,我希望在另一个表更新时更新timestamp列。我认为在视图上触发是可行的。但这并没有像预期的那样工作,因为我没有更新VIEW,而是更新TABLE。请考虑以下最小示例:

create table test(i int, update_ts timestamp);
create table test_2(i int, s text);
create view test_view as select t.i, t2.s, update_ts from test t left join test_2 t2 on t2.i = t.i;

insert into test values(1, now());
insert into test values(2, now());
insert into test_2 values(1, 'one');
insert into test_2 values(2, 'two');

select * from test_view;
-- all is fine

update test_2 set s = 'foo' where i = 2;

select * from test_view;
-- colums s is fine, but how to trigger a refresh of the update_ts column of id 2?

字符串
在我的场景中,不可能在test_2上创建UPDATE触发器,因为由于旧的讨厌的导入机制,整个表都会更新。这也会导致id 1的更新,这不是有意的。也不可能在test_2中添加一个时间戳列,并在test_view中加载两者的MAX值(这实际上是一个不错的解决方案)。

5uzkadbs

5uzkadbs1#

每当test_2中相应行中的s列发生更改时,以下触发函数和触发定义将更新test中的update_ts

CREATE OR REPLACE FUNCTION update_ts_on_change_func() RETURNS TRIGGER AS
$func$
BEGIN
  UPDATE test
    SET update_ts = now()
    WHERE test.i IN (SELECT new_rows.i
                       FROM new_rows
                       JOIN old_rows
                         ON new_rows.i = old_rows.i AND new_rows.s IS DISTINCT FROM old_rows.s);

  RETURN NULL;
END
$func$ LANGUAGE plpgsql;

CREATE TRIGGER update_ts_on_change
  AFTER UPDATE
  ON test_2
  REFERENCING NEW TABLE AS new_rows
              OLD TABLE AS old_rows
  FOR STATEMENT
  EXECUTE FUNCTION update_ts_on_change_func();

字符串
可以通过在触发函数的连接条件中OR来检查多个列的变化;例如:

ON new_rows.i = old_rows.i AND
   (new_rows.s IS DISTINCT FROM old_rows.s OR
    new_rows.t IS DISTINCT FROM old_rows.t OR
    new_rows.u IS DISTINCT FROM old_rows.u)


此方法仅更新test中对应于test_2中更改的行的行。test中的所有其他行保留其值。

相关问题