Reuse results from correlated subqueries in same query

Question

I have 4 tables (I actually have lots more that I want to do this with...but this is where I am starting).

• distr_catalogs: has many distr_catalog_brands, distr_catalog_system_types
• distr_catalog_brands: belongs to distr_catalogs
• distr_catalog_system_types: belongs to distr_catalogs
• brand_catalog_sections: belongs to distr_catalog_brands, distr_catalog_system_types

I am wanting to create a materialized view whose columns are:

catalog_id | catalog_name | brand_ids | system_type_ids | catalog_sections

• catalog_id & catalog_name come from the distr_catalog table
• brand_ids holds an array of the brand ids related to the catalog
• system_type_ids holds an array the system type ids related to the catalog
• catalog_sections holds an array of brand catalog section ids that are related to the brand_ids and system_type_ids

I can do all but the last just fine:

CREATE MATERIALIZED VIEW catalog_system_brands AS
select dc.id as catalog_id, dc.catalog_name, 
ARRAY(SELECT brand_id FROM distr_catalog_brands WHERE distr_catalog_id = dc.id) as brands,
ARRAY(SELECT id FROM distr_catalog_system_types WHERE display_status = true AND distr_catalog_id = dc.id) as system_type_ids,
from distr_catalogs dc

This gets me a great table that has all the right data. However the only way I can figure out to get the last column I need ... is to completely re-write the subqueries in the final array statement. I get an error every time I try to use the brands or system_type_ids aliases I defined in the two above arrays.

CREATE MATERIALIZED VIEW catalog_system_brands AS
select dc.id as catalog_id, dc.catalog_name, 
ARRAY(SELECT brand_id FROM distr_catalog_brands WHERE distr_catalog_id = dc.id) as brands,
ARRAY(SELECT id FROM distr_catalog_system_types WHERE display_status = true AND distr_catalog_id = dc.id) as system_type_ids,
ARRAY(SELECT id FROM brand_catalog_sections 
      WHERE brand_id = ANY(SELECT brand_id FROM distr_catalog_brands WHERE distr_catalog_id = dc.id)
      AND system_type_id = ANY(SELECT system_type_id FROM distr_catalog_system_types WHERE display_status = true AND distr_catalog_id = dc.id)) as section_ids
from distr_catalogs dc

How would I re-write the create statement to not have to re-run the subqueries?

Note: This is the first instance of this. However, the next table I bring into this view will need to use the results of the section_ids column to determine which systems to display. So this will get out of hand quickly if I am not able to reuse the results from a previous query.

Answer 1

Use LATERAL joins instead of correlated subqueries. This way you can reuse results to join to more tables:

SELECT dc.id AS catalog_id
     , dc.catalog_name
     , db.brands
     , ds.system_type_ids
     , bs.section_ids
FROM   distr_catalogs dc
CROSS  JOIN LATERAL (
   SELECT ARRAY(SELECT brand_id
                FROM   distr_catalog_brands
                WHERE  distr_catalog_id = dc.id) AS brands
   ) db
CROSS  JOIN LATERAL (
   SELECT ARRAY (
      SELECT id
      FROM   distr_catalog_system_types
      WHERE  display_status = true
      AND    distr_catalog_id = dc.id
      ) AS system_type_ids
   ) ds
CROSS  JOIN LATERAL (
   SELECT ARRAY(
      SELECT id
      FROM   brand_catalog_sections 
      WHERE  brand_id       = ANY(db.brands)
      AND    system_type_id = ANY(ds.system_type_ids)
      ) AS section_ids
   ) bs;

It's safe to use CROSS JOIN LATERAL (or just , LATERAL) here, because the array constructor always returns a row. Else, if a lateral subquery could come up empty (no row), you would use LEFT JOIN LATERAL (...) ON true to avoid losing rows.

Related:

• What is the difference between LATERAL and a subquery in PostgreSQL?
• Postgres datetime sort slow and not using index
• Build JSON object from one-to-many relationship data in a single query?