Corrections and improvements to `15-procedural-sql'.HEADmagister

1 files changed, 72 insertions, 60 deletions

diff --git a/15-procedural-sql/procedural-sql.ipynb b/15-procedural-sql/procedural-sql.ipynb
index f2894c1..7d22f14 100644
--- a/15-procedural-sql/procedural-sql.ipynb
+++ b/15-procedural-sql/procedural-sql.ipynb
@@ -794,19 +794,6 @@
    ]
   },
   {
-   "cell_type": "code",
-   "execution_count": null,
-   "id": "020a0d03",
-   "metadata": {},
-   "outputs": [],
-   "source": [
-    "%%sql\n",
-    "SELECT 'wxy'\n",
-    "UNION\n",
-    "SELECT 'xyz'"
-   ]
-  },
-  {
    "cell_type": "markdown",
    "id": "6593383a",
    "metadata": {},
@@ -1460,6 +1447,47 @@
    ]
   },
   {
+   "cell_type": "markdown",
+   "id": "c0cff4d2",
+   "metadata": {},
+   "source": [
+    "Let me first introduce to you the `IS DISTINCT FROM` operator."
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": null,
+   "id": "9f11033c",
+   "metadata": {},
+   "outputs": [],
+   "source": [
+    "%%sql\n",
+    "-- Two NULL values are treated as non-distinct.\n",
+    "SELECT (5, 6, NULL) IS DISTINCT FROM (5, 6, NULL)"
+   ]
+  },
+  {
+   "cell_type": "markdown",
+   "id": "e5d4a572",
+   "metadata": {},
+   "source": [
+    "_notes_\n",
+    "\n",
+    "`IS DISTINCT FROM` can be used to compare values or **tuples** (as in this case)."
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": null,
+   "id": "8b704943",
+   "metadata": {},
+   "outputs": [],
+   "source": [
+    "%%sql\n",
+    "SELECT (5, 6, NULL) IS DISTINCT FROM (5, 789, NULL)"
+   ]
+  },
+  {
    "cell_type": "code",
    "execution_count": null,
    "id": "f7367ff8",
@@ -1481,7 +1509,7 @@
     "CREATE OR REPLACE FUNCTION log_edge_change()\n",
     "    RETURNS TRIGGER AS $$\n",
     "    BEGIN\n",
-    "        IF old = new THEN\n",
+    "        IF old IS NOT DISTINCT FROM new THEN\n",
     "            RETURN NULL;\n",
     "        END IF;\n",
     "\n",
@@ -1731,14 +1759,17 @@
     "CREATE OR REPLACE FUNCTION update_is_edge()\n",
     "    RETURNS TRIGGER AS $$\n",
     "    BEGIN\n",
-    "        IF (old.from_id, old.from_label, old.to_id, old.to_label)\n",
-    "           <>\n",
-    "           (new.from_id, new.from_label, new.to_id, new.to_label)\n",
+    "        IF ((old.from_id, old.from_label, old.to_id, old.to_label)\n",
+    "            IS DISTINCT FROM\n",
+    "            (new.from_id, new.from_label, new.to_id, new.to_label))\n",
+    "           OR\n",
+    "           new.present IS NULL\n",
     "            THEN\n",
     "            RAISE EXCEPTION\n",
     "                'Illegal update (from_id = %, to_id = %)',\n",
     "                    old.from_id, old.to_id\n",
-    "                USING HINT = 'Please only set the `present'' column';\n",
+    "                USING\n",
+    "                HINT = 'Please only set `present'' to TRUE/FALSE';\n",
     "        END IF;\n",
     "\n",
     "        IF new.present THEN\n",
@@ -1800,12 +1831,10 @@
     "CREATE OR REPLACE FUNCTION update_is_edge()\n",
     "    RETURNS TRIGGER AS $$\n",
     "    BEGIN\n",
-    "        ASSERT COALESCE(\n",
-    "            (old.from_id, old.from_label, old.to_id, old.to_label)\n",
-    "            =\n",
-    "            (new.from_id, new.from_label, new.to_id, new.to_label),\n",
-    "            TRUE\n",
-    "        );\n",
+    "        ASSERT (old.from_id, old.from_label, old.to_id, old.to_label)\n",
+    "               IS NOT DISTINCT FROM\n",
+    "               (new.from_id, new.from_label, new.to_id, new.to_label);\n",
+    "        ASSERT new.present IS NOT NULL;\n",
     "\n",
     "        IF new.present THEN\n",
     "            CALL add_edge(old.from_label, old.to_label);\n",
@@ -2002,42 +2031,7 @@
    "id": "73130fb1",
    "metadata": {},
    "source": [
-    "If a trigger is only meant to be run in certain cases, we can utilize the `WHEN` clause of `CREATE TRIGGER`.\n",
-    "\n",
-    "Let me first introduce you to the `IS DISTINCT FROM` operator."
-   ]
-  },
-  {
-   "cell_type": "code",
-   "execution_count": null,
-   "id": "6ed6c225",
-   "metadata": {},
-   "outputs": [],
-   "source": [
-    "%%sql\n",
-    "-- Two NULL values are treated as non-distinct.\n",
-    "SELECT (5, 6, NULL) IS DISTINCT FROM (5, 6, NULL)"
-   ]
-  },
-  {
-   "cell_type": "markdown",
-   "id": "4ca99236",
-   "metadata": {},
-   "source": [
-    "_notes_\n",
-    "\n",
-    "`IS DISTINCT FROM` can be used to compare values or **tuples** (as in this case)."
-   ]
-  },
-  {
-   "cell_type": "code",
-   "execution_count": null,
-   "id": "574677cb",
-   "metadata": {},
-   "outputs": [],
-   "source": [
-    "%%sql\n",
-    "SELECT (5, 6, NULL) IS DISTINCT FROM (5, 789, NULL)"
+    "If a trigger is only meant to be run in certain cases, we can utilize the `WHEN` clause of `CREATE TRIGGER`."
    ]
   },
   {
@@ -2287,6 +2281,24 @@
     "\n",
     "After re-creating the trigger as `FOR EACH STATEMENT`, re-run the `DELETE`&`DELETE`&`INSERT`&`SELECT` sequence above.  See that the trigger now only fires once."
    ]
+  },
+  {
+   "cell_type": "markdown",
+   "id": "fa2eacdb",
+   "metadata": {},
+   "source": [
+    "`FOR EACH STATEMENT` triggers can additionally be declared as `DEFERRED`."
+   ]
+  },
+  {
+   "cell_type": "markdown",
+   "id": "2799c27c",
+   "metadata": {},
+   "source": [
+    "_notes_\n",
+    "\n",
+    "`DEFERRED` triggers fire later, at the end of transaction.  They are useful to implement delayed data consistency checks, so that temporal inconsistency during a transaction is allowed."
+   ]
   }
  ],
  "metadata": {