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!

if you want the cross ref in your select do a

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
      Returning *
  )

Select * from prices 
UNION ALL 
Select * from inserted

http://www.postgresql.org/docs/9.2/static/queries-with.html

Data-modifying statements in WITH are executed exactly once, and always to completion, independently of whether the primary query reads all (or indeed any) of their output. Notice that this is different from the rule for SELECT in WITH: as stated in the previous section, execution of a SELECT is carried only as far as the primary query demands its output.

So even if it has a RETURNING clause the CTE woudl be executed

-- 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: