-- -- permission_p_recursive_array/3 -- create or replace function permission_p_recursive_array( p_objects integer[], p_party_id integer, p_privilege character varying ) returns record as $$ DECLARE v_security_context_root integer; BEGIN v_security_context_root := acs__magic_object_id('security_context_root'); RETURN QUERY WITH RECURSIVE object_context(obj_id, context_id, orig_obj_id) AS ( SELECT unnest(p_objects), unnest(p_objects), unnest(p_objects) UNION ALL SELECT ao.object_id, CASE WHEN (ao.security_inherit_p = 'f' OR ao.context_id IS NULL) THEN v_security_context_root ELSE ao.context_id END, oc.orig_obj_id FROM object_context oc, acs_objects ao WHERE ao.object_id = oc.context_id AND ao.object_id != v_security_context_root ), privilege_ancestors(privilege, child_privilege) AS ( SELECT p_privilege, p_privilege UNION ALL SELECT aph.privilege, aph.child_privilege FROM privilege_ancestors pa JOIN acs_privilege_hierarchy aph ON aph.child_privilege = pa.privilege ) SELECT p.object_id, oc.orig_obj_id FROM acs_permissions p JOIN party_approved_member_map pap ON pap.party_id = p.grantee_id JOIN privilege_ancestors pa ON pa.privilege = p.privilege JOIN object_context oc ON p.object_id = oc.context_id WHERE pap.member_id = p_party_id; END; $$ language plpgsql;