Imparare Query ricorsiva PostgreSQL Per esempio

Sommario: in questo tutorial, imparerete a conoscere la query ricorsiva PostgreSQL utilizzando espressioni di tabella comuni ricorsive o CTES.

Introduzione alla query ricorsiva PostgreSQL

PostgreSQL fornisce l’istruzione WITH che consente di costruire istruzioni ausiliarie da utilizzare in una query.

Queste istruzioni sono spesso indicate come espressioni di tabella comuni o CTE. I CTE sono come tabelle temporanee che esistono solo durante l’esecuzione della query.

Una query ricorsiva è una query che fa riferimento a un CTE ricorsivo. Le query ricorsive sono utili in molte situazioni come l’interrogazione di dati gerarchici come struttura organizzativa, distinta base, ecc.

Quanto segue illustra la sintassi di un CTE ricorsivo:

Code language: SQL (Structured Query Language) (sql)

Un CTE ricorsivo ha tre elementi:

• Termine non ricorsivo: il termine non ricorsivo è una definizione di query CTE che costituisce il set di risultati di base della struttura CTE.
• Termine ricorsivo: il termine ricorsivo è una o più definizioni di query CTE unite al termine non ricorsivo utilizzando l’operatore UNION o UNION ALL. Il termine ricorsivo fa riferimento al nome CTE stesso.
• Controllo di terminazione: la ricorsione si interrompe quando non vengono restituite righe dall’iterazione precedente.

PostgreSQL esegue un CTE ricorsivo nella seguente sequenza:

1. Eseguire il termine non ricorsivo per creare il set di risultati di base (R0).
2. Esegui il termine ricorsivo con Ri come input per restituire il risultato impostato Ri+1 come output.
3. Ripetere il passaggio 2 finché non viene restituito un set vuoto. (controllo terminazione)
4. Restituisce il set di risultati finale che è un’UNIONE o UNION ALL del set di risultati R0, R1, R Rn

Esempio di query ricorsiva PostgreSQL

Creeremo una nuova tabella per dimostrare la query ricorsiva PostgreSQL.

Code language: SQL (Structured Query Language) (sql)

La tabella employees ha tre colonne: employee_id, manager_ide full_name. La colonna manager_id specifica l’ID manager di un dipendente.

La seguente istruzione inserisce i dati di esempio nella tabella employees.

Code language: PHP (php)

La seguente query restituisce tutti i subordinati del gestore con l’id 2.

Code language: SQL (Structured Query Language) (sql)

Come funziona:

• Il CTE ricorsivo, subordinato, definisce un termine non ricorsivo e un termine ricorsivo.
• Il termine non ricorsivo restituisce il set di risultati di base R0 che è il dipendente con l’id 2.

Code language: SQL (Structured Query Language) (sql)

Il termine ricorsivo restituisce il / i subordinato / i diretto / i dell’id dipendente 2. Questo è il risultato dell’unione tra la tabella dei dipendenti e i subordinati CTE. La prima iterazione del termine ricorsivo restituisce il seguente set di risultati:

Code language: SQL (Structured Query Language) (sql)

PostgreSQL esegue ripetutamente il termine ricorsivo. La seconda iterazione del membro ricorsivo utilizza il set di risultati sopra il passaggio come valore di input e restituisce questo set di risultati:

Code language: SQL (Structured Query Language) (sql)

La terza iterazione restituisce un set di risultati vuoto perché non vi è alcuna segnalazione del dipendente al dipendente con l’ID 16, 17, 18, 19 e 20.

PostgreSQL restituisce il set di risultati finale che è l’unione di tutti i set di risultati nella prima e nella seconda iterazioni generate dai termini non ricorsivi e ricorsivi.

In questo tutorial, hai imparato come usare i CTE ricorsivi per costruire le query ricorsive PostgreSQL.