A former primary school teacher and member of the Bunratty Castle Entertainers who was ordained a priest in 1994 has been named the Catholic bishop of Waterford and Lismore.