summaryrefslogtreecommitdiff
path: root/migrations
diff options
context:
space:
mode:
authorMatthias Andreas Benkard <code@mail.matthias.benkard.de>2012-07-03 20:25:09 +0200
committerMatthias Andreas Benkard <code@mail.matthias.benkard.de>2012-07-03 20:25:09 +0200
commit9acbd8e14f6ef3ec5a04081294a26ecc3a3333ec (patch)
treefb2222003ce4eb9bcb3bb887dbcb4aa04196c003 /migrations
parent4519ac40237342e0216f5fe7af2029810edc3a6e (diff)
Schema: Add transitive closure functions for Lafargue message references.
Diffstat (limited to 'migrations')
-rw-r--r--migrations/2_add_transitive_references.sql27
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;