From 39001794a3121c33c8cba50398b653a94328be40 Mon Sep 17 00:00:00 2001

From: Florian Schlichting <fsfs@debian.org>

Date: Tue, 3 Oct 2017 22:36:29 +0200

Subject: [PATCH] Update caldav_functions.sql for Postgresql 10

From version 10, Postgresql does not allow set-returning functions in

CASE statements. As we're using the functions recursively, we cannot

use LATERAL as suggested in the error message, but we can switch the

condition inside-out with only a limited amount of repetition.

--- dba/caldav_functions.sql

+++ dba/caldav_functions.sql

@@ -897,21 +897,37 @@ LANGUAGE plpgsql IMMUTABLE STRICT;

 -- Expanded group memberships out to some depth

 CREATE or REPLACE FUNCTION expand_memberships( INT8, INT ) RETURNS SETOF INT8 AS $$

-  SELECT group_id FROM group_member WHERE member_id = $1

-      UNION

-  SELECT expanded.g_id FROM (SELECT CASE WHEN $2 > 0 THEN expand_memberships( group_id, $2 - 1) END AS g_id

-                               FROM group_member WHERE member_id = $1) AS expanded

-                       WHERE expanded.g_id IS NOT NULL;

-$$ LANGUAGE sql STABLE STRICT;

+BEGIN

+  IF $2 > 0 THEN

+    RETURN QUERY

+      SELECT group_id FROM group_member WHERE member_id = $1

+        UNION

+      SELECT expanded.g_id FROM (SELECT expand_memberships( group_id, $2 - 1) AS g_id

+                                 FROM group_member WHERE member_id = $1) AS expanded

+                            WHERE expanded.g_id IS NOT NULL;

+  ELSE

+    RETURN QUERY

+      SELECT group_id FROM group_member WHERE member_id = $1;

+  END IF;

+END

+$$ LANGUAGE plpgsql IMMUTABLE STRICT;

 -- Expanded group members out to some depth

 CREATE or REPLACE FUNCTION expand_members( INT8, INT ) RETURNS SETOF INT8 AS $$

-  SELECT member_id FROM group_member WHERE group_id = $1

-      UNION

-  SELECT expanded.m_id FROM (SELECT CASE WHEN $2 > 0 THEN expand_members( member_id, $2 - 1) END AS m_id

-                               FROM group_member WHERE group_id = $1) AS expanded

-                       WHERE expanded.m_id IS NOT NULL;

-$$ LANGUAGE sql STABLE STRICT;

+BEGIN

+  IF $2 > 0 THEN

+    RETURN QUERY

+      SELECT member_id FROM group_member WHERE group_id = $1

+        UNION

+      SELECT expanded.m_id FROM (SELECT expand_members( member_id, $2 - 1) AS m_id

+                                 FROM group_member WHERE group_id = $1) AS expanded

+                            WHERE expanded.m_id IS NOT NULL;

+  ELSE

+    RETURN QUERY

+      SELECT member_id FROM group_member WHERE group_id = $1;

+  END IF;

+END

+$$ LANGUAGE plpgsql IMMUTABLE STRICT;