SQLize Online / PHPize Online  /  SQLtest Online

-- Tables CREATE TABLE company (account_id VARCHAR PRIMARY KEY, company_id INT UNIQUE); CREATE TABLE companyrelations (company_id INT, parent_company_id INT, relation_type VARCHAR); -- Data INSERT INTO company VALUES ('A', 1), ('B', 2), ('C', 3), ('D', 4), ('E', 5), ('X', 6); INSERT INTO companyrelations VALUES (2, 1, 'Delegated'), (3, 2, 'Delegated'), (4, 3, 'Inherited'), (5, 4, 'Inherited'); -- Function CREATE OR REPLACE FUNCTION get_delegation_relation( parent_account_id VARCHAR, child_account_id VARCHAR ) RETURNS TEXT AS $$ DECLARE result TEXT; parent_company_id INT; child_company_id INT; BEGIN SELECT company_id INTO parent_company_id FROM company WHERE account_id = parent_account_id; SELECT company_id INTO child_company_id FROM company WHERE account_id = child_account_id; IF EXISTS ( SELECT 1 FROM companyrelations WHERE company_id = child_company_id AND parent_company_id = parent_company_id AND relation_type = 'Delegated' ) THEN RETURN 'Delegated'; ELSIF EXISTS ( SELECT 1 FROM companyrelations WHERE company_id = parent_company_id AND parent_company_id = child_company_id AND relation_type = 'Delegated' ) THEN RETURN 'ChildDelegated'; END IF; WITH RECURSIVE relation_chain AS ( SELECT company_id, parent_company_id FROM companyrelations WHERE company_id = 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 = parent_company_id ) THEN 'Delegated' ELSE NULL END INTO result; RETURN COALESCE(result, 'No relation found'); END; $$ LANGUAGE plpgsql; -- Test SELECT get_delegation_relation('A', 'E'); -- should return 'Delegated'