[PGS9.2] Using RETURNING

/****************************************************************************************************************

-- title : [PGS9.2] Using RETURNING

-- reference : postgresql.org jakub.fedyczak.net

-- key word : postgresql returning

****************************************************************************************************************/

-- description

    o notice that RETURNING clause act like SELECT clause.

    o you know how many records changed AND IN what way.

 

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

INSERT INTO ttt

VALUES (10000, 'aaaaa')

     , (20000, 'bbbbb')

     , (30000, 'ccccc')

RETURNING *;

 

-- sample for UPDATE ~ returning

UPDATE ttt

SET b = 'fffff'

WHERE a > 10000

RETURNING a,b;

 

SELECT * FROM ttt

WHERE a > 10000;