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_itemwhere 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 ileft join tt_created c on i.item = c.item where c.item is null
)
delete from tb_item iusing tt_to_delete tdwhere 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.
2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339
“Quality is not an act, it is a habit.” — Aristotle