Hello all.
I am curious about the following usage of CTEs:
Imagine three tables:
* item (id, item_type1_id, item_type2_id, ...)
* item_type1 (id, ...)
* item_type2 (id, ...)
where
* item_type1_id is FK to item_type1 (id)
* item_type2_id is FK to item_type2 (id)
Items are of two types (type1 and type2). Each item type has different data
columns. An item is either of type1 (item_type1_id is populated) or of type2
(item_type2_id is populated). I want to delete some items along with the
corresponding rows in the tables item_type1 and item_type2 (they have no meaning
without the 'parent'). I have written the following CTE (I want to compute
items_to_delete only once):
WITH
items_to_delete AS (
SELECT
item.id AS item_id,
item.item_type1_id AS item_type1_id,
item.item_type2_id AS item_type2_id
FROM item
WHERE ... -- limit the set of items to delete
,
delete_items AS (
DELETE FROM item
WHERE item.id IN (SELECT item_id FROM items_to_delete)
),
delete_items_type1 AS (
DELETE FROM item_type1
WHERE item_type1.id IN (SELECT item_type1_id FROM items_to_delete)
),
delete_items_type2 AS (
DELETE FROM item_type2
WHERE item_type2.id IN (SELECT item_type2_id FROM items_to_delete)
)
SELECT 1;
Should this work? I thought that CTEs are evaluated once in the order of
definition, regardless when/if they are used, so:
* First, items_to_delete is populated from the existing data.
* Then delete_items deletes some items according to items_to_delete.
* Then delete_items_type1 deletes some items of type1 according to items_to_delete.
* Finally delete_items_type2 deletes some items of type2 according to
items_to_delete.
Does the deletes somehow modify the contents of the 'temporary table'
items_to_delete? The thing is I ran a very similar script on our testing
environment as a part of our DB migration to the new model and have just
realized that only items were deleted (i.e. no item_type1 and item_type2).
Hence I would like to ask if any of you see some obvious flaw in this style of
CTE usage.
Thank you in advance,
Ladislav Lenart
