From 37a8253033ca915a3139bfbb8df0fb8627e79979 Mon Sep 17 00:00:00 2001 From: miker Date: Sat, 26 Apr 2008 16:13:13 +0000 Subject: [PATCH] add reporter.materialized_simple_record to the base schema git-svn-id: svn://svn.open-ils.org/ILS/trunk@9456 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Pg/reporter-schema.sql | 20 ++++++++++++++++++++ 1 file changed, 20 insertions(+) diff --git a/Open-ILS/src/sql/Pg/reporter-schema.sql b/Open-ILS/src/sql/Pg/reporter-schema.sql index 30b3bd0ef4..74662ba25a 100644 --- a/Open-ILS/src/sql/Pg/reporter-schema.sql +++ b/Open-ILS/src/sql/Pg/reporter-schema.sql @@ -154,6 +154,26 @@ SELECT r.id, WHERE r.deleted IS FALSE GROUP BY 1,2,3,4,5,6,8,9; +CREATE TABLE reporter.materialized_simple_record AS SELECT * FROM reporter.simple_record WHERE 1=0; +ALTER TABLE reporter.materialized_simple_record ADD PRIMARY KEY (id); + +CREATE OR REPLACE FUNCTION reporter.simple_rec_sync () RETURNS TRIGGER AS $$ +BEGIN + IF TG_OP IN ('UPDATE','DELETE') THEN + DELETE FROM reporter.materialized_simple_record WHERE id = OLD.record; + END IF; + + IF TG_OP IN ('INSERT','UPDATE') AND NOT NEW.deleted THEN + INSERT INTO reporter.materialized_simple_record SELECT * FROM reporter.simple_record WHERE id = NEW.record; + END IF; + +END; +$$ LANGUAGE PLPGSQL; + +CREATE TRIGGER zzz_update_materialized_simple_record_tgr + AFTER INSERT OR UPDATE OR DELETE ON metabib.full_rec + FOR EACH ROW EXECUTE PROCEDURE reporter.simple_rec_sync(); + CREATE OR REPLACE VIEW reporter.demographic AS SELECT u.id, u.dob, -- 2.43.2