Documentazione di PostgreSQL 9.0 > Programmazione del server > PL/pgSQL - Linguaggio procedurale SQL > Istruzioni di base
PrecedenteEspressioniStrutture di controlloSuccessivo

39.5. Istruzioni di base

In questa sezione e nelle seguenti, descriveremo tutti i tipi di istruzione che sono implicitamente compresi da PL/pgSQL. Qualsiasi cosa non riconosciuta come uno si questi tipi di istruzione è supposta essere un comando SQL ed è spedita al motore principale del database per essere eseguita, come descritto in Sezione 39.5.2, «Eseguire un comando senza risultato» e Sezione 39.5.3, «Eseguire una query con un risultato di una singola riga».

39.5.1. Assegnazione

Un'assegnazione di un valore a una variabile PL/pgSQL è scritta come:

variable := expression;

Come spiegato precedentemente, l'espressione in tale istruzione viene valutata mediante un comando SQL SELECT mandato al motore database principale. L'espressione deve generare un singolo valore (possibilmente un valore riga, se la variabile è una variabile riga o record). La variabile obiettivo può essere una variabile semplice (opzionalmente qualificata con un nome di blocco), un campo di una variabile riga o record, o un elemento di un array che è una variabile o campo semplice.

Se il tipo di dato risultante dell'espressione non corrisponde al tipo di dato della variabile, o la variabile ha una dimensione/precisione specifica (tipo char(20)), il valore del risultato sarà implicitamente convertito dall'interprete PL/pgSQL usando la funzione output del tipo del risultato e la funzione input del tipo della variabile. Si noti che questo risulta in potenziali errori in fase di esecuzione generati dalla funzioni di input, se la forma della stringa del valore del risultato non è accettabile per la funzione di input.

Esempi:

tax := subtotal * 0.06;
my_record.user_id := 20;

39.5.2. Eseguire un comando senza risultato

Per qualsiasi comando SQL che non restituisce righe, per esempio INSERT senza una clausola RETURNING, è possibile eseguire il comando all'interno di una funzione PL/pgSQL semplicemente scrivendo il comando.

Qualsiasi nome di variabile PL/pgSQL che appare nel testo del comando viene trattato come parametro, e quindi il valore corrente della variabile viene fornito come il valore del parametro in fase di esecuzione. Questo è esattamente come l'elaborazione descritta precedentemente per le espressioni; per i dettagli si veda Sezione 39.10.1, «Sostituzione di variabili».

Quando si esegue un comando SQL in questo modo, PL/pgSQL pianifica il comando solo una volta e riusa il piano per successive esecuzioni, fino alla fine della connessione al database. Le implicazioni di questo sono discusse in dettaglio in Sezione 39.10.2, «Cache del piano».

A volte è utile valutare un'espressione i una query SELECT ma scartare il risultato, per esempio quando si chiama una funzione che ha effetti collaterali ma non un risultato utile. Per fare questo in PL/pgSQL, usare l'istruzione PERFORM:

PERFORM query;

Questo esegue query e scarta il risultato. Scrivere la query allo stesso modo di come si scriverebbe un comando SQL SELECT, ma sostituire la parola chiave iniziale SELECT con PERFORM. Le variabili PL/pgSQL saranno sostituiti nella query esattamente come per i comandi che non restituiscono risultati, e il piano viene sottoposto a cache nello stesso modo. Inoltre, la variabile speciale FOUND viene impostata a true se la query ha prodotto almeno una riga, o false se non ha prodotto righe (si veda Sezione 39.5.5, «Ottenere lo stato del risultato»).

[Nota]

Nota

Ci si potrebbe aspettare che scrivere direttamente SELECT avrebbe questo risultato, ma al momento l'unico modo accettato per farlo è PERFORM. Un comando SQL che può restituire righe, tipo un SELECT, sarà rifiutato come errore finchè non ha una clausola INTO come discusso nella prossima sezione.

Un esempio:

PERFORM create_mv('cs_session_page_requests_mv', my_query);

39.5.3. Eseguire una query con un risultato di una singola riga

Il risultato di una comando SQL che genera una singola riga (possibilmente di molteplici colonne) può essere assegnato a una variabile record, tipo riga, o elenco di variabili scalari. Questo viene fatto scrivendo il comando SQL di base e aggiungendo una clausola INTO. Per esempio,

SELECT select_expressions INTO [STRICT] target FROM ...;
INSERT ... RETURNING expressions INTO [STRICT] target;
UPDATE ... RETURNING expressions INTO [STRICT] target;
DELETE ... RETURNING expressions INTO [STRICT] target;

dove target può essere una variabile record, una variabile riga, o un elenco separato da virgole di variabili semplici e campi record/riga. Le variabili PL/pgSQL saranno sostituite nel resto della query, e il piano viene sottoposto a cache, esattamente come descritto sopra per comandi che non ritornano righe. Questo funziona per SELECT, INSERT/UPDATE/DELETE con RETURNING, e comandi di utilità che restituiscono risultati insieme di righe (come EXPLAIN). Ad eccezione della clausola INTO, il comando SQL è lo stesso di come sarebbe scritto fuori da PL/pgSQL.

[Suggerimento]

Suggerimento

Si noti che questa interpretazione di SELECT con INTO è abbastanza diversa dal normale comando SELECT INTO di PostgreSQL™, in cui il target di INTO e una tabella creata ex-novo. Se si vuole creare una tabella dal risultato di una SELECT all'interno di una funzione PL/pgSQL, usare la sintassi CREATE TABLE ... AS SELECT.

Se una riga o un elenco di variabili è usato come target, le colonne risultanti della query devono corrispondere esattamente alla struttura del target come numero e tipi di dato, altrimenti si genera un errore in fase di esecuzione. Quando una variabile record è il target, essa automaticamente configura sè stessa al tipo riga delle colonne del risultato della query.

La clausola INTO può apparire quasi ovunque nel comando SQL. Di norma è scritta o appena prima o appena dopo l'elenco di select_expressions in un comando SELECT, o alla fine del comando per altri tipi di comando. Si raccomanda di seguire questa convenzione nel caso che il parser di PL/pgSQL diventi più rigido in versioni future.

Se STRICT non è specificato nella clausola INTO, allora target sarà impostato alla prima riga restituita dalla query, o a null se la query non ha restituito righe. (Notare che «la prima riga» non è ben definita a meno che non si sia usato ORDER BY). Ogni riga risultante dopo la prima viene scartata. Si può controllare la variabile speciale FOUND (si veda Sezione 39.5.5, «Ottenere lo stato del risultato») per determinare se una riga è stata restituita:

SELECT * INTO myrec FROM emp WHERE empname = myname;
IF NOT FOUND THEN
    RAISE EXCEPTION 'employee % not found', myname;
END IF;

Se l'opzione STRICT è specificata, la query deve ritornare esattamente una riga o sarà riportato un errore al momento dell'esecuzione, o NO_DATA_FOUND (nessusa riga) o TOO_MANY_ROWS (più di una riga). Si può usare un blocco eccezione se si desidera intercettare l'errore, per esempio:

BEGIN
    SELECT * INTO STRICT myrec FROM emp WHERE empname = myname;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RAISE EXCEPTION 'employee % not found', myname;
        WHEN TOO_MANY_ROWS THEN
            RAISE EXCEPTION 'employee % not unique', myname;
END;

L'esecuzione con successo di un comando con STRICT imposta sempre FOUND a true.

Per INSERT/UPDATE/DELETE con RETURNING, PL/pgSQL riporta un errore per più di una riga restituita, anche quando STRICT non è specificato. Questo perchè non c'è un opzione tipo la ORDER BY con la quale determinare quali righe coinvolte dovrebbero essere restituite.

[Nota]

Nota

L'opzione STRICT corrisponde al comportamento di del comando Oracle PL/SQL SELECT INTO e relative istruzioni.

Per gestire casi dove si ha bisogno di elaborare moltpeplici righe risultanti da una query SQL, si veda Sezione 39.6.4, «Cicli attraverso i risultati di una query».

39.5.4. Eseguire comandi dinamici

Spesso si vorrà generare comandi dinamici all'interno delle funzioni PL/pgSQL, cioè, comandi che coinvolgeranno differenti tabelle o diversi tipi di dato ogni volta che sono vengono eseguiti. I normali tentativi di PL/pgSQL di sottoporre a cache i piani per i comandi (come discusso in Sezione 39.10.2, «Cache del piano») non funzioneranno in queste situazioni. Per gestire questa sorta di problemi, è disponibile l'istruzione EXECUTE:

EXECUTE command-string [ INTO [STRICT] target ] [ USING expression [, ... ] ];

dove command-string è un'espressione che genera una stringa (di tipo text) contenente il comando che deve essere eseguito. L'opzionale target è una variabile record, una variabile riga, o un elenco separato da virgole di semplici variabili e campi di riga/record, nei quali i risultati del comando saranno salavti. Le espressioni USING opzionali forniscono valori da essere inseriti nel comando.

Nessuna sostituzione di variabili PL/pgSQL viene fatta sulla stringa comando calcolata. Qualsiasi valore della variabile deve essere inserito nella stringa comando come è costruito; o si possono usare parametri come descritto sotto.

Inoltre, il piano non viene sottoposto a cache per comandi eseguiti attraverso EXECUTE. Invece, il comando viene preparato ogni volta che l'istruzione è eseguita. Così la stringa comando può essere creata dinamicamente all'interno della funzione per eseguire azioni su diverse tabelle e colonne.

La clausola INTO specifica dove dovrebbero essere assegnati i risultati di un comando SQL che restituisce righe. Se viene fornita una riga o elenco variabile, deve corrispondere esattamente la struttura dei risultati della query (quando è usata una variabile record, essa configurerà automaticamente sè stessa per corrispondere la struttura del risultato). Se vengono restituite molteplici righe, solo la prima sarà assegnata alla variabile INTO. Se non sono restituite righe, alla variabile/i NULL viene assegnato NULL. Se non è specificata la clausola INTO, i risultati della query vengono scartati.

Se viene fornita l'opzione STRICT, viene riportato un errore finchè la query esattamente una riga.

La stringa del comando può usare valori parametro, che sono referenziati nel comando come $1, $2, ecc. Questi simboli si riferiscono a valori forniti nella clausola USING Questo metodo spesso è preferibile per inserire valori di dati nella stringa comando come testo: elimina overhead in fase di esecuzione convertendo i valori a testo e viceversa, ed è molto meno soggetta a attacchi di tipo SQL.injection dato che non c'è necessità di effettuare il quoting o l'escaping. Un esempio è:

EXECUTE 'SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted <= $2'
   INTO c
   USING checked_user, checked_date;

Notare che i simboli parametro possono essere usati solo per valori di dati - se si vogliono usare nomi di tabelle o colonne determinati dinamicamente, si devono inserire testualmente nella stringa comando. Per esempio, se la precedente query necessitava di essere fatta su una tabella selezionata dinamicamente, si potrebbe fare così:

EXECUTE 'SELECT count(*) FROM '
    || tabname::regclass
    || ' WHERE inserted_by = $1 AND inserted <= $2'
   INTO c
   USING checked_user, checked_date;

Un'altra restrizione sui simboli parametro è che funzionano solo in comandi SELECT, INSERT, UPDATE, e DELETE. In altri tipi di istruzione (chiamate genericamente istruzioni d'ulitità), si devono inserire i valori testualmente anche se sono solamente valori di dati.

Una EXECUTE con una semplice stringa di comando costante e alcuni parametri USING, come nel primo esempio sopra, è funzionalmente equivalente a scrivere semplicemente il comando direttamente in PL/pgSQL e permettendo che la sostituzione di variabili PL/pgSQL accada automaticamente. La differenza importante è che EXECUTE ripianificherà il comando ad ogni esecuzione, generando un piano che è specifico per i valori del parametro corrente; mentre PL/pgSQL normalmente crea un piano generico e lo sottopone a cache per il riuso. In situazioni dove il piano migliore dipende fortemente dai valori dei parametri, EXECUTE può essere significativamente veloce; mentre quando il piano non è sensibile ai valori dei parametri, ripianificare sarà uno spreco.

SELECT INTO attualmente non è supportato all'interno di EXECUTE; invece, eseguire un semplice comando SELECT e specificare INTO come parte dell'EXECUTE stesso.

[Nota]

Nota

L'istruzione EXECUTE di PL/pgSQL non è correlata con la funzione SQL EXECUTE(7) supportata dal server PostgreSQL™. La funzione EXECUTE del server non può essere usata direttamente all'interno di funzioni PL/pgSQL (e non è necessaria).

Esempio 39.1. Quotare valori in query dinamiche

Quando si lavora con comandi dinamici spesso di dovrà gestire l'escape di apici singoli. Il metodo raccomandato per quotare testo fisso nel corpo di una funziona è il quoting con dollari. (Se si ha codice datato che non usa la quotazione con dollari, si prega di riferirsi alla panoramica in Sezione 39.11.1, «Handling of Quotation Marks», che può far risprmiare qualche sforzo quando si traduce tale codice a uno schema più ragionevole).

Valori dinamici che devono essere iseriti nella query costruita richiedono una gestione attenta dato che potrebbero contenere caratteri apice. Un esempio (quato assume che si stia usando la quotazione con dollari per l'intera funzione, così gli apici non necessitano di essere raddoppiati):

EXECUTE 'UPDATE tbl SET '
        || quote_ident(colname)
        || ' = '
        || quote_literal(newvalue)
        || ' WHERE key = '
        || quote_literal(keyvalue);

Questo esempio dimostra l'utilizzo delle funzioni quote_ident e quote_literal (si veda Sezione 9.4, «Funzioni e operatori sulle stringhe»). Per sicurezza, espressioni contenenti identificatori di colonna o tabella dovrebbero essere passati attraverso quote_ident prima dell'inserimento in una query dinamica. Espressioni contenenti valori che dovrebbero essere stringhe letterali nel comando costruito dovrebbero essere passate attraverso quote_literal. Queste funzioni eseguono i passi appropriati a restituire il testo in input racchiuso rispettivamente tra apici doppi o singoli, con qualsiasi carattere speciale incluso sottoposto a appropriatamente a escape.

Dato che quote_literal è etichettata come STRICT, restituirà sempre null quando chiamata con un argomento null. Nell'esempio sopra, se newvalue o keyvalue erano null, l'intera stringa di query dinamica diventerebbe null, generando un errore da EXECUTE. È possibile evitare questo problema usando la funzione quote_nullable, che lavora allo stesso modi di quote_literal ad accezione che quando chiamata con un argomento null restituisce la stringa NULL. Per esempio,

EXECUTE 'UPDATE tbl SET '
        || quote_ident(colname)
        || ' = '
        || quote_nullable(newvalue)
        || ' WHERE key = '
        || quote_nullable(keyvalue);

Se si stanno trattando valori che potrebbero essere null, si dovrebbe di solito usare quote_nullable al posto di quote_literal.

Come al solito, dev'essere posta attenzione che i valori null in una query non portino a risultati non voluti. Per esempio la clausola WHERE

'WHERE key = ' || quote_nullable(keyvalue)

non avrà mai successo se keyvalue è null, dato che il risultato di usare l'operatore uguale = con un operando null è sempre null. Se si desidera che null funzioni come un valore chiave normale, si dovrà riscrivere il sopra come

'WHERE key IS NOT DISTINCT FROM ' || quote_nullable(keyvalue)

(Al momento, IS NOT DISTINCT FROM viene gestito molto meno efficientemente di =, quindi non farlo a meno che non si debba. Si veda Sezione 9.2, «Operatori di confronto» per maggiori informazioni sui null e IS DISTINCT).

Notare che la quitazione con dollari è utile solo per quotare testo fisso. Sarebbe una cattiva idea provare a scrivere questo esempio come:

EXECUTE 'UPDATE tbl SET '
        || quote_ident(colname)
        || ' = $$'
        || newvalue
        || '$$ WHERE key = '
        || quote_literal(keyvalue);

dato che si romperebbe se i contenuti di newvalue contenessero $$. La stessa obiezione si applicherebbe a qualsiasi altro delimitatore di quotazione con dollari si scelga. Quindi, per quotare in sicurezza testo che non è conosciuto in anticipo, si deve usare quote_literal, quote_nullable o quote_ident, in modo appropriato.


Un esempio più vasto di comando dinamico e EXECUTE si trova in Esempio 39.7, «Portare una funzione che crea un'altra funzione da PL/SQL a PL/pgSQL», che costruisce ed esegue un comando CREATE FUNCTION per definire una nuova funzione.

39.5.5. Ottenere lo stato del risultato

Ci sono diversi modi di determinare l'effetto di un comando. Il primo metodo è di usare il comando GET DIAGNOSTICS, che ha la forma:

GET DIAGNOSTICS variable = item [ , ... ];

Questo comando permettere il recupero di indicatori dello stato del sistema. Ogni item è una parola chiave che identifica il valore di uno stato da assegnare alla specifica variabile (che dovrebbe essere del giusto tipo per riceverlo). Gli elementi di stato attualmente disponibili sono ROW_COUNT, il numero di righe elaborate dall'ultimo comando SQL mandato al motore SQL, e RESULT_OID, l'OID dell'ultima riga inserita dal più recente comando SQL. Notare che RESULT_OID è utile solo dopo un comando INSERT in una tabella contenente OID.

Un esempio:

GET DIAGNOSTICS integer_var = ROW_COUNT;

Il secondo metodo per determinare gli effetti di un comando è di controllare la variabile speciale chiamata FOUND, che è di tipo boolean. FOUND all'inizio è false all'interno di ogni chiamata di funzione PL/pgSQL. È impostata da ognuno dei seguenti tipi di istruzione:

  • Un'istruzione SELECT INTO imposta FOUND a true se una riga viene assegnata, false se non viene restituita nessuna riga.

  • Un'istruzione PERFORM imposta FOUND a true se produce (e scarta) una o più righe, false se non vengono prodotte righe.

  • Le istruzioni UPDATE, INSERT, e DELETE impostano FOUND a true se almeno una riga è coinvolta, false se nessuna riga è interessata.

  • Un'istruzione FETCH imposta FOUND a true se restituisce una riga, false se non viene restituita nessuna riga.

  • Un'istruzione MOVE imposta FOUND a true se riposiziona con successo il cursore, altrimenti false.

  • Un'istruzione FOR imposta FOUND a true se itera una o più volte, altrimenti false. Questo si applica a tutte le quattro varianti di dell'istruzione FOR (cicli FOR di interi, cicli FOR di insiemi di record, cicli FOR di insiemi di record dinamici e cicli FOR di cursori). FOUND è impostato in questo modo quando il ciclo FOR esiste; all'interno dell'esecuzione del loop, FOUND non viene modificato dall'istruzione FOR, sebbene potrebbe essere cambiato dall'esecuzione di altre istruzioni all'interno del corpo del ciclo.

  • Le istruzioni RETURN QUERY e RETURN QUERY EXECUTE impostano FOUND a true se la query restituisce almeno una riga, false se non viene restituita nessuna riga.

Altre istruzioni PL/pgSQL non camnbiano lo stato di FOUND. Notare in particolare che EXECUTE cambia l'output di GET DIAGNOSTICS, ma non cambia FOUND.

FOUND è una variabile locale all'interno di ogni funzione PL/pgSQL; qualsiasi cambiamento di essa interessa solo la funzione corrente.

39.5.6. Non fare assolutamente niente

A volte è utile un'istruzione "segnaposto" che non faccia niente. Per esempio, potrebbe indicare che un anello della catena if/then/else è deliberatamente vuoto. Per questo scopo, usare l'istruzione NULL:

NULL;

Per esempio, i seguenti due frammenti di codice sono equivalenti:

BEGIN
    y := x / 0;
EXCEPTION
    WHEN division_by_zero THEN
        NULL;  -- ignorare l'errore
END;

BEGIN
    y := x / 0;
EXCEPTION
    WHEN division_by_zero THEN  -- ignorare l'errore
END;

Quale sia preferibile è una questione di gusti.

[Nota]

Nota

In PL/SQL di Oracle, elenchi di istruzione vuoti non sono permessi, e quindi le istruzioni NULL sono richieste per situazioni come questa. PL/pgSQL invece permette di non scrivere niente.

Documentazione di PostgreSQL 9.0 > Programmazione del server > PL/pgSQL - Linguaggio procedurale SQL > Istruzioni di base
PrecedenteEspressioniStrutture di controlloSuccessivo