mtv_test.sql

application/sql

Download

CREATE TABLE mvtest_t (id int NOT NULL PRIMARY KEY, type text NOT NULL, amt numeric NOT NULL);
INSERT INTO mvtest_t VALUES
  (1, 'x', 2),
  (2, 'x', 3),
  (3, 'y', 5),
  (4, 'y', 7),
  (5, 'z', 11);
CREATE MATERIALIZED VIEW mvtest_tm AS SELECT type, sum(amt) AS totamt FROM mvtest_t GROUP BY type WITH NO DATA;
REFRESH MATERIALIZED VIEW mvtest_tm;
CREATE UNIQUE INDEX mvtest_tm_type ON mvtest_tm (type);
CREATE MATERIALIZED VIEW mvtest_tmm AS SELECT sum(totamt) AS grandtot FROM mvtest_tm;
REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_tm;