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

Capitolo 602.   PostgreSQL: il linguaggio

PostgreSQL è un ORDBMS, ovvero un Object-relational DBMS, cioè un DBMS relazionale a oggetti. La sua documentazione utilizza terminologie differenti, a seconda delle preferenze dei rispettivi autori. In generale si possono distinguere tre modalità, riferite a tre punti di vista: la programmazione a oggetti, la teoria generale sui DBMS e il linguaggio SQL. Le equivalenze dei termini sono riassunte dallo schema seguente:

In questo capitolo si intende usare la terminologia tradizionale dei DBMS, dove i dati sono organizzati in relazioni, tuple e attributi, affiancando eventualmente i termini del linguaggio SQL tradizionale (tabelle, righe e colonne). Inoltre, la sintassi delle istruzioni (interrogazioni) SQL che vengono mostrate è limitata alle funzionalità più semplici, sempre compatibilmente con le possibilità di PostgreSQL. Per una visione più estesa delle funzionalità SQL di PostgreSQL conviene consultare la sua documentazione.

602.1   Prima di iniziare

Per fare pratica con il linguaggio SQL, il modo migliore è quello di utilizzare il programma psql con il quale si possono eseguire interrogazioni interattive con il servente. Quello che conta è tenere a mente che per poterlo utilizzare occorre avere già creato una base di dati (vuota), in cui vanno poi inserite delle nuove relazioni, con le quali si possono eseguire altre operazioni.

Attraverso le istruzioni SQL si fa riferimento sempre a un'unica base di dati: quella a cui ci si collega quando si avvia psql.

602.2   Tipi di dati e rappresentazione

I tipi di dati gestibili sono un punto delicato della compatibilità tra un DBMS e lo standard SQL. Vale la pena di riepilogare i tipi più comuni, compatibili con lo standard SQL, che possono essere trovati nella tabella 602.2.

CHAR

CHARACTER

CHAR(n)

CHARACTER(n)

VARCHAR(n)

CHARACTER VARYING(n)

CHAR VARYING(n)

INTEGER

SMALLINT

FLOAT

FLOAT(n)

REAL

DOUBLE PRECISION

NUMERIC

NUMERIC(precisione[,scala])

DECIMAL

DECIMAL(precisione[,scala])

DEC

DEC(precisione[,scala])

DATE

TIME

TIMESTAMP

INTERVAL

BIT(n)

BIT VARYING(n)

BOOLEAN

Oltre ai tipi di dati gestibili, è necessario conoscere il modo di rappresentarli in forma costante. In particolare, è bene osservare che PostgreSQL ammette solo l'uso degli apici singoli come delimitatori; pertanto, per rappresentare un apice in una stringa delimitata in questo modo, lo si può raddoppiare, oppure si può usare la sequenza di escape \'. La tabella 602.3 mostra alcuni esempi.

CHAR

CHARACTER

CHAR(n)

CHARACTER(n)

VARCHAR(n)

CHARACTER VARYING(n)

CHAR VARYING(n)

'a'

'ciao'

'Ciao'

'123/der:876'

INTEGER

SMALLINT

1

123

-987

FLOAT

FLOAT(n)

REAL

DOUBLE PRECISION

NUMERIC

NUMERIC(precisione[,scala])

DECIMAL

DECIMAL(precisione[,scala])

DEC

DEC(precisione[,scala])

123.45

-45.3

123.45e+10

123.45e-10

DATE

DATE '31.12.2007'

DATE '12/31/2007'

DATE '2007-12-31'

TIME

TIME '15:55:27'

TIME '15:59'

TIMESTAMP

TIMESTAMP '2007-12-31 15:55:27'

TIMESTAMP '2007-12-31 15:55:27+1'

INTERVAL

INTERVAL '15:55:27'

INTERVAL '15 HOUR 59 MINUTE'

INTERVAL '- 15 HOUR'

BIT

BIT VARYING(n)

B'1'

B'101'

X'2F'

BOOLEAN

1

'y'

'yes'

't'

'true'

0

'n'

'no'

'f'

'false'

In particolare, le costanti stringa possono contenere delle sequenze di escape, rappresentate da una barra obliqua inversa seguita da un simbolo. La tabella 602.4 mostra le sequenze di escape tipiche e inserisce anche il caso del raddoppio degli apici singoli.

\n

\r

\b

\'

''

\"

\\

\%

\_

602.3   Funzioni

PostgreSQL, come altri DBMS SQL, offre una serie di funzioni che fanno parte dello standard SQL, assieme ad altre non standard che però sono ampiamente diffuse e di grande utilità. Le tabelle 602.5 e 602.6 ne riportano alcune.

POSITION(stringa_1 IN stringa_2)

SUBSTRING(stringa [FROM n] [FOR m])

TRIM([LEADING|TRAILING|BOTH] ['x'] \
  \FROM [stringa])

UPPER(stringa)

LOWER(stringa)

INITCAP(stringa)

SUBSTR(stringa,n,m)

LTRIM(stringa, 'x')

RTRIM(stringa, 'x')

Segue la descrizione di alcuni esempi.

• SELECT POSITION( 'o' IN 'Topo' )

  Restituisce il valore due.

• SELECT POSITION( 'ino' IN Cognome ) FROM Indirizzi

  Restituisce un elenco delle posizioni in cui si trova la stringa ino all'interno dell'attributo Cognome, per tutte le tuple della relazione Indirizzi.

• SELECT SUBSTRING( 'Daniele' FROM 3 FOR 2 )

  Restituisce la stringa ni.

• SELECT TRIM( LEADING '*' FROM '*****Ciao****' )

  Restituisce la stringa Ciao****.

• SELECT TRIM( TRAILING '*' FROM '*****Ciao****' )

  Restituisce la stringa *****Ciao.

• SELECT TRIM( BOTH '*' FROM '*****Ciao****' )

  Restituisce la stringa Ciao.

• SELECT TRIM( BOTH ' ' FROM ' Ciao ' )

  Restituisce la stringa Ciao.

• SELECT TRIM( ' Ciao ' )

  Esattamente come nell'esempio precedente, dal momento che lo spazio normale è il carattere predefinito e che la parola chiave BOTH è anche predefinita.

• SELECT LTRIM( '*****Ciao****', '*' )

  Restituisce la stringa Ciao****.

• SELECT RTRIM( '*****Ciao****', '*' )

  Restituisce la stringa *****Ciao.

602.4   Esempi comuni

Nelle sezioni seguenti vengono mostrati alcuni esempi comuni di utilizzo del linguaggio SQL, limitato alle possibilità di PostgreSQL. La sintassi non viene descritta, salvo quando la differenza tra quella standard e quella di PostgreSQL è importante.

Negli esempi si fa riferimento frequentemente a una relazione di indirizzi, il cui contenuto è visibile nella figura 602.17.

602.4.1   Creazione di una relazione

La relazione di esempio mostrata nella figura 602.17, potrebbe essere creata nel modo seguente:

CREATE TABLE Indirizzi (
        Codice          integer,
        Cognome         char(40),
        Nome            char(40),
        Indirizzo       varchar(60),
        Telefono        varchar(40)
    );

Quando si inseriscono i valori per una tupla, può capitare che venga omesso l'inserimento di alcuni attributi. In questi casi, il campo corrispondente riceve il valore NULL, cioè un valore indefinito, oppure il valore predefinito attraverso quanto specificato con l'espressione che segue la parola chiave DEFAULT.

In alcuni casi non è possibile definire un valore predefinito e nemmeno è accettabile che un dato resti indefinito. In tali situazioni si può aggiungere l'opzione NOT NULL, dopo la definizione del tipo.

602.4.2   Modifica della relazione

La modifica di una relazione implica l'intervento sulle caratteristiche degli attributi, oppure la loro aggiunta ed eliminazione. Seguono due esempi, con cui si aggiunge un attributo e poi lo si elimina:

ALTER TABLE Indirizzi ADD COLUMN Comune char(30);

ALTER TABLE Indirizzi DROP COLUMN Comune;

L'esempio seguente modifica il tipo di un attributo già esistente:

ALTER TABLE Indirizzi ALTER COLUMN Codice TYPE REAL;

Naturalmente, la conversione del tipo di un attributo può avere significato solo se i valori contenuti nelle tuple esistenti, in corrispondenza di quell'attributo, sono convertibili.

602.4.3   Inserimento dati in una relazione

L'esempio seguente mostra l'inserimento dell'indirizzo dell'impiegato «Pinco Pallino».

INSERT INTO Indirizzi
    VALUES (
        01,
        'Pallino',
        'Pinco',
        'Via Biglie 1',
        '0222,222222'
    );

In questo caso, si presuppone che i valori inseriti seguano la sequenza degli attributi, così come è stata creata la relazione in origine. Se si vuole indicare un comando più leggibile, occorre aggiungere l'indicazione della sequenza degli attributi da compilare, come nell'esempio seguente:

INSERT INTO Indirizzi (
        Codice,
        Cognome,
        Nome,
        Indirizzo,
        Telefono
    )
    VALUES (
        01,
        'Pallino',
        'Pinco',
        'Via Biglie 1',
        '0222,222222'
    );

In questo stesso modo, si può evitare di compilare il contenuto di un attributo particolare, indicando espressamente solo gli attributi che si vogliono fornire; in tal caso gli altri attributi ricevono il valore predefinito o NULL in mancanza d'altro. Nell'esempio seguente viene indicato solo il codice e il nominativo:

INSERT INTO Indirizzi (
        Codice,
        Cognome,
        Nome,
    )
    VALUES (
        01,
        'Pallino'
        'Pinco',
    );

602.4.4   Eliminazione di una relazione

Una relazione può essere eliminata completamente attraverso l'istruzione DROP. L'esempio seguente elimina la relazione degli indirizzi degli esempi già mostrati:

DROP TABLE Indirizzi;

602.4.5   Interrogazioni semplici

L'esempio seguente emette tutto il contenuto della relazione degli indirizzi già vista negli esempi precedenti:

SELECT * FROM Indirizzi;

Seguendo l'esempio fatto in precedenza si dovrebbe ottenere l'elenco riportato sotto, equivalente a tutto il contenuto della relazione.

codice  cognome   nome       indirizzo     telefono

     1  Pallino   Pinco      Via Biglie 1  0222,222222
     2  Tizi      Tizio      Via Tazi 5    0555,555555
     3  Cai       Caio       Via Caini 1   0888,888888
     4  Semproni  Sempronio  Via Sempi 7   0999,999999

Per ottenere un elenco ordinato in base al cognome e al nome (in caso di ambiguità), lo stesso comando si completa nel modo seguente:

SELECT * FROM Indirizzi ORDER BY Cognome, Nome;

codice  cognome   nome       indirizzo     telefono

     3  Cai       Caio       Via Caini 1   0888,888888
     1  Pallino   Pinco      Via Biglie 1  0222,222222
     4  Semproni  Sempronio  Via Sempi 7   0999,999999
     2  Tizi      Tizio      Via Tazi 5    0555,555555

La selezione degli attributi permette di ottenere un risultato che contenga solo quelli desiderati, permettendo anche di cambiarne l'intestazione. L'esempio seguente permette di mostrare solo i nominativi e il telefono, cambiando un po' le intestazioni:

SELECT Cognome as cognomi, Nome as nomi, Telefono as numeri_telefonici
    FROM Indirizzi;

Quello che si ottiene è simile all'elenco seguente:

cognomi   nomi       numeri_telefonici

Pallino   Pinco      0222,222222
Tizi      Tizio      0555,555555
Cai       Caio       0888,888888
Semproni  Sempronio  0999,999999

La selezione delle tuple può essere fatta attraverso la condizione che segue la parola chiave WHERE. Nell'esempio seguente vengono selezionate le tuple in cui l'iniziale dei cognomi è compresa tra N e T.

SELECT * FROM Indirizzi WHERE Cognome >= 'N' AND Cognome <= 'T';

Dall'elenco che si ottiene, si osserva che Caio è stato escluso:

codice  cognome   nome       indirizzo     telefono

     1  Pallino   Pinco      Via Biglie 1  0222,222222
     2  Tizi      Tizio      Via Tazi 5    0555,555555
     4  Semproni  Sempronio  Via Sempi 7   0999,999999

Per evitare ambiguità possono essere indicati i nomi degli attributi prefissati dal nome della relazione a cui appartengono, separando le due parti con l'operatore punto (.). Nell'esempio seguente si selezionano solo il cognome, il nome e il numero telefonico, specificando il nome della relazione a cui appartengono gli attributi:

SELECT Indirizzi.Cognome, Indirizzi.Nome, Indirizzi.Telefono
    FROM Indirizzi;

Ecco il risultato:

cognome   nome       telefono

Pallino   Pinco      0222,222222
Tizi      Tizio      0555,555555
Cai       Caio       0888,888888
Semproni  Sempronio  0999,999999

602.4.6   Interrogazioni simultanee di più relazioni

Se dopo la parola chiave FROM si indicano più relazioni (ciò vale anche se si indica più volte la stessa relazione), si intende fare riferimento a una relazione generata dal «prodotto» di queste. Si immagini di abbinare alla relazione Indirizzi la relazione Presenze contenente i dati visibili nella figura 602.36.

Come si può intendere, il primo attributo, Codice, serve a identificare la persona per la quale è stata fatta l'annotazione dell'ingresso e dell'uscita. Tale codice viene interpretato in base al contenuto della relazione Indirizzi. Si immagini di volere ottenere un elenco contenente tutti gli ingressi e le uscite, indicando chiaramente il cognome e il nome della persona a cui si riferiscono.

SELECT
    Presenze.Giorno,
    Presenze.Ingresso,
    Presenze.Uscita,
    Indirizzi.Cognome,
    Indirizzi.Nome
    FROM Presenze, Indirizzi
    WHERE Presenze.Codice = Indirizzi.Codice;

Ecco quello che si dovrebbe ottenere:

giorno      ingresso  uscita    cognome   nome

01-01-2007  07:30:00  13:30:00  Pallino   Pinco
01-01-2007  07:35:00  13:37:00  Tizi      Tizio
01-01-2007  07:45:00  14:00:00  Cai       Caio     
01-01-2007  08:30:00  16:30:00  Semproni  Sempronio
01-02-2007  07:35:00  13:38:00  Pallino   Pinco
01-02-2007  08:35:00  14:37:00  Tizio     Tizi
01-02-2007  07:40:00  13:30:00  Semproni  Sempronio

602.4.7   Alias

Una stessa relazione può essere presa in considerazione come se si trattasse di due o più relazioni differenti. Per distinguere tra questi punti di vista diversi, si devono usare degli alias, che sono in pratica dei nomi alternativi. Gli alias si possono usare anche solo per questioni di leggibilità. L'esempio seguente è la semplice ripetizione di quello mostrato nella sezione precedente, con l'aggiunta però della definizione degli alias Pre e Nom.

SELECT
    Pre.Giorno,
    Pre.Ingresso,
    Pre.Uscita,
    Nom.Cognome,
    Nom.Nome
    FROM Presenze AS Pre, Indirizzi AS Nom
    WHERE Pre.Codice = Nom.Codice;

602.4.8   Viste

Attraverso una vista, è possibile definire una relazione virtuale:

CREATE VIEW Presenze_dettagliate AS
SELECT
    Presenze.Giorno,
    Presenze.Ingresso,
    Presenze.Uscita,
    Indirizzi.Cognome,
    Indirizzi.Nome
    FROM Presenze, Indirizzi
    WHERE Presenze.Codice = Indirizzi.Codice;

L'esempio mostra la creazione della vista Presenze_dettagliate, ottenuta dalle relazioni Presenze e Indirizzi. In pratica, questa vista permette di interrogare direttamente la relazione virtuale Presenze_dettagliate, invece di utilizzare ogni volta un comando SELECT molto complesso, per ottenere lo stesso risultato.

602.4.9   Aggiornamento delle tuple

La modifica di tuple già esistenti avviene attraverso l'istruzione UPDATE, la cui efficacia viene controllata dalla condizione posta dopo la parola chiave WHERE. Se tale condizione manca, l'effetto delle modifiche si riflette su tutte le tuple della relazione.

L'esempio seguente, aggiunge un attributo alla relazione degli indirizzi, per contenere il nome del comune di residenza degli impiegati; successivamente viene inserito il nome del comune Sferopoli in base al prefisso telefonico.

ALTER TABLE Indirizzi ADD COLUMN Comune char(30);

UPDATE Indirizzi
    SET Comune='Sferopoli'
    WHERE Telefono >= '022' AND Telefono < '023';

In pratica, viene aggiornata solo la tupla dell'impiegato Pinco Pallino.

602.4.10   Cancellazione delle tuple

L'esempio seguente elimina dalla relazione delle presenze le tuple riferite alle registrazioni del giorno 01/01/2007 e le eventuali antecedenti.

DELETE FROM Presenze WHERE Giorno <= '01/01/2007';

602.4.11   Creazione di una nuova relazione a partire da altre

L'esempio seguente crea la relazione mia_prova dalla fusione della relazioni degli indirizzi e delle presenze, come già mostrato in un esempio precedente:

SELECT
    Presenze.Giorno,
    Presenze.Ingresso,
    Presenze.Uscita,
    Indirizzi.Cognome,
    Indirizzi.Nome
    INTO TABLE mia_prova
    FROM Presenze, Indirizzi
    WHERE Presenze.Codice = Indirizzi.Codice;

602.4.12   Inserimento in una relazione esistente

L'esempio seguente aggiunge alla relazione dello storico delle presenze le registrazioni vecchie che poi vengono cancellate.

INSERT INTO PresenzeStorico (
        PresenzeStorico.Codice,
        PresenzeStorico.Giorno,
        PresenzeStorico.Ingresso,
        PresenzeStorico.Uscita
    )
    SELECT
        Presenze.Codice,
        Presenze.Giorno,
        Presenze.Ingresso,
        Presenze.Uscita
        FROM Presenze
        WHERE Presenze.Giorno <= '2007/01/01';

DELETE FROM Presenze WHERE Giorno <= '2007/01/01';

602.4.13   Controllare gli accessi a una relazione

Quando si creano delle relazioni in una base di dati, tutti gli altri utenti che sono stati registrati nel sistema del DBMS, potrebbero accedervi e fare le modifiche che vogliono. Per controllare questi accessi, l'utente proprietario delle relazioni (di solito è colui che le ha create), può usare le istruzioni GRANT e REVOKE. La prima permette a un gruppo di utenti di eseguire operazioni determinate, la seconda toglie dei privilegi.

GRANT {ALL | SELECT | INSERT | UPDATE | DELETE | RULE}[,...]
    ON relazione[,...]
    TO {PUBLIC | GROUP gruppo | utente}

REVOKE {ALL | SELECT | INSERT | UPDATE | DELETE | RULE}[,...]
    ON relazione[,...]
    FROM {PUBLIC | GROUP gruppo | utente}

La sintassi delle due istruzioni è simile, basta fare attenzione a cambiare la parola chiave TO con FROM. I gruppi e gli utenti sono nomi che fanno riferimento a quanto registrato all'interno del DBMS.

L'esempio seguente toglie a tutti gli utenti (PUBLIC) tutti i privilegi sulle relazioni delle presenze e degli indirizzi; successivamente vengono ripristinati tutti i privilegi solo per l'utente tizio:

REVOKE ALL
    ON Presenze, Indirizzi
    FROM PUBLIC;

GRANT ALL
    ON Presenze, Indirizzi
    TO tizio;

602.5   Controllo delle transazioni

La gestione delle transazioni richiede che queste siano introdotte dall'istruzione START TRANSACTION:

START TRANSACTION

L'esempio seguente mostra il caso in cui si voglia isolare l'inserimento di una tupla nella relazione Indirizzi all'interno di una transazione, che alla fine viene confermata regolarmente con l'istruzione COMMIT:

START TRANSACTION;

INSERT INTO Indirizzi
    VALUES (
        05,
        'De Pippo',
        'Pippo',
        'Via Pappo, 5',
        '0333,3333333'
    );

COMMIT;

Nell'esempio seguente, si rinuncia all'inserimento della tupla con l'istruzione ROLLBACK finale:

START TRANSACTION;

INSERT INTO Indirizzi
    VALUES (
        05,
        'De Pippo',
        'Pippo',
        'Via Pappo, 5',
        '0333,3333333'
    );

ROLLBACK;

602.6   Cursori

La gestione dei cursori da parte di PostgreSQL è abbastanza compatibile con lo standard, a parte il fatto che avviene fuori dal contesto previsto, che viene consentito un accesso in sola lettura e che non è possibile assegnare i dati a delle variabili.

La dichiarazione di un cursore si ottiene nel modo solito, con la differenza che questa deve avvenire esplicitamente in una transazione. In particolare, con PostgreSQL, il cursore viene aperto automaticamente nel momento della dichiarazione, per cui l'istruzione OPEN non è disponibile.

START TRANSACTION;

DECLARE Mio_cursore INSENSITIVE CURSOR FOR
    SELECT * FROM Indirizzi ORDER BY Cognome, Nome;

-- L'apertura del cursore non esiste in PostgreSQL
-- OPEN Mio_cursore;
...

L'esempio mostra la dichiarazione dell'inizio di una transazione, assieme alla dichiarazione del cursore Mio_cursore, per selezionare tutta la relazione Indirizzi in modo ordinato per Cognome. Si osservi che per PostgreSQL la selezione che si ingloba nella gestione di un cursore non può aggiornarsi automaticamente se i dati originali cambiano, per cui è come se fosse sempre definita la parola chiave INSENSITIVE.

...
FETCH NEXT FROM Mio_cursore;
...
COMMIT;

L'esempio mostra l'uso tipico dell'istruzione FETCH, in cui si preleva la tupla successiva rispetto alla posizione corrente del cursore e più avanti si conclude la transazione con un COMMIT. L'esempio seguente è identico, con la differenza che si indica espressamente il passo.

...
FETCH RELATIVE 1 FROM Mio_cursore;
...
COMMIT;

Un cursore dovrebbe essere chiuso attraverso una richiesta esplicita, con l'istruzione CLOSE, ma la chiusura della transazione chiude implicitamente il cursore, se questo dovesse essere rimasto aperto. L'esempio seguente riepiloga quanto visto sopra, completato dell'istruzione CLOSE.

START TRANSACTION;

DECLARE Mio_cursore INSENSITIVE CURSOR FOR
    SELECT * FROM Indirizzi ORDER BY Cognome, Nome;

-- L'apertura del cursore non esiste in PostgreSQL
-- OPEN Mio_cursore;

FETCH NEXT FROM Mio_cursore;

CLOSE Mio_cursore;

COMMIT;

602.7   Impostazione dell'ora locale

Il linguaggio SQL dispone dell'istruzione SET TIME ZONE per definire l'ora locale e di conseguenza lo scostamento dal tempo universale. PostgreSQL dispone della stessa istruzione che funziona in modo molto simile allo standard; per la precisione, la definizione dell'ora locale avviene attraverso le definizioni riconosciute dal sistema operativo (nel caso di GNU/Linux si tratta delle definizioni che si articolano a partire dalla directory /usr/share/zoneinfo/).

SET TIME ZONE { 'definizione_ora_locale' | LOCAL }

Per esempio, per definire che si vuole fare riferimento all'ora locale italiana, si potrebbe usare il comando seguente:

SET TIME ZONE 'Europe/Rome';

Questa impostazione riguarda la visione del programma cliente, mentre il programma servente può essere stato preconfigurato attraverso le variabili di ambiente LC_* oppure la variabile LANG, che in questo caso hanno effetto sullo stile di rappresentazione delle informazioni data-orario. Anche il programma cliente può essere preconfigurato attraverso la variabile di ambiente PGTZ, assegnandole gli stessi valori che si possono utilizzare per l'istruzione SET TIME ZONE.

602.8   Riferimenti

• PostgreSQL

  <http://www.postgresql.org/>

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 postgresql_il_linguaggio.htm

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