Todd Todd - 3 months ago 14
SQL Question

What Is the Name of an Object Created in a WITH Clause in a Materialized View in Postgres?

I am having a hard time searching through PostgreSQL documentation to find the right terminology to associate with table/view creation within the

WITH
clause of a
MATERIALIZED VIEW
.

For the following matview (the
AS WITH
clause is the point of focus) :

CREATE MATERIALIZED VIEW my_big_fat_payments_matview AS WITH
some_structure_representing_records_paid AS
(
SELECT polymorphic_fk_id, polymorphic_fk_type, amount, transaction_time
FROM my_big_fat_billing_table
WHERE some_condition = 'A First Condition'
AND some_other_condition IS NOT NULL
),
some_structure_representing_records_discounted AS
(
SELECT polymorphic_fk_id, polymorphic_fk_type, amount, transaction_time
FROM my_big_fat_billing_table
WHERE some_condition = 'A Second Condition'
AND some_other_condition IS NOT NULL
),
some_structure_representing_records_misc AS
(
SELECT polymorphic_fk_id, polymorphic_fk_type, amount, transaction_time
FROM my_big_fat_billing_table
WHERE some_condition = 'A Threeved Condition'
AND some_other_condition IS NOT NULL
)
SELECT
thetable.column_a AS column_a,
thetable.column_x AS column_x,
thetable.amount AS amount,
secondtable.amount AS second_amount,
thirdtable.amount AS third_amount,
fourthtable.amount AS fourth_amount
FROM my_big_fat_billing_table AS thetable
LEFT JOIN some_structure_representing_records_paid AS secondtable
ON thetable.polymorphic_fk_id = secondtable.polymorphic_fk_id
LEFT JOIN some_structure_representing_records_discounted AS thirdtable
ON thetable.polymorphic_fk_id = thirdtable.polymorphic_fk_id
LEFT JOIN some_structure_representing_records_misc AS fourthtable
ON thetable.polymorphic_fk_id = fourthtable.polymorphic_fk_id
WHERE thetable.type = 'Some Type'


What is the name for the
some_structure_representing_records_paid
,
some_structure_representing_records_discounted
, and
some_structure_representing_records_misc
objects in the query? Are they views? I have been casually referring to them as "sub-views" of the matview, but I cant seem to find my way into deeper documentation of the
WITH
clause of this matview to determine if that sort of jargon is appropriate. If they are not views, what are they? Would they be synonyms? Are they temporary tables that only exist for the selection of data and creation of the matview?

Note: my matview is way more complicated than this, so I am not asking for a critique on structure/format/use/etc.

Answer

Credit goes to @AaronLS for the answer:

The "temp table" created with a WITH clause of the MATERIALIZED VIEW is known as a "Common Table Expression" or a CTE.

Full documentation can be found here: https://www.postgresql.org/docs/9.3/static/queries-with.html