Open-ILS/src/sql/Pg/upgrade/XXXX.function.grp-tree-functions-via-cte.sql

   1 BEGIN;
   2
   3 -- Replace connectby from the tablefunc extension with CTEs
   4
   5
   6 CREATE OR REPLACE FUNCTION permission.grp_ancestors( INT ) RETURNS SETOF permission.grp_tree AS $$
   7     WITH RECURSIVE grp_ancestors_distance(id, distance) AS (
   8             SELECT $1, 0
   9         UNION
  10             SELECT ou.parent, ouad.distance+1
  11             FROM permission.grp_tree ou JOIN grp_ancestors_distance ouad ON (ou.id = ouad.id)
  12             WHERE ou.parent IS NOT NULL
  13     )
  14     SELECT ou.* FROM permission.grp_tree ou JOIN grp_ancestors_distance ouad USING (id) ORDER BY ouad.distance DESC;
  15 $$ LANGUAGE SQL ROWS 1;
  16
  17 -- Add a utility function to find descendant groups.
  18
  19 CREATE OR REPLACE FUNCTION permission.grp_descendants( INT ) RETURNS SETOF permission.grp_tree AS $$
  20     WITH RECURSIVE descendant_depth AS (
  21         SELECT  gr.id,
  22                 gr.parent
  23           FROM  permission.grp_tree gr
  24           WHERE gr.id = $1
  25             UNION ALL
  26         SELECT  gr.id,
  27                 gr.parent
  28           FROM  permission.grp_tree gr
  29                 JOIN descendant_depth dd ON (dd.id = gr.parent)
  30     ) SELECT gr.* FROM permission.grp_tree gr JOIN descendant_depth USING (id);
  31 $$ LANGUAGE SQL ROWS 1;
  32
  33 -- Add utility functions to work with permission groups as general tree-ish sets.
  34
  35 CREATE OR REPLACE FUNCTION permission.grp_tree_full_path ( INT ) RETURNS SETOF permission.grp_tree AS $$
  36         SELECT  *
  37           FROM  permission.grp_ancestors($1)
  38                         UNION
  39         SELECT  *
  40           FROM  permission.grp_descendants($1);
  41 $$ LANGUAGE SQL STABLE ROWS 1;
  42
  43 CREATE OR REPLACE FUNCTION permission.grp_tree_combined_ancestors ( INT, INT ) RETURNS SETOF permission.grp_tree AS $$
  44         SELECT  *
  45           FROM  permission.grp_ancestors($1)
  46                         UNION
  47         SELECT  *
  48           FROM  permission.grp_ancestors($2);
  49 $$ LANGUAGE SQL STABLE ROWS 1;
  50
  51 CREATE OR REPLACE FUNCTION permission.grp_tree_common_ancestors ( INT, INT ) RETURNS SETOF permission.grp_tree AS $$
  52         SELECT  *
  53           FROM  permission.grp_ancestors($1)
  54                         INTERSECT
  55         SELECT  *
  56           FROM  permission.grp_ancestors($2);
  57 $$ LANGUAGE SQL STABLE ROWS 1;
  58
  59 COMMIT;
  60