PostgreSQL - Funzioni

Come nella maggior parte dei database, possiamo concatenare una serie di frasi SQL e trattarli come unità di un blocco funzionale; i diversi database descrivono questa operazione con nomi diversi, stored procedure, moduli, macro, ecc.

Nel PostgreSQL si chiamano funzioni. Oltre a unificare diverse istruzioni SQL, queste funzioni ci danno anche la possibilità di eseguire istruzioni SQL utilizzando linguaggi procedurali (PL).

Nel PostgreSQL abbiamo più opzioni per questo e la possibilità di espanderle.

1. Funzioni PostgreSQL

Anatomia di una funzione
Indipendentemente dal linguaggio utilizzato per scrivere le funzioni, hanno una struttura, questa struttura può essere sintetizzata con quanto segue:

 CREATE OR REPLACE FUNCTION nome_funzione (arg1_arg1datatype) RETURNS some_type / setoff sometype / TABLE / (…) / AS $$ BODY off funzione $$ LANGUAGE language_of_function

Se descriviamo ciò che vediamo è abbastanza semplice, FUNZIONE CREA O SOSTITUISCI è la clausola di creazione della funzione, func_name è il nome che avrà arg1 è il parametro che riceverà e arg1_datatype è il tipo di dati che è detto parametro, cioè se è un numero intero, una stringa, ecc. Nel RITORNA Restituiamo il risultato della nostra funzione, $$ è l'inizio del blocco che darà il via al corpo della funzione e poi finisce lo stesso con $$ e infine LINGUAGGIO permette di specificare la lingua in cui è scritta la funzione.

È una struttura ben definita e abbastanza leggibile quindi non dovremmo avere problemi a creare le nostre funzioni.

Scrivere funzioni con SQL
Scrivi le funzioni usando SQL È abbastanza facile e veloce, è semplicemente prendere le nostre istruzioni SQL e aggiungere l'intestazione e il piè di pagina delle funzioni e abbiamo finito.

Tuttavia, come tutto il resto, questo avviene a costo di alcuni sacrifici, ad esempio perdiamo la flessibilità che se potessimo lavorare con un altro linguaggio per creare più rami di controllo dell'esecuzione condizionale, non possiamo avere più di un'istruzione SQL, sebbene ciò possa essere risolto utilizzando vari metodi.

Il più grande vantaggio è che poiché SQL è lo scheduler PostgreSQL ci permette di sfruttare gli indici e quindi velocizzarne l'esecuzione, invece con altri linguaggi la funzione sarà sempre una scatola nera

Ora diamo un'occhiata a una funzione scritta con SQL:

 CREARE O SOSTITUIRE FUNZIONE ins_logs (param_user_name varchar, param_description text) RETURNS integer AS $$ INSERT INTO logs (user_name, description) VALUES ($ 1, $ 2) RETURNING log_id; $$ LINGUA 'sql' VOLATILE;

Vediamo che seguiamo la struttura definita sopra e alla fine nella sezione LINGUAGGIO definiamo la clausola 'sql' VOLATILE Il fatto che possieda significa che la funzione può restituire qualcosa di diverso ad ogni chiamata effettuata, anche se riceve gli stessi parametri. Quindi per chiamare la nostra funzione possiamo usare:

 SELECT ins_logs ('lhsu', 'questo è un test') As new_id;

Facciamo una frase SELEZIONARE, la funzione è attiva e ciò che restituisce è ciò che riceveremo e in questo caso vedremo un valore che chiamiamo new_id e che la funzione restituisce come log_id.
Possiamo anche utilizzare una funzione per eseguire un aggiornamento del record e restituire un parametro void come in questo esempio:

 CREARE O SOSTITUIRE FUNZIONE upd_logs (log_id intero, param_user_name varchar, param_description text) RETURNS void AS $$ UPDATE logs SET user_name = $ 2, description = $ 3, log_ts = CURRENT_TIMESTAMPWHERE log_id = $ 1; $$ LANGUAGE 'sql's 'sql's 'VOLATILE'

Essendo void non abbiamo bisogno di un campo ricevitore quindi lo eseguiamo come segue:

 SELECT upd_logs (12, 'robe', 'Cambia in regina');

Qui possiamo vedere che abbiamo rimosso l'ultimo passaggio di As new_id dalla chiamata precedente.
Con questo finiamo questo tutorial, ora possiamo fare le nostre funzioni di base in SQL, facilitando e semplificando così molte attività di cui potremmo aver bisogno all'interno di un programma o di un sistema che stiamo costruendo.

2. Funzioni PostgreSQL in altri linguaggi

Una delle caratteristiche più interessanti di PostgreSQL è che non è limitato solo al linguaggio SQLGrazie al caricamento dei moduli, possiamo scegliere di incorporare funzionalità avanzate, inclusa la possibilità di utilizzare linguaggi diversi per costruire funzioni, con ciò possiamo ottenere una grande flessibilità utilizzando migliori capacità di generazione di condizionali e i vantaggi intrinseci ai vari linguaggi.

Funzioni di scrittura con PL / pgSQL
Nel momento in cui notiamo che lo standard SQL non è all'altezza delle query che vogliamo svolgere in una funzione, possiamo sempre ricorrere all'uso di PL/pgSQL; una delle sue differenze e miglioramenti rispetto a SQL è che le variabili locali possono essere dichiarate utilizzando l'istruzione DECLARE, possiamo anche avere il controllo sul flusso e dobbiamo racchiudere il corpo della funzione in un blocco BEGIN END.

Vediamo un esempio di una funzione scritta in questo linguaggio:

 FUNZIONE CREATE sel_logs_rt (param_user_name varchar) TABELLA RETURNS (log_id int, user_name varchar (50), testo descrizione, log_ts timestamptz) AS $$ BEGIN RETURN QUERY SELECT log_id, user_name, descrizione, log_ts FROM logs WHERE user_name = param_user_name; FINE; $$ LINGUA 'plpgsql' STABILE; 

Vediamo ora come scrivere funzioni con Python.

Scrivere funzioni con Python
Python è un linguaggio di programmazione abbastanza pulito, che ha un gran numero di librerie disponibili.
PostgreSQL è l'unico motore di database che consente di utilizzare Python per creare funzioni.

Per ottenere la possibilità di creare funzioni con Python, dobbiamo prima assicurarci di avere la lingua installata sul nostro server. Una volta che sappiamo di averlo installato, dobbiamo abilitare le estensioni all'interno di PostgreSQL usando i seguenti comandi:

 CREA ESTENSIONE plpython2u; CREA ESTENSIONE plpython3u; 

Dobbiamo assicurarci che Python sia attivo e funzionante prima di abilitare le estensioni per evitare errori.

Funzioni di base con Python
Una volta che abbiamo attivato tutto per poter usare Python, inizieremo a costruire la nostra funzione, è importante sapere che PostgreSQL può convertire i suoi tipi di dati in tipi di dati Python e viceversa. PL / Python è persino in grado di restituire array e tipi composti.

Vediamo di seguito una funzione che effettua una ricerca testuale in una risorsa online, cosa che non si poteva fare con PL/pgSQL, nell'immagine seguente vedremo il codice e poi faremo la spiegazione corrispondente.

• Importiamo le librerie che andremo ad utilizzare.
• Facciamo la ricerca web concatenando i parametri di input dell'utente.
• Leggiamo la risposta e la salviamo in un file HTML chiamato raw_html.
• Salviamo la parte dell'HTML che inizia con e finisce prima dell'inizio di.
• Rimuoviamo i tag HTML e gli spazi bianchi e salviamo nuovamente la variabile chiamata risultato.
• Restituiamo il risultato finale.
• Un'altra caratteristica interessante dell'uso di Python è che possiamo interagire direttamente con il sistema operativo, vediamo una funzione che fa un elenco di directory, va notato che questa deve essere creata da un superutente:

 CREAZIONE O SOSTITUZIONE DELLA FUNZIONE list_incoming_files () RETURNS SETOF testo AS $$ import os return os.listdir ('/ incoming') $$ LANGUAGE 'plpython2u' VOLATILE SECURITY DEFINER;

Qual è l'utilità di questo? Possiamo chiederci, perché immaginando di voler consultare i file che abbiamo a disposizione in un sistema, la chiamata alla funzione sarebbe qualcosa del genere:

 SELECT nome file FROM list_incoming_files () Come nome file WHERE nome file ILIKE '% .csv'

Con questo finiamo questo tutorial, gestiamo già la creazione di funzioni in altre lingue in PostgreSQL, che ci offre un campo infinito quando si tratta di soddisfare le nostre esigenze.