Como trabalhar com CTEs (expressões de tabela comuns)¶

Potencial para loops infinitos¶

A construção de uma CTE recursiva de forma incorreta pode causar um loop infinito. Nesses casos, a consulta continua a ser executada até que tenha sucesso, até que o tempo limite ocorra (por exemplo, ultrapassa o número de segundos especificado pelo parâmetro STATEMENT_TIMEOUT_IN_SECONDS) ou até que você cancele a consulta.

Para obter mais informações sobre como podem ocorrer loops infinitos e para diretrizes sobre como evitar este problema, consulte Solução de problemas de uma CTE recursiva.

Hierarquias não contíguas¶

Este tópico descreveu hierarquias e como as relações pai-filho podem ser usadas por CTEs recursivas. Em todos os exemplos deste tópico, as hierarquias são contíguas.

Para obter mais informações sobre hierarquias não contíguas, consulte Consulta de dados hierárquicos.

Saída não ordenada¶

Aqui está o primeiro exemplo.

 1)    WITH RECURSIVE managers
 2)          (indent, employee_ID, manager_ID, employee_title)
 3)        AS
 4)          (
 5)
 6)            SELECT '' AS indent, employee_ID, manager_ID, title AS employee_title
 7)              FROM employees
 8)              WHERE title = 'President'
 9)
10)            UNION ALL
11)
12)           SELECT indent || '--- ',
13)               employees.employee_ID, employees.manager_ID, employees.title
14)             FROM employees JOIN managers
15)               ON employees.manager_ID = managers.employee_ID
16)         )
17)
18)     SELECT indent || employee_title AS Title, employee_ID, manager_ID
19)       FROM managers
20)     ;

A consulta inclui as seguintes seções:

• A linha 2 contém os nomes das colunas para a “exibição” (CTE).

• As linhas 4 - 16 contêm a CTE.

• As linhas 6 - 8 contêm a cláusula de âncora da CTE.

• As linhas 12 - 15 contêm a cláusula recursiva da CTE.

• As linhas 18 - 19 contêm o SELECT principal que usa a CTE como uma exibição. Esta SELECT faz referência a:

  • O nome da CTE (managers), definido na linha 1.

  • As colunas da CTE (indent, employee_id, etc.) definidas na linha 2.

A CTE contém duas instruções SELECT:

• A instrução SELECT na cláusula de âncora é executada uma vez e fornece o conjunto de linhas a partir do primeiro nível (superior) da hierarquia.

• A SELECT na cláusula recursiva pode fazer referência à CTE. Você pode pensar na consulta como iteração, com cada iteração expandindo os resultados da consulta das iterações anteriores.

No exemplo do gerente/funcionário, a cláusula de âncora emite a primeira linha, que descreve o presidente da empresa.

Na próxima iteração da cláusula recursiva, a cláusula recursiva encontra todas as linhas cujo gerente é o presidente da empresa (ou seja, encontra todos os vice-presidentes). A terceira iteração encontra todos os funcionários cujo gerente é um dos vice-presidentes. A iteração continua até que haja uma iteração na qual todas as linhas recuperadas são linhas de funcionários de nível folha que não gerenciam ninguém. A instrução faz mais uma iteração, procurando (mas não encontrando) quaisquer funcionários cujos gerentes sejam funcionários de nível folha. A iteração produz 0 linhas e para.

Ao longo destas iterações, a cláusula UNION ALL acumula os resultados. Os resultados de cada iteração são adicionados aos resultados das iterações anteriores. Após a conclusão da última iteração, as linhas acumuladas (como qualquer linha produzida em uma cláusula WITH) são disponibilizadas para a cláusula SELECT principal da consulta. A SELECT principal pode então consultar essas linhas.

Este exemplo particular de consulta usa recuos para mostrar a natureza hierárquica dos dados. Se você olhar para a saída, verá que quanto mais baixo o nível do funcionário, mais os dados desse funcionário são recuados.

O recuo é controlado pela coluna chamada indent. O recuo começa com 0 caracteres (uma cadeia de caracteres vazia na cláusula de âncora), e aumenta em 4 caracteres (---) para cada iteração (ou seja, para cada nível da hierarquia).

Não surpreendentemente, é muito importante construir as junções corretamente e selecionar as colunas corretas na cláusula recursiva. As colunas em SELECT da cláusula recursiva devem corresponder corretamente às colunas da cláusula de âncora. Lembre-se de que a consulta começa com o presidente, depois seleciona os vice-presidentes e depois seleciona as pessoas que se reportam diretamente aos vice-presidentes, etc. Cada iteração procura funcionários cujo campo manager_id corresponda a um dos valores managers.employee_id produzidos na iteração anterior.

Expressado de outra forma, a ID do funcionário na “exibição” de gerentes é a ID de gerente para o próximo nível de funcionários. As IDs dos funcionários devem progredir para baixo através da hierarquia (presidente, vice-presidente, gerente sênior, gerente júnior, etc.) durante cada iteração. Se as IDs de funcionário não progredirem, então a consulta pode fazer loop infinito (se o mesmo manager_ID continuar aparecendo na coluna managers.employee_ID em diferentes iterações), ou pular um nível, ou falhar de outras maneiras.

Saída ordenada¶

O exemplo anterior não tinha cláusula ORDER BY, portanto, mesmo que o registro de cada funcionário esteja recuado corretamente, cada funcionário não necessariamente aparecia diretamente abaixo de seu gerente. O exemplo abaixo gera saída com recuo correto, e com os funcionários de cada gerente diretamente abaixo de seu próprio gerente.

A cláusula ORDER BY da consulta usa uma coluna adicional, chamada sort_key. A chave de ordenação se acumula à medida que a cláusula recursiva itera; você pode pensar na chave de ordenação como uma sequência que contém toda a cadeia de comando acima de você (seu gerente, o gerente de seu gerente, etc.). A pessoa mais graduada nessa cadeia de comando (o presidente) está no início da cadeia de caracteres da chave de ordenação. Embora você normalmente não mostre a chave de ordenação, a consulta abaixo inclui a chave de ordenação na saída, para que seja mais fácil entender a saída.

Cada iteração deve aumentar o comprimento da chave de ordenação na mesma quantidade (mesmo número de caracteres), assim a consulta usa uma UDF (função definida pelo usuário) chamada skey, com a seguinte definição, para gerar segmentos de comprimento consistente da chave de ordenação:

CREATE OR REPLACE FUNCTION skey(ID VARCHAR)
  RETURNS VARCHAR
  AS
  $$
    SUBSTRING('0000' || ID::VARCHAR, -4) || ' '
  $$
  ;

Copy

Aqui está um exemplo de saída da função SKEY:

SELECT skey(12);
+----------+
| SKEY(12) |
|----------|
| 0012     |
+----------+

Copy

Aqui está a versão final da consulta. Isto coloca os funcionários de cada gerente imediatamente abaixo desse gerente e insere recuos com base no “nível” do funcionário:

WITH RECURSIVE managers 
      -- Column list of the "view"
      (indent, employee_ID, manager_ID, employee_title, sort_key) 
    AS 
      -- Common Table Expression
      (
        -- Anchor Clause
        SELECT '' AS indent, 
            employee_ID, manager_ID, title AS employee_title, skey(employee_ID)
          FROM employees
          WHERE title = 'President'

        UNION ALL

        -- Recursive Clause
        SELECT indent || '--- ',
            employees.employee_ID, employees.manager_ID, employees.title, 
            sort_key || skey(employees.employee_ID)
          FROM employees JOIN managers 
            ON employees.manager_ID = managers.employee_ID
      )

  -- This is the "main select".
  SELECT 
         indent || employee_title AS Title, employee_ID, 
         manager_ID, 
         sort_key
    FROM managers
    ORDER BY sort_key
  ;
+----------------------------------+-------------+------------+-----------------+
| TITLE                            | EMPLOYEE_ID | MANAGER_ID | SORT_KEY        |
|----------------------------------+-------------+------------+-----------------|
| President                        |           1 |       NULL | 0001            |
| --- Vice President Engineering   |          10 |          1 | 0001 0010       |
| --- --- Programmer               |         100 |         10 | 0001 0010 0100  |
| --- --- QA Engineer              |         101 |         10 | 0001 0010 0101  |
| --- Vice President HR            |          20 |          1 | 0001 0020       |
| --- --- Health Insurance Analyst |         200 |         20 | 0001 0020 0200  |
+----------------------------------+-------------+------------+-----------------+

Copy

A consulta seguinte mostra como fazer referência a um campo do nível anterior (superior) na hierarquia; preste especial atenção à coluna mgr_title:

WITH RECURSIVE managers 
      -- Column names for the "view"/CTE
      (employee_ID, manager_ID, employee_title, mgr_title) 
    AS
      -- Common Table Expression
      (

        -- Anchor Clause
        SELECT employee_ID, manager_ID, title AS employee_title, NULL AS mgr_title
          FROM employees
          WHERE title = 'President'

        UNION ALL

        -- Recursive Clause
        SELECT 
            employees.employee_ID, employees.manager_ID, employees.title, managers.employee_title AS mgr_title
          FROM employees JOIN managers 
            ON employees.manager_ID = managers.employee_ID
      )

  -- This is the "main select".
  SELECT employee_title AS Title, employee_ID, manager_ID, mgr_title
    FROM managers
    ORDER BY manager_id NULLS FIRST, employee_ID
  ;
+----------------------------+-------------+------------+----------------------------+
| TITLE                      | EMPLOYEE_ID | MANAGER_ID | MGR_TITLE                  |
|----------------------------+-------------+------------+----------------------------|
| President                  |           1 |       NULL | NULL                       |
| Vice President Engineering |          10 |          1 | President                  |
| Vice President HR          |          20 |          1 | President                  |
| Programmer                 |         100 |         10 | Vice President Engineering |
| QA Engineer                |         101 |         10 | Vice President Engineering |
| Health Insurance Analyst   |         200 |         20 | Vice President HR          |
+----------------------------+-------------+------------+----------------------------+

Copy

Causa 1: Hierarquia de dados cíclica¶

Se sua hierarquia de dados contém um ciclo (ou seja, não é uma árvore verdadeira), há múltiplas soluções possíveis:

Solução 1.1

Se os dados não devem conter um ciclo, corrija os dados.

Solução 1.2

Limite a consulta de alguma forma (por exemplo, limitar o número de linhas de saída). Por exemplo:

WITH RECURSIVE t(n) AS
    (
    SELECT 1
    UNION ALL
    SELECT N + 1 FROM t
   )
 SELECT n FROM t LIMIT 10;

Copy

Solução 1.3

Não use uma consulta que contenha uma CTE recursiva, que espera dados hierárquicos.

Causa 2: Loop infinito¶

Um loop infinito pode acontecer se a cláusula de projeção na recursive_clause produzir um valor do “pai” (a iteração anterior) ao invés do “filho” (a iteração atual) e então a próxima iteração usar esse valor em uma junção quando deveria usar o valor da iteração atual na junção.

O seguinte pseudocódigo mostra um exemplo aproximado disso:

CREATE TABLE employees (employee_ID INT, manager_ID INT, ...);
INSERT INTO employees (employee_ID, manager_ID) VALUES
        (1, NULL),
        (2, 1);

WITH cte_name (employee_ID, manager_ID, ...) AS
  (
     -- Anchor Clause
     SELECT employee_ID, manager_ID FROM table1
     UNION ALL
     SELECT manager_ID, employee_ID   -- <<< WRONG
         FROM table1 JOIN cte_name
           ON table1.manager_ID = cte_name.employee_ID
  )
SELECT ...

Copy

Neste exemplo, a cláusula recursiva passa seu valor pai (manager_id) na coluna que deve ter o valor atual/filho (employee_id). O pai aparecerá como o valor “atual” na próxima iteração, e será passado novamente como o valor “atual” para a geração seguinte, de modo que a consulta nunca avança através dos níveis; ela continua processando o mesmo nível a cada vez.

Etapa 1

Suponha que a cláusula de âncora selecione os valores employee_id = 1 e manager_id = NULL.

CTE:

employee_ID  manager_ID
-----------  ---------
      1         NULL

Copy

Etapa 2

Durante a primeira iteração da cláusula recursiva, employee_id = 2 e manager_id = 1 em table1.

CTE:

employee_ID  manager_ID
-----------  ----------
       1         NULL

Copy

table1:

employee_ID  manager_ID
-----------  ----------
 ...
       2         1
 ...

Copy

Resultado da junção na cláusula recursiva:

table1.employee_ID  table1.manager_ID  cte.employee_ID  cte.manager_ID
-----------------   -----------------  ---------------  --------------
 ...
       2                   1                 1                NULL
 ...

Copy

Projeção:

employee_ID  manager_ID
-----------  ----------
 ...
       2         1
 ...

Copy

Entretanto, como as colunas employee_id e manager_id são invertidas na projeção, a saída real da consulta (e portanto o conteúdo da CTE no início da próxima iteração) é:

employee_ID  manager_ID
-----------  ----------
 ...
       1         2        -- Because manager and employee IDs reversed
 ...

Copy

Etapa 3

Durante a segunda iteração da cláusula recursiva:

CTE:

employee_ID  manager_ID
-----------  ----------
       1         2

Copy

table1:

employee_ID  manager_ID
-----------  ----------
 ...
       2         1
 ...

Copy

Resultado da junção em cláusula recursiva:

table1.employee_ID  table1.manager_ID  cte.employee_ID  cte.manager_ID
-----------------   -----------------  ---------------  --------------
 ...
       2                   1                 1                2
 ...

Copy

Projeção:

employee_ID  manager_ID
-----------  ----------
 ...
       2         1
 ...

Copy

Resultado da consulta (conteúdo da CTE no início da próxima iteração):

employee_ID  manager_ID
-----------  ----------
 ...
       1         2        -- Because manager and employee IDs reversed
 ...

Copy

Como você pode ver, no final da segunda iteração, a linha na CTE é a mesma que era no início da iteração:

• employee_id é 1.

• manager_id é 2.

Assim, o resultado da junção durante a próxima iteração será o mesmo que o resultado da junção durante a iteração atual, e a consulta faz um loop infinito.

Se você criou um loop infinito:

Solução 2

Certifique-se de que a cláusula recursiva passe as variáveis corretas na ordem correta.

Certifique-se também de que a condição JOIN na cláusula recursiva esteja correta. Em um caso típico, o pai da linha “atual” deve ser unido ao valor filho/atual da linha pai.