postgreTrigger在表更新时重新运行SQL查询

Question

假设我有3个表A,B,C和一个表D，这样D是通过以下查询创建的:

SELECT DISTINCT ON (col_a) col_a,col_b FROM A WHERE col_b IS NOT NULL
UNION
SELECT DISTINCT ON (col_a) col_a,col_b FROM B WHERE col_b IS NOT NULL
UNION
SELECT DISTINCT ON (col_a) col_a,col_b FROM C WHERE col_b IS NOT NULL

我希望D在插入任何表A,B或C时被更新。我想我会使用触发器来做到这一点，但我在网上找不到关于如何在插入触发器上重新运行查询的任何内容。

我想这样做的原因是因为它是一个更大查询中的子查询，而这个子查询确实占用了查询时间。我希望通过将其存储在定期更新自身的表中，可以将查询速度提高到可接受的程度。

Answer 1

创建视图将是直接的方法，如

CREATE OR REPLACE VIEW D AS
SELECT DISTINCT ON (col_a) col_a, col_b FROM A WHERE col_b IS NOT NULL UNION 
SELECT DISTINCT ON (col_a) col_a, col_b FROM B WHERE col_b IS NOT NULL UNION 
SELECT DISTINCT ON (col_a) col_a, col_b FROM C WHERE col_b IS NOT NULL