3 SELECT evergreen.upgrade_deps_block_check('0979', :eg_version);
5 -- Replace connectby from the tablefunc extension with CTEs
8 CREATE OR REPLACE FUNCTION permission.grp_ancestors( INT ) RETURNS SETOF permission.grp_tree AS $$
9 WITH RECURSIVE grp_ancestors_distance(id, distance) AS (
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
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;
19 -- Add a utility function to find descendant groups.
21 CREATE OR REPLACE FUNCTION permission.grp_descendants( INT ) RETURNS SETOF permission.grp_tree AS $$
22 WITH RECURSIVE descendant_depth AS (
25 FROM permission.grp_tree gr
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;
35 -- Add utility functions to work with permission groups as general tree-ish sets.
37 CREATE OR REPLACE FUNCTION permission.grp_tree_full_path ( INT ) RETURNS SETOF permission.grp_tree AS $$
39 FROM permission.grp_ancestors($1)
42 FROM permission.grp_descendants($1);
43 $$ LANGUAGE SQL STABLE ROWS 1;
45 CREATE OR REPLACE FUNCTION permission.grp_tree_combined_ancestors ( INT, INT ) RETURNS SETOF permission.grp_tree AS $$
47 FROM permission.grp_ancestors($1)
50 FROM permission.grp_ancestors($2);
51 $$ LANGUAGE SQL STABLE ROWS 1;
53 CREATE OR REPLACE FUNCTION permission.grp_tree_common_ancestors ( INT, INT ) RETURNS SETOF permission.grp_tree AS $$
55 FROM permission.grp_ancestors($1)
58 FROM permission.grp_ancestors($2);
59 $$ LANGUAGE SQL STABLE ROWS 1;