반응형
/****************************************************************************************************************
-- 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;
 
반응형

+ Recent posts