PGSQL Toate comenzile și funcțiile PostgreSQL

`WITH (Common Table Expressions)` în PostgreSQL

CTE și Window Functions PostgreSQL 8.4+ (MATERIALIZED din PostgreSQL 12+)

Definește subinterogări numite (CTE) pentru structurarea clară a interogărilor complexe și înlănțuirea operațiilor DML.

Sintaxă SQL

WITH nume_cte AS [MATERIALIZED | NOT MATERIALIZED] (SELECT ...) SELECT * FROM nume_cte;

Ce face `WITH (Common Table Expressions)`

Clauza WITH (cunoscută ca CTE — Common Table Expression) îți permite să definești una sau mai multe tabele temporare valabile strict pe durata unei singure interogări. CTE-urile înlocuiesc subinterogările imbricate adânc, făcând codul SQL lizibil și modular.

În PostgreSQL, un CTE poate conține nu doar un SELECT, ci și instrucțiuni de modificare (INSERT ... RETURNING, UPDATE ... RETURNING, DELETE ... RETURNING), ceea ce permite mutarea datelor între tabele într-o singură interogare atomică. De asemenea, începând cu PostgreSQL 12, optimizatorul poate integra (inline) automat CTE-urile, sau îi poți controla comportamentul cu MATERIALIZED / NOT MATERIALIZED.

Clauze, operatori și argumente

Clauză / Operator	Descriere
`WITH cte1 AS (...), cte2 AS (...)`	Definește una sau mai multe expresii de tabel; `cte2` poate face referire la `cte1`.
`MATERIALIZED`	Forțează PostgreSQL să evalueze CTE-ul o singură dată și să stocheze rezultatul într-un buffer temporar.
`NOT MATERIALIZED`	Cere optimizatorului să integreze CTE-ul în interogarea principală și să împingă filtrele `WHERE` și indecșii înăuntru.
`WITH mutare AS (DELETE ... RETURNING *)`	CTE modificator de date: rândurile returnate de `RETURNING` pot fi inserate sau interogate în comanda principală.

Exemple

Exemplul 1: Analiza vânzărilor pe categorii peste media generală cu două CTE-uri

Primul CTE calculează vânzările fiecărei categorii, iar interogarea finală le păstrează doar pe cele care depășesc media tuturor categoriilor.

WITH vanzari_categorii AS (
    SELECT categorie,
           SUM(cantitate * pret_unitar) AS total_vanzari
    FROM linii_comanda
    GROUP BY categorie
),
media_globala AS (
    SELECT ROUND(AVG(total_vanzari), 2) AS prag_mediu
    FROM vanzari_categorii
)
SELECT vc.categorie,
       vc.total_vanzari,
       mg.prag_mediu
FROM vanzari_categorii vc
CROSS JOIN media_globala mg
WHERE vc.total_vanzari > mg.prag_mediu
ORDER BY vc.total_vanzari DESC;

Rezultat interogare

 categorie | total_vanzari | prag_mediu 
-----------+---------------+------------
 Laptopuri |      84500.00 |   41200.00
 Monitoare |      52300.00 |   41200.00
(2 rows)

Exemplul 2: Mutarea atomică a rândurilor între tabele cu DELETE … RETURNING într-un CTE

Ștergem logurile mai vechi de 90 de zile din tabelul activ și le inserăm simultan în tabelul de arhivă.

WITH loguri_sterse AS (
    DELETE FROM audit_logs
    WHERE creat_la < NOW() - INTERVAL '90 days'
    RETURNING id, actiune, utilizator_id, creat_la
)
INSERT INTO audit_logs_arhiva (id, actiune, utilizator_id, creat_la)
SELECT id, actiune, utilizator_id, creat_la
FROM loguri_sterse
RETURNING id, actiune;