From 4c8485ba1b781b1dedeb0d59fd9a8c5f1247c691 Mon Sep 17 00:00:00 2001
From: "Karl O. Pinc kop@karlpinc.com" <kop@karlpinc.com>
Date: Sun, 21 Sep 2025 18:30:16 +0000
Subject: [PATCH] New macro for checking that PEOPLE.Active is TRUE before use

---
 db/include/macros.m4 | 53 ++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 53 insertions(+)

diff --git a/db/include/macros.m4 b/db/include/macros.m4
index 9cab3bc..44a4668 100644
--- a/db/include/macros.m4
+++ b/db/include/macros.m4
@@ -164,6 +164,59 @@ define({inserted_null}, {dnl
 })
 changequote(`,')
 
+dnl Plpgsql fragment for preventing the use of an inactive PEOPLE row.
+dnl
+dnl Syntax: person_active(table, column, row_desc)
+dnl Variables required:
+dnl   table
+dnl     The name of the table using the PEOPLE row.
+dnl   column
+dnl     The name of the column holding the PEOPLE key.
+dnl   row_desc
+dnl     Text identifying the row in the table, along with other useful
+dnl     column values.
+dnl     (Should conform to (Key) = (foo), etc., style.)
+dnl Restrictions: Must be used in a FOR EACH ROW insert trigger function.
+changequote({,})
+define({person_active}, {dnl
+    IF TG_OP = 'INSERT'
+       OR (NEW.$2 IS DISTINCT FROM OLD.$2
+           AND NEW.$2 IS NOT NULL) THEN
+      DECLARE
+        this_name TEXT;
+      BEGIN
+        SELECT people.name
+          INTO this_name
+          FROM people
+          WHERE person = NEW.$2
+                AND NOT people.active;
+        IF FOUND THEN
+          RAISE EXCEPTION data_exception USING
+                MESSAGE = 'Error '
+                          || CASE
+                               WHEN TG_OP = 'INSERT' THEN
+                                 'inserting '
+                               ELSE
+                                 'updating '
+                             END
+                          || 'into $1'
+              , DETAIL = 'Only PEOPLE rows with a TRUE Active value can be '
+                         || 'used: '
+                         || $3
+                         || ': Value ($2) = ('
+                         || NEW.$2
+                         || ')'
+              , HINT = 'Change the PEOPLE.Active column, of the related $2 '
+                       || 'value, to TRUE before using the PEOPLE row';
+        END IF;
+      END;
+    END IF;
+
+    IF NEW.$2 IS NOT NULL THEN
+    END IF;
+})
+changequote(`,')
+
 dnl Strings used in error messages
 dnl
 define(`sdb_cannot_change_msg',
-- 
2.34.1