Let’s say we’re setting up a data structure for security, with group membership:<\/p>\n
\ncreate table groups (\n id int unique,\n name varchar unique,\n member_groups int[]\n);\n<\/pre>\n\n\ninsert into groups values (1, 'admins', '{}');\n\n-- automatically include any admins as users\ninsert into groups values (2, 'users', '{1}'); \n\n-- grant admins access to write\ninsert into groups values (3, 'writers', '{1}'); \n\n-- grant writers access to read\ninsert into groups values (4, 'readers', '{3}'); \n<\/pre>\nNow, we want to flatten this structure to find all transitive memberships. To do this, we take each group’s children, find their children, and concatenate the result recursively.<\/p>\n
Note that the column order in each part of the recursive sql must be the same, as well as in the column definition on the first line.<\/p>\n
\nWITH RECURSIVE all_groups(id, member_groups) AS (\n select\n id,\n member_groups\n from groups\n UNION ALL\n SELECT groups.id,\n all_groups.member_groups || groups.member_groups\n FROM all_groups\n JOIN groups\n ON all_groups.id = ANY (groups.member_groups)\n )\nSELECT id, uniq(flatMap(member_groups))\nFROM all_groups\nGROUP BY id;\n<\/pre>\n