Come creare una funzione PostgreSQL per restituire una tabella
Quando si dispone di un database PostgreSQL e si desidera controllare i record della tabella, utilizzare periodicamente una funzione è il modo più conveniente, in particolare una funzione PostgreSQL che restituisce una tabella nel suo set di risultati. In questo modo, incapsula il set di risultati e l'utilizzo di questo approccio aiuta a migliorare l'organizzazione del codice.
Quella che segue è la sintassi per creare una funzione PostgreSQL che restituisca una tabella:
CREA O SOSTITUISCI FUNZIONE nome_funzione(elenco_parametri)
TABELLA RESI(elenco_colonna)
COME $$
BEGIN RETURN QUERY(query);
FINE;
$$ LINGUA plpgsql
L'aspetto positivo della creazione di tali funzioni è che ti consente di specificare vari 'column_list' invece di restituire un singolo valore dalla tabella. Facciamo due esempi per aiutarci a capire quali passaggi seguire.
Esempio 1: lavorare con un singolo input
Quando crei una funzione che restituisce una tabella, devi fornire l'argomento da utilizzare con la query di ritorno. L'argomento può essere uno schema o un input specifico. Questo esempio fornisce un caso in cui utilizziamo un singolo input come argomento.
Quella che segue è la tabella “student” che utilizzeremo per la nostra query:
Nell'immagine seguente, creiamo una funzione denominata 'get_student' che accetta un INT come argomento. Nella sezione RETURNS TABLE, restituiamo una tabella con quattro colonne: 'student_id', 'student_name', 'student_faculty' e 'current_status'.
Tutte queste colonne ottengono i loro valori dalla query di ritorno che definiamo. Si noti che la query di ritorno utilizza un'istruzione WHERE utilizzando l'elenco di parametri specificato durante la creazione della funzione.
Una volta creata la funzione, otterrai un output simile a quello che avevamo in precedenza che conferma che la tua funzione PostgreSQL è stata creata con successo. Per verificarlo ulteriormente, esegui il comando seguente per elencare le funzioni disponibili:
\df *get_studente();Aggiungiamo gli asterischi per abbinare qualsiasi funzione che abbia il nome specificato. L'output mostra che abbiamo la nostra funzione PostgreSQL nel nostro database.
L'ultimo passaggio è testare la funzione creata. Eseguire l'istruzione 'select' per chiamare la funzione. Quindi, aggiungi l'argomento previsto. Nel nostro caso il parametro è di tipo INT. Pertanto, aggiungiamo 1 come argomento per recuperare i record che lo corrispondono e restituire una tabella come dimostrato di seguito:
Esempio 2: lavorare con un modello di input
Quando non sei sicuro del valore da utilizzare con la query di ritorno, puoi utilizzare l'operatore ILIKE per trovare una corrispondenza con un determinato modello. Ad esempio, se hai un nome e conosci solo una sezione della stringa, l'operatore ILIKE ti consente di utilizzare il simbolo '%' per definire come sarà il tuo modello.
Utilizziamo la tabella seguente per questo caso e prendiamo come target la colonna del nome:
Creiamo una funzione simile a quella che abbiamo fatto prima. Tuttavia, il tipo di parametro è cambiato e la query restituita utilizza l'operatore ILIKE che viene aggiunto come argomento quando si chiama la funzione.
Una volta che la funzione è pronta, possiamo chiamarla per restituire la tabella. Ci sono diversi modi per farlo. Ad esempio, se il modello di ricerca contiene 'Jo' nella stringa, eseguiamo la query del comando come segue:
Seleziona * da get_details('%Jo%');Abbiniamo tutti i valori con 'Jo' nella loro stringa, ottenendo due record.
Se conosciamo solo l'ultima parte di una stringa, giriamo la query e la eseguiamo come segue:
Seleziona * da get_details('%Tyson'); 
Infine, se conosciamo la prima parte della stringa, aggiungiamo il simbolo “&” dopo il pattern come mostrato di seguito:
Seleziona * da get_details('Tim%'); 
Questi sono i diversi esempi su come utilizzare la funzione PostgreSQL per restituire una tabella.
Conclusione
PostgreSQL è un potente database con numerose funzionalità. Quando crei funzioni, puoi impostarle in modo che restituiscano una tabella come set di risultati per diversi motivi, incluso il raggiungimento dell'incapsulamento. Questo post ha presentato due esempi di creazione e utilizzo di una funzione che restituisce una tabella in PostgreSQL.