I thought this was interesting, and wanted to make sure I understood what is going on, but the more tests I run the more confused I get.
if I take the exact set up outlined by Mosche I get the same results in 9.3 (as expected) , but if I insert one row before I run the sql the CTE is executed and I get a new row in the table. I was hoping that I would see a difference in the explain, but the explain with an empty table where the CTE is not executed is identical to the explain where there is one row in the table already and the CTE is executed resulting in a new row. I thought maybe Postgres was not executing the CTE because it knows that there are no rows in the table for it to delete, however if I change the CTE to be an insert returning instead of a function I get different results. Even when the table is empty I get new row created.
I would really like to know "why' it is working like this so something similar does not come back and bite me in the future.
Thanks
