1 files changed, 35 insertions, 0 deletions
diff --git a/lint_summarizer.fnc b/lint_summarizer.fnc
new file mode 100644
index 0000000..240eff3
--- /dev/null
+++ b/lint_summarizer.fnc
@@ -0,0 +1,35 @@
+CREATE OR REPLACE FUNCTION lint_summarizer(
+) RETURNS TRIGGER
+AS $$
+DECLARE
+	t_noOfCerts		lint_summary.NO_OF_CERTS%TYPE;
+BEGIN
+	IF TG_OP = 'INSERT' THEN
+		INSERT INTO lint_summary (
+				LINT_ISSUE_ID, ISSUER_CA_ID, NOT_BEFORE_DATE, NO_OF_CERTS
+			)
+			VALUES (
+				new.LINT_ISSUE_ID, new.ISSUER_CA_ID, new.NOT_BEFORE_DATE, 1
+			)
+			ON CONFLICT (LINT_ISSUE_ID, ISSUER_CA_ID, NOT_BEFORE_DATE) DO UPDATE
+				SET NO_OF_CERTS = lint_summary.NO_OF_CERTS + 1;
+		RETURN NEW;
+	ELSIF TG_OP = 'DELETE' THEN
+		UPDATE lint_summary
+			SET NO_OF_CERTS = NO_OF_CERTS - 1
+			WHERE LINT_ISSUE_ID = old.LINT_ISSUE_ID
+				AND ISSUER_CA_ID = old.ISSUER_CA_ID
+				AND NOT_BEFORE_DATE = old.NOT_BEFORE_DATE
+			RETURNING NO_OF_CERTS
+				INTO t_noOfCerts;
+		IF FOUND AND (t_noOfCerts = 0) THEN
+			DELETE FROM lint_summary
+				WHERE LINT_ISSUE_ID = old.LINT_ISSUE_ID
+					AND ISSUER_CA_ID = old.ISSUER_CA_ID
+					AND NOT_BEFORE_DATE = old.NOT_BEFORE_DATE
+					AND NO_OF_CERTS = 0;
+		END IF;
+		RETURN OLD;
+	END IF;
+END;
+$$ LANGUAGE plpgsql STRICT;