기본 값selectinwith복잡한 사설 토토를 더 간단한 부품으로 분해하는 것입니다. 예는 다음과 같습니다.

greal_sales as (
        Total_sales로 영역, 합 (금액)을 선택하십시오
        주문에서
        지역별 그룹
     ), top_regions as (
        지역을 선택하십시오
        regional_sales에서
        여기서 total_sales (select sum (total_sales)/regional_sales에서 10)
     ))
지역 선택,
       제품,
       sum (수량)으로 product_units,
       sum (금액)은 product_sales입니다
주문에서
지역 (Top_regions에서 지역 선택)
지역별 그룹, 제품;

상위 판매 지역에서만 제품 별 판매량을 표시합니다. 그만큼with조항은 두 개의 보조 문서를 정의합니다.Regional_salesandTOP_REGIONSRegional_salesTOP_REGIONS의 출력TOP_REGIONS기본에서 사용됩니다select사설 토토. 이 예는없이 쓰여졌을 수 있습니다.with, 그러나 우리는 두 가지 레벨의 중첩 하위가 필요했을 것입니다selects. 이런 식으로 따르는 것이 조금 더 쉽습니다.

선택 사항재귀수정 자 변경with단순한 구문 편의에서 표준 SQL에서 불가능한 것을 달성하는 기능으로. 사용재귀, awith사설 토토는 자체 출력을 참조 할 수 있습니다. 매우 간단한 예는 1 ~ 100의 정수를 합산하는이 사설 토토입니다.

재귀 t (n) as (
    값 (1)
  모두
    n <100에서 n+1을 선택하십시오
))
t;에서 합 (n)을 선택하십시오.

재귀의 일반적인 형태with사설 토토는 항상입니다.비 수거 용어,Union(또는Union All), a재귀 용어, 여기서 재귀 용어 만 쿼리의 자체 출력에 대한 참조를 포함 할 수 있습니다. 이러한 쿼리는 다음과 같이 실행됩니다.

위의 예에서 작업 테이블은 각 단계에서 단일 행을 가지며 연속 단계에서 1 ~ 100의 값을 취합니다. 100 번째 단계에서는 때문에 출력이 없습니다.여기서절, 따라서 사설 토토가 종료됩니다.

재귀 쿼리는 일반적으로 계층 또는 트리 구조화 된 데이터를 다루는 데 사용됩니다. 유용한 예는 즉각적인 포함을 보여주는 테이블 만 주어진 제품의 모든 직접적이고 간접적 인 하위 부분을 찾는이 쿼리입니다.

재귀 포함 포함 _parts (sub_part, part, rotity) as (
    sub_part, part, part에서 part = 'our_product'를 선택하십시오.
  모두
    p.sub_part, p.part, p.quantity * pr.quantity를 선택하십시오
    포함 _parts pr, parts p
    여기서 p.part = pr.sub_part
  ))
sub_part, sum (수량)을 total_quantity로 선택하십시오
conlude_parts에서
sub_part의 그룹

재귀 쿼리로 작업 할 때 쿼리의 재귀 부분이 결국 튜플을 반환하지 않으면 쿼리가 무기한으로 반복되는지 확인하는 것이 중요합니다. 때로는 사용Union대신Union All이전 출력 행을 복제하는 행을 폐기하여이를 달성 할 수 있습니다. 그러나 종종 사이클에는 완전히 복제 된 출력 행이 포함되지 않습니다. 이전에 동일한 지점에 도달했는지 확인하려면 하나 또는 몇 개의 필드 만 확인해야 할 수도 있습니다. 이러한 상황을 처리하는 표준 방법은 이미 방문한 값의 배열을 계산하는 것입니다. 예를 들어, 테이블을 검색하는 다음 쿼리를 고려하십시오그래프a 사용링크필드 :

recursive search_graph (id, link, data, depth) as (
        G.ID, G.Link, G.Data, 1을 선택하십시오
        그래프 g에서
      모두
        G.ID, G.Link, G.Data, Sg.Depth + 1을 선택하십시오
        그래프 G, search_graph sg에서
        여기서 g.id = sg.link
))
sexect *에서 search_graph;

이 사설 토토는이면 루프됩니다.링크관계에는주기가 포함됩니다. 우리가 필요하기 때문에“깊이”출력, 그냥 변경Union AlltoUnion루핑을 제거하지 않습니다. 대신 우리는 특정 링크 경로를 따르면서 같은 행에 다시 도달했는지 여부를 인식해야합니다. 두 개의 열을 추가합니다Pathand사이클루프가 발생하기 쉬운 사설 토토 :

Recursive search_graph (id, 링크, 데이터, 깊이, 경로, 사이클)
        G.ID, G.Link, G.Data, 1, 1,
          배열 [g.id],
          거짓
        그래프 g에서
      모두
        G.ID, G.Link, G.Data, Sg.Depth + 1,
          경로 || g.id,
          g.id = 모든 (경로)
        그래프 G, search_graph sg에서
        여기서 g.id = sg.link 및 순환이 아닙니다
))
sexect *에서 search_graph;

사이클을 방지하는 것 외에도 배열 값은 종종 자체적으로 유용합니다.“Path”특정 행에 도달하기 위해 촬영.

주기를 인식하기 위해 둘 이상의 필드를 점검 해야하는 일반적인 경우 행을 사용하십시오. 예를 들어, 필드를 비교 해야하는 경우F1andF2:

recursive search_graph (id, 링크, 데이터, 깊이, 경로, 사이클)
        G.ID, G.Link, G.Data, 1, 1,
          배열 [행 (G.F1, G.F2)],
          거짓
        그래프 g에서
      모두
        G.ID, G.Link, G.Data, Sg.Depth + 1,
          경로 || 행 (G.F1, G.F2),
          행 (G.F1, G.F2) = 모든 (경로)
        그래프 G, search_graph sg에서
        여기서 g.id = sg.link 및 순환이 아닙니다
))
sexect *에서 search_graph;

루프가 a에 있는지 확실하지 않은 경우 사설 토토 테스트에 유용한 트릭Limit부모 사설 토토에서. 예를 들어,이 사설 토토는없이 영원히 루프합니다.Limit:

재귀 t (n) as (
    선택 1
  모두
    t에서 n+1을 선택하십시오
))
t 한계 100에서 n을 선택하십시오;

이것은 작동하기 때문에PostgreSQL의 구현은 A의 많은 행만 평가합니다.with쿼리는 실제로 상위 쿼리에 의해 가져옵니다. 다른 시스템이 다르게 작동 할 수 있으므로이 트릭을 생산에 사용하는 것은 권장되지 않습니다. 또한 외부 쿼리가 재귀 쿼리의 결과를 정렬하거나 다른 테이블에 결합하면 일반적으로 작동하지 않습니다.이 경우 외부 쿼리는 일반적으로 모든를 가져 오려고합니다.with어쨌든 쿼리의 출력.

유용한 속성with쿼리는 부모 쿼리 또는 형제 자매가 한 번 이상 참조하더라도 부모 쿼리의 실행 당 한 번만 평가된다는 것입니다.with사설 토토. 따라서 여러 장소에서 필요한 비싼 계산은 A 내에 배치 할 수 있습니다.with중복 작업을 피하기위한 사설 토토. 또 다른 가능한 응용 프로그램은 부작용과 함께 함수의 원치 않는 다중 평가를 방지하는 것입니다. 그러나이 코인의 다른 쪽은 Optimizer가 상위 사설 토토의 제한을 A로 푸시 할 수 없다는 것입니다.with일반 하위 퀘스트보다 사설 토토. 그만큼with사설 토토는 일반적으로 상위 사설 토토가 나중에 버릴 수있는 행을 억제하지 않고 서면으로 평가됩니다. (그러나 위에서 언급했듯이 사설 토토에 대한 참조가 제한된 수의 행만 요구하면 평가가 일찍 중지 될 수 있습니다.)

위의 예제 만 표시withselect, 그러나 같은 방식으로 첨부 할 수 있습니다삽입, 업데이트또는삭제. 각각의 경우에는 메인 명령에서 참조 할 수있는 임시 테이블을 효과적으로 제공합니다.

데이터 수정 문을 사용할 수 있습니다 (삽입, 업데이트또는삭제) inwith. 이를 통해 동일한 사설 토토에서 여러 다른 작업을 수행 할 수 있습니다. 예는 다음과 같습니다.

mized_rows as (
    제품에서 삭제하십시오
    어디
        "날짜" = '2010-10-01'및
        "날짜"< '2010-11-01'
    반환 *
))
Products_Log에 삽입하십시오
maze_rows에서 *를 선택하십시오;

이 사설 토토는 효과적으로 행을 움직입니다제품toProducts_Log. 그만큼삭제inwith지정된 행을 삭제합니다제품반환절; 그리고 기본 쿼리는 출력을 읽고을 삽입합니다.Products_Log.

위의 예의 미세점은입니다.with절에 첨부되어삽입, 서브가 아닙니다.select내에서삽입. 데이터 수정 진술은에만 허용되기 때문에 필요합니다.with최상위 성명서에 첨부 된 조항. 그러나 정상with가시성 규칙이 적용되므로를 참조 할 수 있습니다.withsub-의 명령문 출력select.

데이터 수정 문의with보통반환조항 (참조섹션 6.424107_24167반환절,not데이터 수정 명령문의 대상 테이블은 나머지 사설 토토에서 참조 할 수있는 임시 테이블을 형성합니다. 데이터 수정 명세서에서with부족반환절은 임시 표를 형성하지 않으며 나머지 사설 토토에서는 참조 할 수 없습니다. 그럼에도 불구하고 그러한 진술은 실행됩니다. 적절하지 않은 예는 다음과 같습니다.

t as (
    foo에서 삭제하십시오
))
바에서 삭제;

이 예제는 테이블에서 모든 행을 제거합니다fooandbar. 클라이언트에보고 된 영향을받는 행의 수에는에서만 제거 된 행만 포함됩니다.bar.

데이터 수정 진술의 재귀 적 자기 참조는 허용되지 않습니다. 경우에 따라 재귀의 출력을 참조 하여이 제한을 해결할 수 있습니다with, 예 :

재귀 포함 _parts (sub_part, part) as (
    sub_part, part = 'our_product'부분에서 부품을 선택하십시오.
  모두
    p.sub_part, p.part를 선택하십시오
    포함 _parts pr, parts p
    여기서 p.part = pr.sub_part
  ))
부품에서 삭제하십시오
  PART (conture_Parts에서 부품을 선택);

이 쿼리는 제품의 모든 직접적이고 간접적 인 하위 부분을 제거합니다.

데이터 수정 문의with는 1 차 쿼리가 출력의 모든 (또는 실제로)를 읽는지 여부와 독립적으로 한 번 정확하게 한 번 실행되며 항상 완료됩니다. 이것은 규칙과 다릅니다selectinwith: 이전 섹션에서 언급 한 바와 같이 aselect기본 사설 토토가 출력을 요구하는 한만 운반됩니다.

하위 진술의with는 서로 동시에 그리고 기본 사설 토토와 함께 실행됩니다. 따라서 데이터 수정 문을 사용할 때with, 지정된 업데이트가 실제로 발생하는 순서는 예측할 수 없습니다. 모든 진술은 동일하게 실행됩니다스냅 샷(참조PostgreSQL : 문서 : 10 : 13 장. 무지개 토토 제어), 따라서“참조”대상 테이블에 대한 서로의 영향. 이것은 실제 행 업데이트의 실제 순서의 예측 불가능 성의 영향을 완화하며반환데이터는 다른 변화를 전달하는 유일한 방법입니다with하위 진술 및 기본 사설 토토. 이것의 예는에서입니다.

t as (
    업데이트 제품 세트 가격 = 가격 * 1.05
    반환 *
))
선택 *에서 제품에서;

외부select|업데이트

t as (
    업데이트 제품 세트 가격 = 가격 * 1.05
    반환 *
))
선택 *에서 t; 선택

외부select업데이트 된 데이터를 반환합니다.

단일 문서에서 같은 행을 두 번 업데이트하려는 것은 지원되지 않습니다. 수정 중 하나만 발생하지만 어느 것을 쉽게 예측하는 것은 쉽지 않으며 때로는 불가능합니다. 이는 동일한 문서에서 이미 업데이트 된 행을 삭제하는데도 적용됩니다. 업데이트 만 수행됩니다. 따라서 일반적으로 단일 문서에서 단일 행을 두 번 수정하지 않아야합니다. 특히 글쓰기를 피하십시오with메인 문 또는 형제 자매 하위 진술에 의해 변경된 동일한 행에 영향을 줄 수있는 하위 진술. 그러한 진술의 효과는 예측할 수 없습니다.

현재, 데이터 수정 명령문의 대상으로 사용 된 모든 테이블with조건부 규칙도 없어야합니다.또한규칙, 또는대신여러 진술로 확장하는 규칙.