Notes

DO $$
DECLARE
other_group TEXT;
exposure_groups TEXT[] := ARRAY['asset_class', 'asset_subclass', 'china_vie', 'country',
'em', 'ex_us', 'ex_us_ca', 'industry', 'industry_group',
'region', 'sector', 'subindustry'];
exposure_group TEXT;
view_name TEXT;
BEGIN
FOREACH exposure_group IN ARRAY exposure_groups
LOOP
FOREACH other_group IN ARRAY exposure_groups
LOOP
IF exposure_group < other_group THEN
view_name := format('%I_x_%I_exposure_groups', exposure_group, other_group);
IF NOT EXISTS (SELECT 1 FROM pg_matviews WHERE schemaname = 'yyyy' AND matviewname = view_name) THEN
EXECUTE format(
'CREATE MATERIALIZED VIEW proto.%I_x_%I_exposure_groups AS
SELECT eg1.listing_uid,
tsrange(
GREATEST(eg1.effective_during.lower, eg2.effective_during.lower),
LEAST(eg1.effective_during.upper, eg2.effective_during.upper)
) AS effective_during,
%L AS exposure_category
FROM xxxx.%I_exposure_groups eg1
INNER JOIN onyx_api.%I_exposure_groups eg2
ON eg1.listing_uid = eg2.listing_uid
AND eg1.effective_during && eg2.effective_during
GROUP BY eg1.listing_uid',
exposure_group, other_group, format('%I_x_%I', exposure_group, other_group), exposure_group, other_group
);
END IF;
END IF;
END LOOP;
END LOOP;
END $$;