Struttura di PL/pgSQL

PL/pgSQL è un linguaggio procedurale con molte delle stesse costruzioni di altri linguaggi procedurali.

Blocco

PL/pgSQL è un linguaggio strutturato a blocchi. Il corpo completo di una procedura è definito in un blocco, che contiene dichiarazioni variabili e istruzioni PL/pgSQL. Un'istruzione può anche essere un blocco nidificato o un sottoblocco.

Finire le dichiarazioni e le istruzioni con un punto e virgola. Segui la parola chiave END in un blocco o un sottoblocco con un punto e virgola. Non utilizzare il punto e virgola dopo le parole chiave DECLARE e BEGIN.

Puoi scrivere tutte le parole chiave e tutti gli identificatori in un mix di maiuscolo e minuscolo. Gli identificatori vengono convertiti implicitamente in minuscolo a meno che siano racchiusi in virgolette doppie.

Un trattino doppio (--) inizia un commento che si estende alla fine della riga. Un /* inizia un commento blocco che si estende alla prossima occorrenza di */. Non è possibile nidificare commenti blocco. Tuttavia, è possibile racchiudere i commenti a doppio trattino in un commento blocco e un trattino doppio può nascondere i delimitatori del commento blocco /* e */.

Qualsiasi istruzione in una sezione istruzioni di un blocco può essere un sottoblocco. Puoi utilizzare sottoblocchi per un raggruppamento logico o per localizzare variabili di un gruppo piccolo di istruzioni.

[ <<label>> ]
[ DECLARE
  declarations ]
BEGIN
  statements
END [ label ];  

Le variabili dichiarate nella sezione dichiarazioni prima di un blocco vengono inizializzate ai loro valori predefiniti ogni volta che viene inserito il blocco. In altre parole, non vengono inizializzati solo una volta per chiamata di funzione.

Di seguito viene riportato un esempio.

CREATE PROCEDURE update_value() AS $$
DECLARE
  value integer := 20;
BEGIN
  RAISE NOTICE 'Value here is %', value;  -- Value here is 20
  value := 50;
  --
  -- Create a subblock
  --
  DECLARE
    value integer := 80;
  BEGIN
    RAISE NOTICE 'Value here is %', value;  -- Value here is 80
  END;

  RAISE NOTICE 'Value here is %', value;  -- Value here is 50
END;
$$ LANGUAGE plpgsql;               

Utilizza un'etichetta per identificare il blocco da utilizzare in un'istruzione EXIT o per qualificare i nome delle variabili dichiarate nel blocco.

Non confondere l'utilizzo di BEGIN/END per le istruzioni di raggruppamento in PL/pgSQL con i comandi del database per il controllo della transazione. BEGIN e END in PL/pgSQL sono solo per il raggruppamento. Non iniziano o terminano la transazione.

Dichiarazione di variabile

Dichiarare tutte le variabili in un blocco, ad eccezione delle variabili di loop, nella sezione DECLARE del blocco. Le variabili possono utilizzare qualsiasi tipo di dati Amazon Redshift valido. Per tipi di dati supportati, consultare Tipi di dati.

Le variabili PL/pgSQL possono essere qualsiasi tipo di dati supportati da Amazon Redshift, più RECORD e refcursor. Per ulteriori informazioni su RECORD, consultare Tipi di record. Per ulteriori informazioni su refcursor, consultare Cursori.

DECLARE
name [ CONSTANT ] type [ NOT NULL ] [ { DEFAULT | := } expression ];

Seguono delle dichiarazioni delle variabili di esempio.

customerID integer;
numberofitems numeric(6);
link varchar;
onerow RECORD;

La variabile per un loop FOR che effettua l'iterazione in una gamma di numeri interi viene dichiarata automaticamente come variabile di numero intero.

La clausola DEFAULT, se fornita, specifica il valore iniziale assegnato alla variabile quando viene inserito il blocco. Se la clausola DEFAULT non viene fornita, la variabile viene inizializzata con il valore SQL NULL. L'opzione CONSTANT previene che la variabile venga assegnata, in modo che il suo valore rimanga constante per la durata del blocco. Se NOT NULL è specificato, un'assegnazione di un valore null risulta in errore di runtime. Tutte le variabili dichiarate come NOT NULL devono avere un valore predefinito non-null specificato.

Il valore predefinito viene valutato ogni volta che il blocco viene inserito. Ad esempio, l'assegnazione di now() a una variabile del tipo timestamp determina che la variabile abbia l'ora della chiamata di funzione corrente, non l'ora di quando la funzione è stata precompilata.

quantity INTEGER DEFAULT 32;
url VARCHAR := 'http://mysite.com';
user_id CONSTANT INTEGER := 10;

Il tipo di dati refcursor è il tipo di dati delle variabili di cursore nelle procedure archiviate. Un valore refcursor può essere restituito da una procedura archiviata. Per ulteriori informazioni, consultare Restituzione di un set di risultati.

Dichiarazione alias

Se la firma della procedura archiviata omette il nome dell'argomento, è possibile dichiarare un alias per l'argomento.

name ALIAS FOR $n;

Variabili integrate

Le seguenti variabili integrate sono supportate:

• FOUND

• SQLSTATE

• SQLERRM

• GET DIAGNOSTICS integer_var := ROW_COUNT;

FOUND è una variabile speciale di tipo booleano. FOUND inizia come falsa in ogni chiamata di procedura. FOUND viene impostato dai seguenti tipi di istruzioni:

• SELECT INTO

  Imposta FOUND su true se restituisce una riga, false se non viene restituita alcuna riga.

• UPDATE, INSERT e DELETE

  Imposta FOUND su true se almeno una riga è coinvolta, false se nessuna riga è coinvolta.

• FETCH

  Imposta FOUND su true se restituisce una riga, false se non viene restituita alcuna riga.

• Istruzione FOR

  Imposta FOUND su true se l'istruzione FOR effettua l'iterazione una o più volte, altrimenti la imposta su false. Questo si applica a tutte e tre le varianti dell'istruzione FOR: loop FOR interi, loop FOR record-set e loop FOR record-set dinamici.

  FOUND viene impostato quando il loop FOR esce. Nel runtime del loop, FOUND non viene modificato dall'istruzione FOR. Tuttavia, può essere modificato dall'esecuzione di altre istruzioni nel corpo del loop.

Di seguito viene riportato un esempio.

CREATE TABLE employee(empname varchar);
CREATE OR REPLACE PROCEDURE show_found()
AS  $$
DECLARE
  myrec record;
BEGIN
  SELECT INTO myrec * FROM employee WHERE empname = 'John';
  IF NOT FOUND THEN
    RAISE EXCEPTION 'employee John not found';
  END IF;
END;
$$ LANGUAGE plpgsql;

Nel gestore delle eccezioni, la variabile speciale SQLSTATE contiene il codice di errore che corrisponde all'eccezione che è stata generata. La variabile speciale SQLERRM contiene il messaggio di errore associato all'eccezione. Queste variabili non sono definite al di fuori dei gestori delle eccezioni e, se utilizzate, generano un errore.

Di seguito viene riportato un esempio.

CREATE OR REPLACE PROCEDURE sqlstate_sqlerrm() AS
$$
BEGIN
  UPDATE employee SET firstname = 'Adam' WHERE lastname = 'Smith';
  EXECUTE 'select invalid';
  EXCEPTION WHEN OTHERS THEN
  RAISE INFO 'error message SQLERRM %', SQLERRM;
  RAISE INFO 'error message SQLSTATE %', SQLSTATE;
END;
$$ LANGUAGE plpgsql;               

ROW_COUNT viene utilizzato con il comando GET DIAGNOSTICS. Mostra il numero di righe elaborate dall'ultimo comando SQL inviato al motore SQL.

Di seguito viene riportato un esempio.

CREATE OR REPLACE PROCEDURE sp_row_count() AS
$$
DECLARE
  integer_var int;
BEGIN
  INSERT INTO tbl_row_count VALUES(1);
  GET DIAGNOSTICS integer_var := ROW_COUNT;
  RAISE INFO 'rows inserted = %', integer_var;
END;
$$ LANGUAGE plpgsql;               

Tipi di record

Un tipo RECORD non è un tipo di dati true, solo un segnaposto. Le variabili tipo di record assumono la struttura della riga attuale della riga alla quale sono assegnate durante un comando SELECT o FOR. La sottostruttura di una variabile di record può cambiare ogni volta che le viene assegnato un valore. Fino a quando una variabile di record non viene assegnata, non ha sottostruttura. Ogni tentativo di accedere un campo in essa genera un errore di runtime.

name RECORD;

Di seguito viene riportato un esempio.

CREATE TABLE tbl_record(a int, b int);
INSERT INTO tbl_record VALUES(1, 2);
CREATE OR REPLACE PROCEDURE record_example()
LANGUAGE plpgsql
AS $$
DECLARE
  rec RECORD;
BEGIN
  FOR rec IN SELECT a FROM tbl_record
  LOOP
    RAISE INFO 'a = %', rec.a;
  END LOOP;
END;
$$;