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!

-- Feature 2

The sub-statements in WITH are executed concurrently with each other and with the main query. Therefore, when using data-modifying statements in WITH, the order in which the specified updates actually happen is unpredictable. All the statements are executed with the same snapshot (see Chapter 13), so they cannot "see" each others' effects on the target tables. This alleviates the effects of the unpredictability of the actual order of row updates, and means that RETURNING data is the only way to communicate changes between different WITH sub-statements and the main query.

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: