Schema: Add transitive closure functions for Lafargue message references.

1 files changed, 27 insertions, 0 deletions

diff --git a/migrations/2_add_transitive_references.sql b/migrations/2_add_transitive_references.sql
new file mode 100644
index 0000000..25eb3a5
--- /dev/null
+++ b/migrations/2_add_transitive_references.sql
@@ -0,0 +1,27 @@
+BEGIN TRANSACTION;
+
+CREATE FUNCTION compute_lazychat_transitive_references(message INTEGER)
+RETURNS TABLE (referrer INTEGER, referee INTEGER) AS $$
+  WITH RECURSIVE t(referrer, referee) AS (
+      SELECT * FROM lazychat_references WHERE referee = $1
+    UNION
+      SELECT lr.*
+        FROM lazychat_references lr
+       INNER JOIN t ON (t.referee = lr.referrer)
+  )
+  SELECT * FROM t
+$$ LANGUAGE SQL;
+
+CREATE FUNCTION compute_lazychat_transitive_referrals(message INTEGER)
+RETURNS TABLE (referrer INTEGER, referee INTEGER) AS $$
+  WITH RECURSIVE t(referrer, referee) AS (
+      SELECT * FROM lazychat_references WHERE referee = $1
+    UNION
+      SELECT lr.*
+        FROM lazychat_references lr
+       INNER JOIN t ON (lr.referee = t.referrer)
+  )
+  SELECT * FROM t
+$$ LANGUAGE SQL;
+
+COMMIT;