[SQL] postgresql WITH, RECURSIVE, RETURNING

 WITH에 있는 작은 쿼리가 CTE( common table expression )가 되어 질의할 수 있다. 

CTE는 사전 정의가 필요가 없으며 쿼리가 끝나면 소멸되고 재귀 쿼리에 사용될 수 있으며 여러행이 반환 가능하다.

동일 문에서 여러번 참조가 가능하다.

 

 

WITH cte-name[ ( column-name, ... ) ] AS (

 

SELECT, INSERT, UPDATE, DELETE

 

)[ , cte-name2[ ( column-name, ... ) ] AS (

 

SELECT, INSERT, UPDATE, DELETE

 

) ]

SELET * FROM cte-name, cte-name2;

 

설명: cte_name과 cte-name2의 쿼리 질의 후, 두 테이블 정보를 마지막 쿼리에 사용할 수 있다.

 

 

WITH에 RECURSIVE를 붙여 재귀호출을 할 수 있다.

 

WITH RECURSIVE cte-name( n ) AS (

 

-- 처음 호출 쿼리

SELECT 1

 

UNION [ ALL ]

 

-- 재귀 호출 쿼리

SELECT n+1 FROM cte-name WHERE n <= 100

 

)

-- 부모 또는 외부 쿼리

SELECT * FROM cte-name;

 

결과는 column n에 1부터 101까지의 ROW를 볼 수 있다.

 

 

 

RETURNING

 

INSERT, UPDATE, DELETE를 실행 후 처리한 ROW들을 알려고 할 때 사용된다.

 

UPDATE table-name SET column = value RETURING *;

 

RETURING *을 하면 모든 컬럼들이 나오고 보고 싶은 컬럼이 있으면 컬럼명을 쓰면 된다.

 

 

ex )

 

WITH cte as (
     SELECT *,  RANK() over( order by point DESC ) as rnk
     FROM tb_rank
     WHERE id = _id
    )
    UPDATE tb_list
    SET ranking = rnk
    FROM cte
    WHERE tb_list.id = cte.id;

 

WITH cte as (
     SELECT *
     FROM tb_war  
     WHERE id = _id
    )
INSERT INTO tb_war_reward( id, ranking, grade, honor, datetime, reward)
SELECT id, ranking, grade, honorpoint, now() + interval '1 day', 0
from cte;

 

 

https://www.postgresql.org/docs/9.1/static/queries-with.html

https://blog.gaerae.com/2015/10/postgresql-insert-update-returning.html

https://spoqa.github.io/2013/08/21/cte.html

http://www.postgresdba.com/bbs/board.php?bo_table=B10&wr_id=49

http://i5on9i.blogspot.kr/2016/02/postgresql-with-recursive.html

http://www.postgresdba.com/bbs/board.php?bo_table=P01&wr_id=69

https://m.blog.naver.com/PostView.nhn?blogId=wiseyoun07&logNo=221135850258&targetKeyword=&targetRecommendationCode=1&proxyReferer=https%3A%2F%2Fwww.google.co.kr%2F

http://yeobi27.tistory.com/entry/PostgreSQL-WITH-%EB%AC%B8