Re: [PG9.1] CTE usage

On 16.9.2013 13:26, Alban Hertroys wrote:
> On 16 September 2013 11:58, Ladislav Lenart <lenartlad@volny.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