-- 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'
Telegram: DB developers channel