Bug? Function with side effects not evaluated in CTE

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