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



반응형

+ Recent posts