In a new database test, I create the following table and function: create table tb_item ( item integer );
create or replace function fn_new_item( in_item integer ) returns integer language plpgsql as $_$ begin insert into tb_item values( in_item ); return in_item; end $_$; Then, I execute the following query and see DELETE 0 as you would expect: test=# with tt_created as ( select fn_new_item(1) as item ) delete from tb_item where item not in (select item from tt_created); DELETE 0 However, if everything is correct, we should see one row in the table, where item = 1. But we do not: test=# table tb_item; item ------ (0 rows) I tried to outsmart Postgres with the following query, but it failed in the same way: with tt_created as ( select fn_new_item(1) as item ), tt_to_delete as ( select i.item from tb_item i left join tt_created c on i.item = c.item where c.item is null ) delete from tb_item i using tt_to_delete td where td.item = i.item; However, It behaves as one would expect if the first CTE is built with INSERT ... RETURNING. It also behaves as one would expect if the table starts out non-empty. This seems like a bug. Would someone please look into this? Thanks. Moshe Jacobson Manager of Systems Engineering, Nead Werx Inc. <http://www.neadwerx.com> 2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339 “Quality is not an act, it is a habit.” — Aristotle