summaryrefslogtreecommitdiff
path: root/migrations
diff options
context:
space:
mode:
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;