[successivo] [precedente] [inizio] [fine] [indice generale] [indice ridotto] [indice analitico] [volume] [parte]


Capitolo 605.   Le funzioni e i grilletti in PostgreSQL: un'esercitazione didattica

Questo capitolo deriva da un lavoro originale di Antonio Bernardi <toni (ad) swlibero·org> che ne ha concesso espressamente l'utilizzo libero all'interno di questa opera. Il testo originale di questo lavoro si trova presso <http://linuxdidattica.org/docs/besta/le_funzioni_e_trigger.html>.

605.1   Lo scenario: la base di dati «biblioteca»

L'esercitazione che viene proposta, riguarda la gestione dei prestiti di una biblioteca. Le relazioni coinvolte sono:

Le relazioni sono ovviamente ridotte all'osso e sono autoesplicative. In questo esercizio la relazione fondamentale è quella denominata prestiti, nella quale si trovano il codice dell'utente (cod_ut) e il numero di inventario del libro (n_inv). Quando viene fatto un prestito l'impiegato deve inserire:

A questo punto, per evitare errori e non immettere un libro che è già a prestito, si deve fare un controllo che impedisca tale inserimento. Nella relazione prestiti vi è il campo data_r che riporta la data di rientro di un libro. Quando un libro viene dato a prestito questa data viene inserita in modo predefinito usando un valore assurdo (2050.01.01) che serve a indicare che il libro è a prestito. Conseguentemente, quando si inserisce un prestito nuovo, se nella relazione prestiti esiste una tupla con un attributo n_inv uguale a quello che si vuole inserire e data_r equivalente al valore convenzionale indicante che il libro è a prestito, il DBMS deve avvisare dell'errore.

Nell'esercitazione seguente questo controllo viene fatto in due modi differenti: prima con l'utilizzo di una funzione, poi con l'utilizzo di un grilletto (trigger).

605.2   In classe: al lavoro!

Si immagina di avere un'unico elaboratore, nel quale sia già installato PostgreSQL.

Per prima cosa ci si deve collegare all'elaboratore GNU/Linux, come utente postgres, per creare la base di dati:

postgres$ createdb biblioteca[Invio]

Successivamente ci si connette alla base di dati biblioteca con il programma cliente psql per creare le relazioni:

postgres$ psql -h localhost -d biblioteca -U postgres[Invio]

biblioteca=> CREATE TABLE localita ([Invio]

biblioteca-> citta CHAR(20) PRIMARY KEY,[Invio]

biblioteca-> cap CHAR(5),[Invio]

biblioteca-> prov CHAR(2),[Invio]

biblioteca-> naz CHAR(3)[Invio]

biblioteca-> );[Invio]

biblioteca=> CREATE TABLE editori ([Invio]

biblioteca-> cod_ut CHAR(3) PRIMARY KEY,[Invio]

biblioteca-> nome CHAR(10),[Invio]

biblioteca-> cognome CHAR(10),[Invio]

biblioteca-> telefono CHAR(11),[Invio]

biblioteca-> indirizzo CHAR(20),[Invio]

biblioteca-> citta CHAR(20) REFERENCES localita[Invio]

biblioteca-> );[Invio]

biblioteca=> CREATE TABLE libri ([Invio]

biblioteca-> n_inv CHAR(5) PRIMARY KEY,[Invio]

biblioteca-> autore CHAR(25),[Invio]

biblioteca-> titolo CHAR(35) NOT NULL,[Invio]

biblioteca-> collocazione CHAR(15),[Invio]

biblioteca-> soggetto CHAR(11),[Invio]

biblioteca-> cod_ed CHAR(5) REFERENCES editori,[Invio]

biblioteca-> prezzo INTEGER,[Invio]

biblioteca-> anno_ed CHAR(4)[Invio]

biblioteca-> );[Invio]

biblioteca=> CREATE TABLE utenti ([Invio]

biblioteca-> cod_ut CHAR(5) PRIMARY KEY,[Invio]

biblioteca-> nome CHAR(10),[Invio]

biblioteca-> cognome CHAR(10),[Invio]

biblioteca-> telefono CHAR(11),[Invio]

biblioteca-> indirizzo CHAR(20),[Invio]

biblioteca-> citta CHAR(20) REFERENCES localita[Invio]

biblioteca-> );[Invio]

biblioteca=> CREATE TABLE prestiti ([Invio]

biblioteca-> np serial,[Invio]

biblioteca-> n_inv CHAR(5) REFERENCES libri,[Invio]

biblioteca-> cod_ut CHAR(5) REFERENCES utenti,[Invio]

biblioteca-> data_p DATE CHECK (data_p <= data_r),[Invio]

biblioteca-> data_r DATE DEFAULT '2050.1.1'[Invio]

biblioteca-> );[Invio]

A questo punto si vanno a popolare le relazioni (esclusa la relazione prestiti): qui bisogna fare attenzione, inserendo prima i dati delle relazioni che non hanno chiavi esterne; successivamente inserendo quelle relazioni che fanno riferimento alle prime tramite chiavi esterne. Per esempio, è necessario popolare la relazione localita prima della relazione editori.

biblioteca=> INSERT INTO localita (citta, cap, prov, naz)[Invio]

biblioteca-> VALUES ('TREVISO', '31100', 'TV', 'I');[Invio]

biblioteca=> INSERT INTO localita (citta, cap, prov, naz)[Invio]

biblioteca-> VALUES ('PADOVA', '35100', 'PD', 'I');[Invio]

biblioteca=> INSERT INTO localita (citta, cap, prov, naz)[Invio]

biblioteca-> VALUES ('MILANO', '20100', 'MI', 'I');[Invio]

biblioteca=> INSERT INTO editori (cod_ed, rag_soc, indirizzo, \
  \citta, telefono)
[Invio]

biblioteca-> VALUES ('1', 'CEDAM SPA', 'VIA JAPPELLI 5/6', \
  \'PADOVA', '049-8239111');
[Invio]

biblioteca=> INSERT INTO editori (cod_ed, rag_soc, indirizzo, \
  \citta, telefono)
[Invio]

biblioteca-> VALUES ('2', 'ELEMOND SPA', 'VIA ROMA 17', \
  \'MILANO', '02-7820012');
[Invio]

biblioteca=> INSERT INTO utenti (cod_ut, nome, cognome, \
  \telefono, indirizzo, citta)
[Invio]

biblioteca-> VALUES ('1', 'LUCA', 'BONALDO', '0422-401582', \
  \'VIA CORNARE 14', 'TREVISO');
[Invio]

biblioteca=> INSERT INTO utenti (cod_ut, nome, cognome, \
  \telefono, indirizzo, citta)
[Invio]

biblioteca-> VALUES ('2', 'LUIGI', 'GOBBO', '049-458270', \
  \'VIA MANIN 72', 'PADOVA');
[Invio]

biblioteca=> INSERT INTO utenti (cod_ut, nome, cognome, \
  \telefono, indirizzo, citta)
[Invio]

biblioteca-> VALUES ('3', 'SIMONE', 'PRIAMO', '0422-478791', \
  \'VIALE M.GRAPPA 1', 'TREVISO');
[Invio]

biblioteca=> INSERT INTO utenti (cod_ut, nome, cognome, \
  \telefono, indirizzo, citta)
[Invio]

biblioteca-> VALUES ('4', 'MAURO', 'MENEGAZZI', '049-987756', \
  \'VIA EVEREST 7', 'PADOVA');
[Invio]

biblioteca=> INSERT INTO libri (n_inv, autore, titolo, \
  \collocazione, soggetto, cod_ed, prezzo, anno_ed)
[Invio]

biblioteca-> VALUES ('1', 'STELLIO MARTELLI', \
  \'RACCONTI MITOLOGICI', 'X.1.1', 'STORICO', '1', 7000, '1992');
[Invio]

biblioteca=> INSERT INTO libri (n_inv, autore, titolo, \
  \collocazione, soggetto, cod_ed, prezzo, anno_ed)
[Invio]

biblioteca-> VALUES ('2', 'HECTOR MALOT', 'SENZA FAMIGLIA', \
  \'X.1.2', 'DRAMMATICO', '2', 14000, '1990');
[Invio]

biblioteca=> INSERT INTO libri (n_inv, autore, titolo, \
  \collocazione, soggetto, cod_ed, prezzo, anno_ed)
[Invio]

biblioteca-> VALUES ('3', 'LOUISE MAY ALCOTT', \
  \'PICCOLE DONNE CRESCONO', 'X.1.3', 'ROMANTICO', \
  \'1', 10000, '1991');
[Invio]

biblioteca=> INSERT INTO libri (n_inv, autore, titolo, \
  \collocazione, soggetto, cod_ed, prezzo, anno_ed)
[Invio]

biblioteca-> VALUES ('4', 'MARY E. MAPES DODGE', \
  \'PATTINI D ARGENTO', 'X.1.4', 'FANTASTICO', \
  \'2', 13000, '1987');
[Invio]

A questo punto se si inseriscono i dati nella relazione prestiti ci si può trovare nella situazione di avere a prestito lo stesso libro più volte contemporaneamente. Naturalmente si riesce a sperimentare facilmente tale situazione con qualche prova.

Per esempio, se viene digitato quanto segue, si inserisce una tupla nella relazione prestiti:

biblioteca=> INSERT INTO prestiti (n_inv, cod_ut, data_p)[Invio]

biblioteca-> VALUES ('2','3', '2001.1.1');[Invio]

Se si digita ancora l'istruzione seguente ci si trova con un libro che è dato a prestito all'utente di codice 3 e contemporaneamente all'utente di codice 1, il che è assurdo, assumendo il fatto che un libro non possa essere preso a prestito da più utenti, contemporaneamente:

biblioteca=> INSERT INTO prestiti (n_inv, cod_ut, data_p)[Invio]

biblioteca-> VALUES ('2','1', '2001.1.1');[Invio]

La soluzione proposta utilizza le funzioni e i grilletti di PostgreSQL. Per la realizzazione delle funzioni si mostra qui l'uso del linguaggio Plpgsql, che prima di poter essere utilizzato deve essere associato esplicitamente. Questa operazione richiede l'uso del comando createlang, come si vede nell'esempio seguente:

postgres$ createlang plpgsql -h localhost -d biblioteca \
  \                            --pglib=/usr/lib/pgsql
[Invio]

In questo caso, si intende che il file plpgsql.so sia contenuto nella directory /usr/lib/pgsql/.

605.2.1   Le funzioni

Successivamente si passa alla scrittura della funzione che viene mostrata sotto, con l'aiuto di un programma per la creazione e la modifica di file di testo (come VI per esempio), generando il file funzione_controlla.plpgsql (volendo rimanere nell'ambito di psql, si può usare il comando \! per avviare temporaneamente il programma di creazione e modifica dei file di testo).

create function inserisci_prestito(char(5), char(5), date)
returns boolean
     as 'declare
         numero_inventario alias for $1;
         codice_utente alias for $2;
         data_prestito alias for $3;
         data_restituzione date;
         prestito record;
         begin
            data_restituzione:=''2050.1.1'';
            select into prestito *
            from prestiti
            where n_inv=numero_inventario and
            data_r=data_restituzione;
            if found
               then
                  raise exception \'il libro è già a prestito\';
                  return ''f'';
               else
                  insert into prestiti (n_inv, cod_ut, data_p)
                  values (numero_inventario, codice_utente, data_prestito);
                  return ''t'';
            end if;
         end;'
language 'plpgsql';

Inizialmente si assegnano alle variabili numero_inventario, codice_utente e data_prestito i valori corrispondenti n_inv, cod_ut e data_p. Successivamente viene definita la variabile data_restituzione, di tipo DATE, alla quale viene assegnato il valore sentinella 2050.1.1; quindi la variabile prestito, di tipo record, che deve contenere la tupla letta dalla relazione prestiti, nel caso la lettura vada a buon fine con l'istruzione:

SELECT INTO prestito * FROM prestiti WHERE...

Sostanzialmente si legge la relazione prestiti e se si trova una tupla che soddisfa la condizione di uguaglianza tra n_inv e il numero di inventario del libro che si vuole dare a prestito e tra la data_r e la data fittizia del 2050.01.01, significa che il libro è già a prestito.

Se questa tupla viene trovata (con la condizione if found), la funzione deve uscire dal blocco begin-end ed emettere un avviso che il libro è già a prestito, altrimenti deve inserire la tupla in oggetto nella relazione prestiti con l'istruzione seguente con i dati passati dalla funzione:

INSERT INTO prestiti ...

Una volta scritto il file della funzione, si deve acquisirne il codice con il comando seguente, nell'ambito di psql:

biblioteca=> \i funzione_controlla.plpgsql[Invio]

Se nel frattempo la relazione prestiti contiene tuple senza senso, conviene azzerarla completamente, prima di mettere in pratica l'uso della nuova funzione di controllo:

biblioteca=> DELETE FROM prestiti;[Invio]

Per l'uso vero e proprio della funzione, si interviene come nell'esempio seguente:

biblioteca=> SELECT inserisci_prestito ('2','3', \
  \cast '2001.1.1' as DATE);
[Invio]

A questo punto, se si tenta di inserire per due volte lo stesso prestito, la funzione impedisce l'operazione e avvisa dell'errore.

605.2.2   I grilletti e le funzioni

L'utilizzo dell'istruzione SELECT abbinata a una funzione può creare qualche confusione. Si può superare questo problema utilizzando un grilletto che richiami automaticamente una funzione di controllo. Quello che segue è l'esempio di tale funzione corrispondente al file funzione_trigger.plpgsql.

create function inserisci_prestito_trigger()
returns opaque
   as 'declare
     numero_inventario char(5);
     data_restituzione date;
     prestito record;
        begin
        numero_inventario:=new.n_inv;
        data_restituzione:=''2050.1.1'';
           select into prestito *
           from prestiti
           where n_inv=numero_inventario and
           data_r=data_restituzione;
              if found
                 then
                    raise exception \'il libro è già a prestito\';
                 else
                    return new;
               end if;
        end;'
language 'plpgsql';

create trigger controlla_libro_uscito
before insert
on prestiti
for each row
execute procedure inserisci_prestito_trigger();

Nel file in questione, si vede la dichiarazione di una funzione analoga a quanto già mostrato in precedenza, seguita dalla dichiarazione del grilletto relativo.

La variabile new corrisponde alla nuova tupla che si vuole inserire con l'istruzione INSERT INTO, ed è di tipo RECORD.

Dopo averle dichiarate, si assegna alla variabile numero_inventario il valore new.n_inv e alla variabile data_restituzione il valore sentinella 2050.1.1. Successivamente con l'istruzione seguente si va a vedere se nella relazione prestiti esiste una tupla che soddisfa la condizione di esistenza del libro a prestito. Se si trova questa tupla viene mostrato un messaggio di errore, altrimenti la funzione deve restituire il valore contenuto nella variabile new, ovvero la tupla che viene inserita nella relazione.

SELECT INTO prestito * ...

La funzione inserisci_prestito_trigger() viene messa in azione, ogni volta che si vuole inserire una tupla nel file prestiti, attraverso il controllo del grilletto controlla_libro_uscito.

Si acquisisce la funzione e il grilletto con il comando seguente, nell'ambito di psql:

biblioteca=> \i funzione_trigger.plpgsql[Invio]

A questo punto per inserire un libro a prestito si utilizza l'istruzione standard:

biblioteca=> INSERT INTO prestiti (n_inv, cod_ut, data_p) \
  \VALUES ('3','2','2001.10.10');
[Invio]

Se il libro non è già a prestito, si ottiene la segnalazione standard del fatto che il libro è stato inserito. Se si ritenta l'inserimento di un prestito con lo stesso numero di inventario, si ottiene solo la segnalazione di errore prevista.

biblioteca=> INSERT INTO prestiti (n_inv, cod_ut, data_p) \
  \VALUES ('3','3','2001.10.10');
[Invio]

ERROR: il libro è già a prestito

605.3   Riferimenti

Appunti di informatica libera 2007.02 --- Copyright © 2000-2007 Daniele Giacomini -- <daniele (ad) swlibero·org>


Dovrebbe essere possibile fare riferimento a questa pagina anche con il nome le_funzioni_e_i_grilletti_in_postgresql_un_x0027_esercitazio.htm

[successivo] [precedente] [inizio] [fine] [indice generale] [indice ridotto] [indice analitico]

Valid ISO-HTML!

CSS validator!