Bug? Function with side effects not evaluated in CTE

[Moshe Jacobson <moshe@xxxxxxxxxxxx>]

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.
2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339

“Quality is not an act, it is a habit.” — Aristotle