illustrate 2 features of PostgreSQL's common table expressions

gistfile1.sql

-- this illustrates certain behaviour of CTE in Postgres.

-- define a new table and fill in some entries
-- ! make sure you dont mess up some existing prices table !
--
create table if not exists prices( id int, created_at int, price int);
insert into prices values (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5);

--
-- we now have 5 records in `prices` at the start, then:

with
  new_values as (
    select * from prices order by created_at limit 3 
  ),
  inserted as (
    insert into prices (id, created_at, price)
      select id, created_at + 1, price from new_values
  )

select * from prices;

-- this will output the original 5 entries, but if you now do another:

select * from prices;

-- there will be 3 more !

--
-- Feature 1: a DDL statement in a CTE will be executed to the database even if not referenced 
-- from the main query!
-- 
-- Feature 2: However all elements from the CTE are executed 'simultaneously' so results cannot be 
-- cross-used within the same CTE! 
--
-- A Note: the new_values select statement must be explicitly ordered, otherwise strange
-- things might happen!

yes - it's true that the clause will be executed regardless of RETURNING the thing that won't happen is however that no temp table will be formed .

Data-modifying statements in WITH usually have RETURNING clauses, as seen in the example above. It is the output of the RETURNING clause, not the target table of the data-modifying statement, that forms the temporary table that can be referred to by the rest of the query. If a data-modifying statement in WITH lacks a RETURNING clause, then it forms no temporary table and cannot be referred to in the rest of the query. Such a statement will be executed nonetheless. A not-particularly-useful example is: