PG之可寫的CTE

• 執行個體1

with naughty_users as (
  select * from users where banned = 1
)
select * from naughty_users;      

• 執行個體2

with deletions as (
  delete from users where expired is true
  returning *
)
insert into deleted_even_user_archive
select * from deletions where userid % 2 = 0;      

• 執行個體3

with userdata as (
  insert into users (name, email) values (?,?)
  returning userid
), addressdata as (
  insert into addresses (userid, address, city, state, zip)
  select userid,?,?,?,?
  from userdata
  returning addressid 
), historydata as (
  insert into user_history (userid, addressid, action)
  select userid, addressid,?
  from userdata, addressdata 
  returning historyid
)
select userid, addressid, historyid 
from userdata, addressdata, historydata;