Documentazione di PostgreSQL 9.0 > Programmazione del server > PL/pgSQL - Linguaggio procedurale SQL > Strutture di controllo
PrecedenteIstruzioni di baseCursoriSuccessivo

39.6. Strutture di controllo

Le strutture di controllo sono probabilmente la parte più utile (e importante) di PL/pgSQL. Con le strutture di controllo di PL/pgSQL, è possibile manipolare i dati di PostgreSQL™ in modo molto flessibile e potente.

39.6.1. Ritorno da una funzione

Ci sono due comandi disponibili che permettono di restituire dati da una funzione: RETURN e RETURN NEXT.

39.6.1.1. RETURN

RETURN expression;

RETURN con un'espressione termina la funzione e ritorna il valore di expression al chiamante. Questa forma è usata per funzioni PL/pgSQL che non restituiscono un insieme.

Quando si restituisce un tipo scalare, può essere usata qualsiasi espressione. Il risultato dell'espressione sarà automaticamente convertito al tipo di ritorno della funzione come descritto per le assegnazioni. Per restituire un valore composto (riga), si deve scrivere una variabile record o riga come expression.

Se si dichiara la funzione con parametri di output, scrivere solo RETURN senza espressione. Saranno restituiti i valori attuali dei parametri di output.

Se si dichiara la funzione per ritornare void, un'istruzione RETURN può essere usata per uscire dalla funzione in anticipo; ma non scrivere un espressione dopo RETURN.

Il valore di ritorno di una funzione non può essere lasciato indefinito. Se il controllo raggiunge la fine del blocco superiore della funzione senza incontrare un'istruzione RETURN, viene generato un errore in fase di esecuzione. Questa restrizione comunque non si applica a funzioni con parametri di output e funzioni che restituiscono void. In quei casi un'istruzione RETURN viene eseguita automaticamente quando il blocco superiore finisce.

39.6.1.2. RETURN NEXT e RETURN QUERY

RETURN NEXT expression;
RETURN QUERY query;
RETURN QUERY EXECUTE command-string [ USING expression [, ... ] ];

Quando una funzione PL/pgSQL viene dichiarata per restituire SETOF sometype, la procedura da seguire e leggermente diversa. In quel caso, gli elementi individuali da restituire sono specificati da una sequenza di comandi RETURN NEXT o RETURN QUERY, e quindi un comando RETURN finale senza argomenti viene usato epr indicare che la funzione ha terminato l'esecuzione. RETURN NEXT può essere usato sia con tipi di dato scalari che composti; con un tipo di risultato composto, sarà restituita un'intera «table». RETURN QUERY aggiunge i risultati dell'esecuzione di una query all'insieme dei risultati della funzione. RETURN NEXT e RETURN QUERY possono essere liberamente interscambiati in una singola funzione che ritorna un insieme, nel qual caso i loro risultati saranno concatenati.

RETURN NEXT e RETURN QUERY in realtà non ritornano dalla funzione - semplicemente aggiungono zero o più righe al risultato di una funzione. L'esecuzione quindi continua con la prossima istruzione nella funzione PL/pgSQL. Come vengono eseguiti successivi comandi RETURN NEXT o RETURN QUERY, l'insieme risultato è formato. Un RETURN finale, che non dovrebbe avere argomenti, causa che il controllo esca dalla funzione (o è possibile lasciare che il controllo raggiunga la fine della funzione).

RETURN QUERY ha una variante RETURN QUERY EXECUTE, che specifica che la query sia eseguita dinamicamente. Espressioni di parametro possono essere inserite nella stringa calcolata della query attraverso USING, nello stesso modo del comando EXECUTE.

Se si è dichiarata la funzione senza parametri di output, scrivere solo RETURN NEXT senza espressione. Ad ogni esecuzione, i valori correnti della variabile/i parametro di output saranno salvati per l'eventuale ritorno come riga del risultato. Si noti che si deve dichiarare la funzione in modo che ritorni SETOF record quando ci sono molteplici parametri di output, o SETOF sometype quando c'è sono un parametro di output di tipo sometype, per creare una funzione che restituisce un insieme con parametri di output.

Ecco un esempio di una funzione che usa RETURN NEXT:

CREATE TABLE foo (fooid INT, foosubid INT, fooname TEXT);
INSERT INTO foo VALUES (1, 2, 'three');
INSERT INTO foo VALUES (4, 5, 'six');

CREATE OR REPLACE FUNCTION getAllFoo() RETURNS SETOF foo AS
$BODY$
DECLARE
    r foo%rowtype;
BEgin
    FOR r IN SELECT * FROM foo
    WHERE fooid > 0
    LOOP
        -- qui è possibile fare elaborazioni
        RETURN NEXT r; -- restituisce la riga corrente della SELECT
    END LOOP;
    RETURN;
END
$BODY$
LANGUAGE 'plpgsql' ;

SELECT * FROM getallfoo();

[Nota]

Nota

L'implementazione corrente di RETURN NEXT e RETURN QUERY salva l'intero insieme dei risultati prima di ritornare dalla funzione, come discusso sopra. Questo significa che se una funzione PL/pgSQL produce un insieme di risultati molto grande, le prestazioni potrebbero risentirne: i dati saranno scritti su disco per evitare l'esaurimento della memoria, ma la funzione stessa non ritornerà finchè l'intero insieme dei risultati sia stato generato. Una versione futura di PL/pgSQL potrebbe permettere agli utenti di definire funzioni che ritornano insiemi che non hanno questa limitazione. Attualmente, il punto in cui i dati cominciano ad essere scritti su disco è controllato dalla variabile di configurazione work_mem. Gli amministratori che hanno memoria sufficiente per immagazzinare in memoria grandi insiemi di risultati dovrebbero considerare l'incremento di questo parametro.

39.6.2. Condizioni

Le istruzioni IF e CASE ti permettono di eseguire comandi alternativi in base a certe condizioni. PL/pgSQL ha tre forme di IF:

  • IF ... THEN

  • IF ... THEN ... ELSE

  • IF ... THEN ... ELSIF ... THEN ... ELSE

e due forme di CASE:

  • CASE ... WHEN ... THEN ... ELSE ... END CASE

  • CASE WHEN ... THEN ... ELSE ... END CASE

39.6.2.1. IF-THEN

IF boolean-expression THEN
    statements
END IF;

Le istruzioni IF-THEN sono la forma più semplice di IF. Le istruzioni tra THEN e END IF saranno eseguite se la condizione è true. Altrimenti, vengono saltate.

Esempio:

IF v_user_id <> 0 THEN
    UPDATE users SET email = v_email WHERE user_id = v_user_id;
END IF;

39.6.2.2. IF-THEN-ELSE

IF boolean-expression THEN
    statements
ELSE
    statements
END IF;

Le istruzioni IF-THEN-ELSE si aggiungono a IF-THEN permettendo di specificare un insieme di istruzioni alternative che dovrebbero essere eseguite se la condizione non è true. (Notare che questo include i casi dove la condizione è valutata essere NULL).

Esempi:

IF parentid IS NULL OR parentid = ''
THEN
    RETURN fullname;
ELSE
    RETURN hp_true_filename(parentid) || '/' || fullname;
END IF;

IF v_count > 0 THEN
    INSERT INTO users_count (count) VALUES (v_count);
    RETURN 't';
ELSE
    RETURN 'f';
END IF;

39.6.2.3. IF-THEN-ELSIF

IF boolean-expression THEN
    statements
[ ELSIF boolean-expression THEN
    statements
[ ELSIF boolean-expression THEN
    statements
    ...
]
]
[ ELSE
    statements ]
END IF;

A volte ci sono più di due sole alternative. IF-THEN-ELSIF fornisce un metodo conveniente di controllare a turno diverse alternative. Le condizioni IF sono testate successivamente finchè la prima che è true viene trovata. Quindi l'istruzione/i associate sono eseguite, dopo le quali il controllo passa alla prossima istruzione dopo END IF. (Ogni successiva condizione IF non è testata). Se nessuna delle condizioni IF è true, allora viene eseguito il blocco ELSE (se presente).

Ecco un esempio:

IF number = 0 THEN
    result := 'zero';
ELSIF number > 0 THEN
    result := 'positive';
ELSIF number < 0 THEN
    result := 'negative';
ELSE
    -- hmm, l'unica altra possibilità è che il numero sia null
    result := 'NULL';
END IF;

La parola chiave ELSIF può essere scritta anche ELSEIF.

Un modo alternativo di svolgere lo stesso compito è di annidare le istruzioni IF-THEN-ELSE, come nell'esempio seguente:

IF demo_row.sex = 'm' THEN
    pretty_sex := 'man';
ELSE
    IF demo_row.sex = 'f' THEN
        pretty_sex := 'woman';
    END IF;
END IF;

Comunque, questo metodo richiede la scrittura di un corrispondente END IF per ogni IF, quindi è molto più scomodo rispetto ad usare ELSIF quando ci sono molte alternative.

39.6.2.4. CASE semplice

CASE search-expression
    WHEN expression [, expression [ ... ]] THEN
      statements
  [ WHEN expression [, expression [ ... ]] THEN
      statements
    ... ]
  [ ELSE
      statements ]
END CASE;

La forma semplice di CASE fornisce l'esecuzione condizionale basata sull'uguaglianza di operandi. La search-expression viene valutata (una volta) e successivamente confrontata con ogni expression nelle clausole WHEN. Se viene trovata una corrispondenza, allora le corrispondenti istruzioni statements sono eseguite, e quindi il controllo passa all'istruzione successiva dopo END CASE. (Successive espressioni WHEN non sono valutate). Se non viene trovata nessuna corrispondenza, vengono eseguite le istruzioni statements ELSE; ma se ELSE non è presente, allora viene generata un'eccezione CASE_NOT_FOUND.

Ecco un semplice esempio:

CASE x
    WHEN 1, 2 THEN
        msg := 'one or two';
    ELSE
        msg := 'other value than one or two';
END CASE;

39.6.2.5. CASE cercato

CASE
    WHEN boolean-expression THEN
      statements
  [ WHEN boolean-expression THEN
      statements
    ... ]
  [ ELSE
      statements ]
END CASE;

La forma cercata di CASE fornisce l'esecuzione condizionale basata sulla verità di espressioni Booleane. Ogni boolean-expression della clausola WHEN viene valutata a turno, finchè non ne viene trovata una che risulta true. Quindi le corrispondenti istruzioni statements vengono eseguite, e poi il controllo passa alla successiva istruzione dopo END CASE. (Successive espressioni WHEN non sono valutate). Se non vengono trovati risultati true, allora vengono eseguite le istruzioni statements ELSE; ma se ELSE non è presente, allora viene generata un'eccezione CASE_NOT_FOUND.

Ecco un esempio:

CASE
    WHEN x BETWEEN 0 AND 10 THEN
        msg := 'value is between zero and ten';
    WHEN x BETWEEN 11 AND 20 THEN
        msg := 'value is between eleven and twenty';
END CASE;

Questa forma di CASE è completamente equivalente a IF-THEN-ELSIF, ad eccezione della regola per cui raggiungere una clausola ELSE omessa genera un errore invece di non fare niente.

39.6.3. Cicli semplici

Con le istruzioni LOOP, EXIT, CONTINUE, WHILE e FOR, è possibile fare in modo che la funzione PL/pgSQL ripeta una serie di comandi.

39.6.3.1. LOOP

[ <<label>> ]
LOOP
    statements
END LOOP [ label ];

LOOP definisce un ciclo incondizionato che viene ripetuto indefinitzmente finchè terminato da un'istruzione EXIT o RETURN. L'etichetta label opzionale può essere usata dalle istruzioni EXIT e CONTINUE all'interno di cicli annidati per specificare a quale ciclo si riferiscono quelle istruzioni.

39.6.3.2. EXIT

EXIT [ label ] [ WHEN boolean-expression ];

Se nessuna label è fornita, il ciclo più interno viene terminato e l'istruzione seguente END LOOP viene eseguita. Se label è fornita, deve essere l'etichetta del ciclo annidato o del blocco corrente o esterno. Quindi il ciclo nominato o il blocco viene terminato e il controllo continua con l'istruzione dopo la END corrispondente del ciclo/blocco.

Se WHEN è specificato, l'uscita dal ciclo avviene solo se boolean-expression è true. Altrimenti, il controllo passa all'istruzione dopo EXIT.

EXIT può essere usato con tutti i tipi di ciclo; non è limitato all'utilizzo con cicli incondizionali.

Quando usato con un blocco BEGIN, EXIT passa il controllo alla successiva istruzione dopo la fine del blocco. Notare che una etichetta deve essere usata per questo scopo; una EXIT senza etichetta non è mai considerata corrispondente a un blocco BEGIN. (Questo è un cambiamento rispetto alle versioni precedenti la 8.4 di PostgreSQL™, che avrebbero permesso a una EXIT senza etichetta di corrispondere a un blocco BEGIN).

Esempi:

LOOP
    -- alcuni calcoli
    IF count > 0 THEN
        EXIT;  -- esce dal ciclo
    END IF;
END LOOP;

LOOP
    -- alcuni calcoli
    EXIT WHEN count > 0;  -- stesso risultato dell'esempio precedente
END LOOP;

<<ablock>>
BEGIN
    -- alcuni calcoli
    IF stocks > 100000 THEN
        EXIT ablock;  -- causa l'uscita dal blocco BEGIN
    END IF;
    -- i calcoli qui saranno saltati quando stocks > 100000
END;

39.6.3.3. CONTINUE

CONTINUE [ label ] [ WHEN boolean-expression ];

Se non viene fornita nessuna label, la successiva iterazione del ciclo più interno è iniziata. Cioè, tutte le istruzioni restanti nel corpo del ciclo sono saltate, e il controllo ritorna all'espressione di controllo del ciclo (se presente) per determinare se un'altra iterazione del loop è necessaria. Se label è presente, specifica l'etichetta del ciclo la cui esecuzione sarà continuata.

Se WHEN è specificata, la successiva iterazione del ciclo è cominciata solo se boolean-expression è true. Altrimenti, il controllo passa all'istruzione dopo CONTINUE.

CONTINUE puù essere usato con tutti i tipi di ciclo; non è limitato all'uso con cicli incondizionali.

Esempi:

LOOP
    -- alcuni calcoli
    EXIT WHEN count > 100;
    CONTINUE WHEN count < 50;
    -- alcuni calcoli per count IN [50 .. 100]
END LOOP;

39.6.3.4. WHILE

[ <<label>> ]
WHILE boolean-expression LOOP
    statements
END LOOP [ label ];

L'istruzione WHILE ripete una sequenza di istruzioni finchè l'espressione boolean-expression risulta true. L'espressione è controllata appena prima ogni ingresso nel corpo del ciclo.

Per esempio:

WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP
    -- alcuni calcoli qui
END LOOP;

WHILE NOT done LOOP
    -- alcuni calcoli qui
END LOOP;

39.6.3.5. FOR (variante intera)

[ <<label>> ]
FOR name IN [ REVERSE ] expression .. expression [ BY expression ] LOOP
    statements
END LOOP [ label ];

Questa forma di FOR crea un ciclo che itera su un intervallo di valori interi. La variabile name viene definita automaticamente come tipo integer ed esiste solo all'interno del ciclo (qualsiasi definizione esistente del nome della variabile viene ignorata all'interno del ciclo). Le due espressioni che forniscono il limite inferiore e superiore dell'intervallo sono calcolate una volta quando si entra nel ciclo. Se la clausola BY non è specificata, il passo di iterazione è 1, altrimenti è il valore specificato nella clausola BY, che di nuovo viene clcolato una volta all'ingresso del ciclo. Se REVERSE è specificato allora il valore dell'incremento è sottratto, invece che aggiunto, dopo ogni iterazione.

Alcuni esempi di cicli FOR di interi:

FOR i IN 1..10 LOOP
    -- i assumerà i valori 1,2,3,4,5,6,7,8,9,10 all'interno del ciclo
END LOOP;

FOR i IN REVERSE 10..1 LOOP
    -- i assumerà i valori 10,9,8,7,6,5,4,3,2,1 all'interno del ciclo
END LOOP;

FOR i IN REVERSE 10..1 BY 2 LOOP
    -- i assumerà i valori 10,8,6,4,2 all'interno del ciclo
END LOOP;

Se il limite inferiore è maggiore del limite superiore (o minore di, nel caso di REVERSE), il corpo del ciclo non viene eseguito. Non viene generato errore.

Se una etichetta label viene assegnata al ciclo FOR allora ci si può riferire alla variabile ciclo di interi con un nome qualificato, usando quell'etichetta label.

39.6.4. Cicli attraverso i risultati di una query

Usando un tipo diverso di ciclo FOR, è possibile iterare attraverso i risultati di una query e manipolare quei dati di conseguenza. La sintassi è:

[ <<label>> ]
FOR target IN query LOOP
    statements
END LOOP [ label ];

Il target è una variabile record, variabile riga, o elenco separato da virgole di variabili scalari. Il target successivamemente è assegnato ad ogni riga risultante dalla query e il corpo del ciclo viene eseguito per ogni riga. Ecco un esempio:

CREATE FUNCTION cs_refresh_mviews() RETURNS integer AS $$
DECLARE
    mviews RECORD;
BEGIN
    PERFORM cs_log('Refreshing materialized views...');

    FOR mviews IN SELECT * FROM cs_materialized_views ORDER BY sort_key LOOP

        -- Adesso "mviews" ha un record da cs_materialized_views

        PERFORM cs_log('Refreshing materialized view '
                   || quote_ident(mviews.mv_name) || ' ...');
        EXECUTE 'TRUNCATE TABLE ' || quote_ident(mviews.mv_name);
        EXECUTE 'INSERT INTO '
                   || quote_ident(mviews.mv_name) || ' '
                   || mviews.mv_query;
    END LOOP;

    PERFORM cs_log('Done refreshing materialized views.');
    RETURN 1;
END;
$$ LANGUAGE plpgsql;

Se il ciclo è terminato da un'istruzione EXIT, l'ultimo valore assegnato della riga è comunque accessibile dopo il ciclo.

La query usata in questo tipo di istruzione FOR può essere qualsiasi comando SQL che ritorna righe al chiamante: SELECT è il caso più comune, ma si possono usare anche INSERT, UPDATE, o DELETE con una clausola RETURNING. Funzioneranno anche alcuni comandi di utilità tipo EXPLAIN.

Le variabili PL/pgSQL sono sostituiti nel testo della query, e il piano della query è sottoposto a cache per un possibile riutilizzo, come discusso in dettaglio in Sezione 39.10.1, «Sostituzione di variabili» e Sezione 39.10.2, «Cache del piano».

L'istruzione FOR-IN-EXECUTE rappresenta un altro modo di iterare le righe:

[ <<label>> ]
FOR target IN EXECUTE text_expression [ USING expression [, ... ] ] LOOP
    statements
END LOOP [ label ];

Questo è simile alla forma precedente, ad eccezione che la query sorgente viene specificata come una espressione stringa, che è calcolata e ripianificata ad ogni voce del ciclo FOR. Questo permette al programmatore di scegliere la velocità di una query prepianificata o la flessibilità di una query dinamica, proprio come con una normale istruzione EXECUTE. Come con EXECUTE, i valori dei parametri possono essere inseriti nel comando dinamico con USING.

Un altro modo di specificare la query i cui risultati dovrebbero essere iterati è di dichiararla come cursore. Questo è descritto in Sezione 39.7.4, «Looping Through a Cursor's Result».

39.6.5. Trapping Errors

In maniera predefinitia, qualsiasi errore accada in una funzione PL/pgSQL annulla l'esecuzione della funzione, ed effettivamente anche la transazione circostante. È possibile intrappolare gli errori e ripristinarli usando un blocco BEGIN con una clausola EXCEPTION. La sintassi è una estensione della sintassi normale di un blocco BEGIN:

[ <<label>> ]
[ DECLARE
    declarations ]
BEGIN
    statements
EXCEPTION
    WHEN condition [ OR condition ... ] THEN
        handler_statements
    [ WHEN condition [ OR condition ... ] THEN
          handler_statements
      ... ]
END;

Se non si verificano errori, questa forma di blocco semplicemente esegue tutte le istruzioni statements, e quindi il controllo passa alla successiva istruzione dopo END. Ma se un errore accade all'interno di statements, l'ulteriore elaborazione di istruzioni statements viene abbandonata, e il controllo passa all'elenco EXCEPTION. L'elenco viene cercato per la prima condition corrispondente all'errore accaduto. Se viene trovata una corrispondenza, vengono eseguite le corrispondenti istruzioni handler_statements, e quindi il controllo passa alla successiva istruzione dopo END. Se non vengono trovate corrispondenze, l'errore si propaga sebbene la clausola EXCEPTION non ci fosse stata: l'errore può essere catturato da un blocco con EXCEPTION, o se non ce ne sono esso annulla l'elaborazione della funzione.

I nomi delle condizioni condition possono essere qualsiasi di quelli mostrati in Appendice A, Codici di errore di PostgreSQL. Un nome di categoria corrisponde a qualsiasi errore all'interno della sua categoria. Il nome della condizione speciale OTHERS corrisponde ad ogni tipo di errore ad eccezione di QUERY_CANCELED. (È possibile, ma spesso imprudente, intercettare QUERY_CANCELED dal nome). I nomi delle condizioni non sono sensibili al case. Inoltre, una condizione di errore può essere specificata dal codice SQLSTATE; per esempio, questi sono equivalenti:

WHEN division_by_zero THEN ...
WHEN SQLSTATE '22012' THEN ...

Se succede un nuovo errore all'interno delle istruzioni handler_statements, non può essere catturato da questa clausola EXCEPTION, ma viene propagato al di fuori. Una clausola EXCEPTION circostante potrebbe intercettarlo.

Quando un errore è catturato da una clausola EXCEPTION, le variabili locali della funzione PL/pgSQL rimangono come erano quando l'errore è accaduto, ma tutti i cambiamenti allo stato persistente del database all'interno del blocco sono sottoposti a rollback. Come esempio, considerare questo frammento:

INSERT INTO mytab(firstname, lastname) VALUES('Tom', 'Jones');
BEgin
    UPDATE mytab SET firstname = 'Joe' WHERE lastname = 'Jones';
    x := x + 1;
    y := x / 0;
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE 'caught division_by_zero';
        RETURN x;
END;

Quando il controllo raggiunge l'assegnazione a y, fallirà con un errore division_by_zero. Questo sarà catturato dalla clausola EXCEPTION. Il valore restituito nell'istruzione RETURN sarà il valore incrementato di x, ma gli effetti del comando UPDATE saranno stati sottoposti a rollback. Il comando INSERT precedente il blocco non è sottoposto a rollback, comunque, quindi il risultato finale è che il database contiene Tom Jones e non Joe Jones.

[Suggerimento]

Suggerimento

Un blocco contenente una clausola EXCEPTION è significativamente più costoso da entrare e uscire rispetto a un blocco senza. Per questo, non usare EXCEPTION senza bisogno.

All'interno di un gestore di eccezione, la variabile SQLSTATE contiene il codice dell'errore che corrisponde all'eccezione che è stata generata (si veda Tabella A.1, «Codici di errore PostgreSQL™» per un elenco dei possibili codici di errore). La variabile SQLERRM contiene il messaggio di errore associato con l'eccezione. Queste variabili sono indefinite all'esterno del gestore di eccezione.

Esempio 39.2. Eccezioni con UPDATE/INSERT

Questo esempio usa la gestione delle eccezioni per eseguire o una UPDATE o una INSERT, come appropriato:

CREATE TABLE db (a INT PRIMARY KEY, b TEXT);

CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
$$
BEgin
    LOOP
        -- prima prova ad aggiornare la chiave 
        UPDATE db SET b = data WHERE a = key;
        IF found THEN
            RETURN;
        END IF;
        -- non trovata, quindi prova ad inserire la chiave
        -- se qualcun altro inserisce la stessa chiave concorrentemente,
        -- potremmo ottenere un fallimento unique-key
        BEgin
            INSERT INTO db(a,b) VALUES (key, data);
            RETURN;
        EXCEPTION WHEN unique_violation THEN
            -- non fa niente, e cicla per provare di nuovo l'UPDATE
        END;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

SELECT merge_db(1, 'david');
SELECT merge_db(1, 'dennis');


Documentazione di PostgreSQL 9.0 > Programmazione del server > PL/pgSQL - Linguaggio procedurale SQL > Strutture di controllo
PrecedenteIstruzioni di baseCursoriSuccessivo