SQLize | PHPize | SQLtest

CREATE OR REPLACE FUNCTION get_delegation_relation( parent_account_id VARCHAR, child_account_id VARCHAR ) RETURNS TEXT AS $$ DECLARE result TEXT; v_parent_company_id INT; v_child_company_id INT; BEGIN -- Get company IDs for the accounts SELECT company_id INTO v_parent_company_id FROM company WHERE account_id = parent_account_id; SELECT company_id INTO v_child_company_id FROM company WHERE account_id = child_account_id; -- Check direct Delegated relationship IF EXISTS ( SELECT 1 FROM companyrelations WHERE company_id = v_child_company_id AND parent_company_id = v_parent_company_id AND relation_type = 'Delegated' ) THEN RETURN 'Delegated'; -- Check direct ChildDelegated relationship ELSIF EXISTS ( SELECT 1 FROM companyrelations WHERE company_id = v_parent_company_id AND parent_company_id = v_child_company_id AND relation_type = 'Delegated' ) THEN RETURN 'ChildDelegated'; END IF; -- Recursive Delegated lookup upward from child WITH RECURSIVE relation_chain AS ( SELECT company_id, parent_company_id FROM companyrelations WHERE company_id = v_child_company_id AND relation_type IN ('Delegated', 'Inherited') UNION ALL SELECT cr.company_id, cr.parent_company_id FROM companyrelations cr JOIN relation_chain rc ON cr.company_id = rc.parent_company_id WHERE cr.relation_type IN ('Delegated', 'Inherited') ) SELECT CASE WHEN EXISTS ( SELECT 1 FROM relation_chain WHERE parent_company_id = v_parent_company_id ) THEN 'Delegated' WHEN EXISTS ( SELECT 1 FROM relation_chain WHERE company_id = v_parent_company_id ) THEN 'ChildDelegated' ELSE 'No relation found' END INTO result; RETURN result; END; $$ LANGUAGE plpgsql; SELECT get_delegation_relation('A', 'B');