From 9acbd8e14f6ef3ec5a04081294a26ecc3a3333ec Mon Sep 17 00:00:00 2001
From: Matthias Andreas Benkard <code@mail.matthias.benkard.de>
Date: Tue, 3 Jul 2012 20:25:09 +0200
Subject: Schema: Add transitive closure functions for Lafargue message
 references.

---
 migrations/2_add_transitive_references.sql | 27 +++++++++++++++++++++++++++
 1 file changed, 27 insertions(+)
 create mode 100644 migrations/2_add_transitive_references.sql

(limited to 'migrations')

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;
-- 
cgit v1.2.3