Search Postgresql Archives

[PG9.1] CTE usage

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux