From: | Ladislav Lenart <lenartlad(at)volny(dot)cz> |
---|---|
To: | "List, Postgres" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: [PG9.1] CTE usage |
Date: | 2013-09-16 11:57:45 |
Message-ID: | 5236F239.2030201@volny.cz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 16.9.2013 13:26, Alban Hertroys wrote:
> On 16 September 2013 11:58, Ladislav Lenart <lenartlad(at)volny(dot)cz> wrote:
>> 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):
>
> Wouldn't it be much easier to define an FK constraint with ON DELETE CASCADE?
> With that, you only need to worry about which rows you delete from the
> parent table and dependant children will be removed automatically.
Hello.
I don't quite follow. Having item.item_type1_id FK with ON DELETE CASCADE would
delete ITEM (the parent) when ITEM_TYPE1 (the child) is deleted. You suggests
the opposite direction. Could you please describe your proposal in more detail
(just the list of tables with their columns)?
Nevertheless, I am still curious about my original question(s):
* Whether is this style of CTE usage discouraged (i.e. rely on the in-order
evaluation of CTEs without even mentioning them in the top query).
* Any idea what could be wrong in my example.
Thank you,
Ladislav Lenart
From | Date | Subject | |
---|---|---|---|
Next Message | Ladislav Lenart | 2013-09-16 13:18:46 | Re: [PG9.1] CTE usage |
Previous Message | Alban Hertroys | 2013-09-16 11:26:01 | Re: [PG9.1] CTE usage |