[PGS9.2] how to get INSERTED or UPDATED ROWS

****************************************************************************************************************
-- Title : [PGS9.2] how to get INSERTED or UPDATED ROWS
-- Reference : dbrang.tistory.com
-- Key word : postgresql inserted updated rows returning 
****************************************************************************************************************/

-- initialization

DROP TABLE ttt;

-- creating table

CREATE unlogged TABLE ttt

( a int

, b varchar(10)

);

-- inserting data

WITH RECURSIVE tt(n)

as

( 

  VALUES (1)

  UNION all

  SELECT n + 1 FROM tt

  WHERE n < 1000

)

INSERT INTO ttt

SELECT n ,'aaaaaaaaaa' FROM tt;

SELECT * FROM ttt;

-- sample for INSERT ~ returning

with temp_set as

(

INSERT INTO ttt

VALUES (10000, 'aaaaa')

     , (20000, 'bbbbb')

     , (30000, 'ccccc')

RETURNING a, b

)

select *

from temp_set;

select * from ttt where a >= 10000;

-- sample for UPDATE ~ returning

with rows as

(

UPDATE ttt

SET b = 'fffff'

WHERE a > 10000

RETURNING a,b

) 

select count(*) "cnt"

from rows;

SELECT * FROM ttt

WHERE a > 10000;