2

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.

2

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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.