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

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