CTE

Uma Common Table Expression (CTE) é um resultado temporário de uma consulta que pode ser utilizado dentro de uma consulta SQL principal. A CTE ajuda a simplificar consultas complexas, tornando-as mais legíveis e melhorando o desempenho em determinados cenários.

O que é uma CTE?

Uma CTE é um conjunto temporário de dados nomeado, definido com a palavra-chave WITH. Ela existe apenas dentro de uma única execução de consulta SQL e permite o uso repetido dos resultados intermediários dos cálculos. Isso é especialmente útil ao lidar com junções complexas e consultas recursivas.

Sintaxe da CTE

Uma CTE é declarada antes da consulta SQL principal utilizando `WITH`:

WITH temporary_table AS (

SELECT column1, column2

FROM some_table

WHERE condition

)

SELECT * FROM temporary_table;

Aqui, temporary_table é o nome temporário da CTE, que pode ser referenciado na instrução SELECT subsequente.

Vantagens da CTE

Simplificação do código – Permite dividir consultas SQL complexas em partes mais compreensíveis.
Reutilização de dados – A CTE pode ser chamada várias vezes na consulta principal, evitando a duplicação de código.
Otimização de consultas recursivas – A CTE facilita a execução de consultas recursivas, útil para manipulação de dados hierárquicos (como estruturas em árvore).

Exemplo de uso

Suponha que temos uma tabela employees, contendo informações sobre funcionários e seus gerentes. Para encontrar todos os subordinados de um gerente específico, podemos usar uma CTE recursiva:

WITH RECURSIVE subordinates AS (

SELECT id, name, manager_id

FROM employees

WHERE id = 1 -- ID do gerente

UNION ALL

SELECT e.id, e.name, e.manager_id

FROM employees e

INNER JOIN subordinates s ON e.manager_id = s.id

)

SELECT * FROM subordinates;

Essa consulta retorna todos os funcionários subordinados ao gerente com id = 1, incluindo subordinados indiretos.

Limitações da CTE

Escopo temporário – A CTE só existe durante a execução da consulta e não é armazenada na memória entre execuções.
Impacto no desempenho – Em alguns casos, o desempenho da CTE pode ser inferior ao de subconsultas ou tabelas temporárias, especialmente se o PostgreSQL não otimizar sua reutilização de forma eficiente.

Onde encontrar mais informações

Se você deseja aprofundar seus conhecimentos sobre PostgreSQL, a base de conhecimento da Serverspace oferece diversos materiais e guias práticos sobre administração, configuração e otimização de bancos de dados.