Recursion in PL/pgSQL

recursion.sql

--
-- Name: Relationships; Type: TABLE; Schema: parties; Owner: postgres; Tablespace: 
--

CREATE TABLE "Relationships" (
    id integer NOT NULL,
    relationshiptype_id integer,
    roletype_id integer,
    lower_party_id integer,
    upper_party_id integer,
    start_date date,
    end_date date,
    state integer
);

--
-- Name: partyLeads; Type: VIEW; Schema: parties; Owner: postgres
--

CREATE VIEW "partyLeads" AS
    SELECT r.upper_party_id AS unit_party_id, pn.party_id AS lead_party_id, pn.lastname, pn.firstname FROM ("partyNames" pn JOIN "Relationships" r ON (((r.lower_party_id = pn.party_id) AND (r.relationshiptype_id = 2))));

--
-- Name: partyNames; Type: VIEW; Schema: parties; Owner: postgres
--

CREATE VIEW "partyNames" AS
    SELECT DISTINCT pd.party_id, pda.value AS unitname, pdb.value AS lastname, pdc.value AS firstname FROM ((("PartyDetails" pd LEFT JOIN "PartyDetails" pda ON (((pd.party_id = pda.party_id) AND (pda.detailtype_id = 1)))) LEFT JOIN "PartyDetails" pdb ON (((pd.party_id = pdb.party_id) AND (pdb.detailtype_id = 3)))) LEFT JOIN "PartyDetails" pdc ON (((pd.party_id = pdc.party_id) AND (pdc.detailtype_id = 4)))) ORDER BY pd.party_id;

--
-- Name: Parties; Type: TABLE; Schema: parties; Owner: postgres; Tablespace: 
--

CREATE TABLE "Parties" (
    id integer NOT NULL,
    partytype_id integer,
    state integer
);


-- Get super branches

WITH RECURSIVE superParty AS
(
	SELECT * FROM parties."Relationships" WHERE lower_party_id IN ( :my_party_ids )

	UNION ALL

	SELECT r.*
	FROM
		parties."Relationships" AS r
	JOIN
		superParty AS sp
		ON r.lower_party_id = sp.upper_party_id
)
SELECT DISTINCT sp.upper_party_id AS upper_unit_party_id, pn.unitname, pl.lead_party_id, pl.lastname, pl.firstname
FROM superParty AS sp
JOIN parties."partyNames" AS pn
	ON sp.upper_party_id = pn.party_id
JOIN parties."partyLeads" AS pl
	ON pl.unit_party_id = sp.upper_party_id
JOIN parties."Parties" AS p
	ON sp.upper_party_id = p.id
	AND p.partytype_id = 1
	
-- get sub branches

WITH RECURSIVE subParty AS
(
	SELECT * FROM parties."Relationships" WHERE upper_party_id IN ( :my_party_ids )

	UNION ALL

	SELECT r.*
	FROM
		parties."Relationships" AS r
	JOIN
		subParty AS sp
		ON r.upper_party_id = sp.lower_party_id
)
SELECT DISTINCT p.partytype_id, sp.lower_party_id AS lower_unit_party_id, pn.unitname, ( pn.firstname || ' ' ||  pn.lastname ) AS fullname
FROM subParty AS sp
LEFT JOIN parties."partyNames" AS pn
	ON sp.lower_party_id = pn.party_id

JOIN parties."Parties" AS p
	ON sp.lower_party_id = p.id

-- get unit structure
	
WITH RECURSIVE subParty AS
(
	SELECT * FROM parties."Relationships" WHERE upper_party_id IS NULL

	UNION ALL

	SELECT r.*
	FROM
		parties."Relationships" AS r
	JOIN
		subParty AS sp
		ON r.upper_party_id = sp.lower_party_id
)
SELECT DISTINCT sp.lower_party_id, sp.upper_party_id, pn.unitname
FROM subParty AS sp
JOIN parties."partyNames" AS pn
	ON sp.lower_party_id = pn.party_id
JOIN parties."Parties" AS p
	ON sp.lower_party_id = p.id
WHERE p.partytype_id = 1