반응형
/****************************************************************************************************************
-- Title : [PGS9.2] Samples of WITH clause.
-- Reference : postgresql.org, some blogs(don't remember..ㅠㅠ)
-- Key word : postgresql with returning recursive cte bom
****************************************************************************************************************/
-- Initialization
DROP TABLE ttt;
DROP TABLE ttt2;
 
-- Sample.1
WITH RECURSIVE fff (n) 
AS 
( VALUES (1)
   UNION ALL
   SELECT n + 1 FROM fff WHERE n < 100
)
SELECT n FROM fff; 
 
 -- Sample.2
CREATE TABLE ttt
( id int, name varchar(10), age int, address varchar(50), salary int);
 
INSERT INTO ttt
VALUES (1, 'Paul' , 32, 'California', 20000)
     , (2, 'Allen', 25, 'Texas'     , 15000)
     , (3, 'Teddy', 23, 'Norway'    , 20000)
     , (4, 'Mark' , 25, 'Rich-Mond' , 65000)
     , (5, 'David', 27, 'Texas'     , 85000)
     , (6, 'Kim'  , 22, 'South-Hall', 45000)
     , (7, 'James', 24, 'Houston'   , 10000);
 
WITH CTE AS
(Select ID, NAME, AGE, ADDRESS, SALARY
 FROM ttt 
)
SELECT * FROM CTE;
 
-- Sample.3
WITH RECURSIVE ggg(n) 
AS 
(
    VALUES (0)
    UNION ALL
    SELECT SALARY FROM ttt WHERE SALARY < 20000
)
SELECT n FROM ggg;
 
-- Sample.4
-- move rows FROM ttt(delete) TO ttt2(insert) using returning keywoard.
CREATE TABLE ttt2
( id int, name varchar(10), age int, address varchar(50), salary int);
 
SELECT * FROM ttt;
 
WITH moved_rows 
AS 
(
    DELETE FROM ttt
    WHERE salary >= 30000
    RETURNING *
)
INSERT INTO ttt2 (SELECT * FROM moved_rows);
 
SELECT * FROM ttt;
SELECT * FROM ttt2;
 
 
반응형

+ Recent posts