[inizio] [indice generale] [precedente] [successivo] [indice analitico] [contributi]


191. 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 dall'elenco seguente:

In questo capitolo si intende usare la terminologia tradizionale dei DBMS relazioni, corrispondente a quella delle prime versioni del linguaggio SQL: tabelle, righe, colonne,... Nello stesso modo, 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, a cominciare da sql(1) per finire con il manuale dell'utente che contiene diversi esempi molto utili.

191.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 verranno inserite delle nuove tabelle, e con queste si eseguiranno 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'.

Utilizzando `psql', le istruzioni devono essere terminate con il punto e virgola (`;'), oppure dal comando interno `\g' (go).

191.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 191.1. Si deve tenere presente che SQL utilizza diversi modi possibili per definire lo stesso tipo di dati; per esempio il tipo `CHAR' può essere indicato anche come `CHARACTER', e così `VARCHAR' che può essere espresso come `CHAR VARYING' o `CHARACTER VARYING'. Quando PostgreSQL ammette l'utilizzo di una forma, riconosce poi anche le altre.

Tipo Standard Descrizione
CHAR SQL92 Un carattere singolo.
CHAR(n) SQL92 Una stringa di lunghezza fissa, di n caratteri, completata da spazi.
VARCHAR(n) SQL92 Una stringa di lunghezza variabile con un massimo di n caratteri.
INTEGER SQL92 Intero (al massimo nove cifre numeriche).
SMALLINT SQL92 Intero più piccolo di `INTEGER'.
FLOAT SQL92 Numero a virgola mobile.
FLOAT(n) SQL92 Numero a virgola mobile lungo n bit.
REAL SQL92 Numero a virgola mobile (teoricamente più preciso di `FLOAT').
DOUBLE PRECISION SQL92 Numero a virgola mobile (più o meno equivalente a `REAL').
DATE SQL92 Data, di solito nella forma `'mm/gg/aaaa''.
TIME SQL92 Orario, nella forma `'hh:mm:ss'', oppure solo `'hh:mm''.
TIMESTAMP SQL92 Informazione completa data-orario.
INTERVAL SQL92 Intervallo di tempo.
BOOLEAN SQL3 Valore logico booleano.

Tabella 191.1: Elenco dei tipi di dati standard utilizzabili con PostgreSQL, espressi nella loro forma compatta.

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. La tabella 191.2 mostra alcuni esempi.

Tipo Esempi
CHAR `'a'', `'A'', `'b'', `'1''.
CHAR(n) `'a'', `'ciao'', `'Ciao'', `'123/der:876''.
VARCHAR(n) `'a'', `'ciao'', `'Ciao'', `'123/der:876''.
INTEGER `1', `123', `-987'.
SMALLINT Come `INTEGER'.
FLOAT `123.45', `-45.3', `123.45e+10', `123.45e-10'.
FLOAT(n) Come `FLOAT'.
REAL Come `FLOAT'.
DOUBLE PRECISION Come `FLOAT'.
DATE `'31.12.1999'', `'12/31/1999'', `'1999-12-31''.
TIME `'15:55:27'', `'15:59''.
TIMESTAMP `'1999-12-31 15:55:27'', `'1999-12-31 15:55:27+1''.
INTERVAL `INTERVAL '15:55:27'', `INTERVAL '15 HOUR 59 MINUTE'', `INTERVAL '- 15 HOUR''.
BOOLEAN `1', `'y'', `'yes'', `'t'', `'true''; `0', `'n'', `'no'', `'f'', `'false''.

Tabella 191.2: Esempi di rappresentazione dei valori costanti.

In particolare, le costanti stringa possono contenere delle sequenze di escape, rappresentate da una barra obliqua inversa seguita da un simbolo. La tabella 191.3 mostra le sequenze di escape tipiche.

Escape Significato
\n newline
\r return
\b backspace
\' '
\" "
\\ \
\% %
\_ _

Tabella 191.3: Sequenze di escape utilizzabili all'interno delle stringhe di caratteri costanti.

191.3 Funzioni

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

Funzione Descrizione
POSITION(<stringa-1> IN <stringa-2>) Posizione di <stringa-1> in <stringa-2>.
SUBSTRING(<stringa> [FROM n] [FOR m]) Sottostringa da n per m caratteri.
TRIM([LEADING|TRAILING|BOTH] ['x'] FROM [<stringa>]) Ripulisce all'inizio e alla fine del testo.

Tabella 191.4: Funzioni SQL riconosciute da PostgreSQL.

Funzione Descrizione
UPPER(<stringa>) Converte la stringa in caratteri maiuscoli.
LOWER(<stringa>) Converte la stringa in caratteri minuscoli.
INITCAP(<stringa>) Converte la stringa in modo che le parole inizino con la maiuscola.
SUBSTR(<stringa>,n,m) Estrae la stringa che inizia dalla posizione n, lunga m caratteri.
LTRIM(<stringa>, 'x') Ripulisce la stringa a sinistra (Left TRIM).
RTRIM(<stringa>, 'x') Ripulisce la stringa a destra (Right TRIM).

Tabella 191.5: Alcune funzioni riconosciute dal linguaggio di PostgreSQL.

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 della colonna `Cognome', per tutte le righe della tabella `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 considerato anche che la parola chiave `BOTH' è anche predefinita.

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

Restituisce la stringa `Ciao****'.

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

Restituisce la stringa `*****Ciao'.

191.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 tabella di indirizzi, il cui contenuto è visibile nella figura 191.1.

+=====================================================================+
|Indirizzi                                                            |
+---------------------------------------------------------------------+
|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   |
+======+===============+===============+===============+==============+

Figura 191.1: La tabella `Indirizzi(Codice,Cognome,Nome,Indirizzo,Telefono)' usata in molti esempi del capitolo.

191.4.1 Creazione di una tabella

La tabella di esempio mostrata nella figura 191.1, 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 riga, può capitare che venga omesso l'inserimento di alcune colonne. In questi casi, il campo corrispondente riceve il valore `NULL', cioè un valore indefinito, oppure il valore predefinito attraverso quanto specificato attraverso 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 tal caso si può aggiungere `NOT NULL', dopo la definizione del tipo.

191.4.2 Modifica della tabella

Per il momento, le funzionalità di modifica della struttura di una tabella sono limitate alla sola aggiunta di colonne, come nell'esempio seguente dove viene aggiunta una colonna per l'indicazione del comune di residenza alla tabella già vista in precedenza.

ALTER TABLE Indirizzi ADD COLUMN Comune char(30);

È bene osservare che non sempre si ottiene il risultato desiderato.

191.4.3 Inserimento dati in una tabella

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 delle colonne, così come è stata creata la tabella in origine. Se si vuole indicare un comando più leggibile, occorre aggiungere l'indicazione della sequenza delle colonne 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 una colonna particolare, indicando espressamente solo le colonne che si vogliono fornire; le altre colonne riceveranno 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',
	);

191.4.4 Eliminazione di una tabella

Una tabella può essere eliminata completamente attraverso l'istruzione `DROP'. L'esempio seguente elimina la tabella degli indirizzi degli esempi precedenti.

DROP TABLE Indirizzi;

191.4.5 Interrogazioni semplici

L'esempio seguente emette tutto il contenuto della tabella 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 tabella.

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 delle colonne permette di ottenere un risultato con le sole colonne desiderate, 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 righe può essere fatta attraverso la condizione che segue la parola chiave `WHERE'. Nell'esempio seguente vengono selezionate le righe 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

Come si vedrà meglio in seguito, per evitare ambiguità possono essere indicati i nomi delle colonne prefissati dal nome della tabella a cui appartengono, separando le due parti con l'operatore punto (`.'). L'esempio seguente è già stato mostrato in precedenza, ma serve a chiarire questo modo di identificazione delle colonne.

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

cognome   nome       telefono

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

191.4.6 Interrogazioni simultanee di più tabelle

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

+=================================+
|Presenze                         |
+---------------------------------+
|Codice|Giorno    |Ingresso|Uscita|
+------+----------+--------+------+
|     1|01/01/1999| 07:30  |13:30 |
|     2|01/01/1999| 07:35  |13:37 |
|     3|01/01/1999| 07:45  |14:00 |
|     4|01/01/1999| 08:30  |16:30 |
|     1|01/02/1999| 07:35  |13:38 |
|     2|01/02/1999| 08:35  |14:37 |
|     4|01/02/1999| 07:40  |13:30 |
+======+==========+========+======+

Figura 191.2: La tabella `Presenze(Codice,Giorno,Ingresso,Uscita)'.

Come si può intendere, la prima colonna, `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 tabella `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-1999  07:30:00  13:30:00  Pallino   Pinco
01-01-1999  07:35:00  13:37:00  Tizi      Tizio
01-01-1999  07:45:00  14:00:00  Cai       Caio     
01-01-1999  08:30:00  16:30:00  Semproni  Sempronio
01-02-1999  07:35:00  13:38:00  Pallino   Pinco
01-02-1999  08:35:00  14:37:00  Tizio     Tizi
01-02-1999  07:40:00  13:30:00  Semproni  Sempronio

191.4.7 Alias

Una stessa tabella può essere presa in considerazione come se si trattasse di due o più tabelle 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;

191.4.8 Viste

Attraverso una vista, è possibile definire una tabella virtuale. PostgreSQL, allo stato attuale, consente di utilizzare le viste in sola lettura.

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 tabelle `Presenze' e `Indirizzi'. In pratica, questa vista permette di interrogare direttamente la tabella virtuale `Presenze_dettagliate', invece di utilizzare ogni volta un comando `SELECT' molto complesso, per ottenere lo stesso risultato.

191.4.9 Aggiornamento delle righe

La modifica di righe 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 righe della tabella.

L'esempio seguente, aggiunge una colonna alla tabella 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 riga dell'impiegato `Pinco Pallino'.

191.4.10 Cancellazione delle righe

L'esempio seguente elimina dalla tabella delle presenze le righe riferite alle registrazioni del giorno 01/01/1999 e le eventuali antecedenti.

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

191.4.11 Creazione di una nuova tabella a partire da altre

L'esempio seguente crea la tabella `mia_prova' come risultato della fusione della tabella 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;

191.4.12 Inserimento in una tabella esistente

L'esempio seguente aggiunge alla tabella 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 <= '1999/01/01';

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

191.4.13 Controllare gli accessi a una tabella

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

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

REVOKE {ALL | SELECT | INSERT | UPDATE | DELETE | RULE}[,...]
	ON <tabella>[,...]
	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; solo che attualmente potrebbe non essere possibile la gestione dei gruppi.

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

REVOKE ALL
	ON Presenze, Indirizzi
	FROM PUBLIC;

GRANT ALL
	ON Presenze, Indirizzi
	TO daniele;

191.5 Controllo delle transazioni

PostgreSQL ha una gestione delle transazioni leggermente diversa da quanto stabilito dall'SQL. Per la precisione, occorre dichiarare esplicitamente l'inizio di una transazione con l'istruzione `BEGIN'.

BEGIN [WORK]

L'esempio seguente mostra il caso in cui si voglia isolare l'inserimento di una riga nella tabella `Indirizzi' all'interno di una transazione, che alla fine viene confermata regolarmente.

BEGIN;

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

COMMIT;

Nell'esempio seguente, si rinuncia all'inserimento della riga con l'istruzione `ROLLBACK' finale.

BEGIN;

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

ROLLBACK;

191.6 Cursori

La gestione dei cursori da parte di PostgreSQL è limitata rispetto all'SQL92. In particolare, non è disponibile lo spostamento assoluto del cursore, e non è possibile assegnare i dati a delle variabili.

La dichiarazione di un cursore avviene nel modo solito, con la differenza che questo 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.

BEGIN;

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, e la dichiarazione del cursore `Mio_cursore', per selezionare tutta la tabella `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 prossima riga 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'.

BEGIN;

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;

191.7 Particolarità di PostgreSQL

Ogni DBMS, per quanto compatibile con gli standard, può avere la necessità di introdurre delle estensioni al linguaggio di gestione per permettere l'accesso a funzionalità speciali che dipendono dalle sue caratteristiche particolari. In questo capitolo si è voluto porre l'accento su ciò che è il più vicino possibile all'SQL, trascurando quasi tutto il resto. In queste sezioni si descrivono alcune istruzioni particolari che si ritengono importanti da un punto di vista operativo, benché siano estranee all'SQL.

191.7.1 Importazione ed esportazione dei dati

PostgreSQL fornisce un'istruzione speciale per permettere l'importazione e l'esportazione dei dati da e verso un file di testo normale. Si tratta di `COPY' la cui sintassi semplificata è quella seguente:

COPY <tabella> FROM { '<file>' | STDIN }
	[ USING DELIMITERS '<delimitatore>' ]

COPY <tabella> TO { '<file>' | STDOUT }
	[ USING DELIMITERS '<delimitatore>' ]

Nella prima delle due forme, si importano i dati da un file o dallo standard input; nel secondo si esportano verso un file o verso lo standard output.

Ogni riga del file di testo corrisponde a una riga della tabella; gli attributi sono separati da un carattere di delimitazione, che in mancanza della definizione tramite la clausola `USING DELIMITERS' è un carattere di tabulazione. In ogni caso, anche se si specifica tale clausola, può trattarsi solo di un carattere. In pratica, ogni riga è organizzata secondo lo schema seguente:

<attributo-1>x<attributo-2>x...x<attributo-N>

Nello schema, x rappresenta il carattere di delimitazione, e come si vede, all'inizio e alla fine non viene inserito.

Quando l'istruzione `COPY' viene usata per importare dati dallo standard input, è necessario che dopo l'ultima riga che contiene attributi da inserire nella tabella, sia presente una sequenza di escape speciale: una barra obliqua inversa seguita da un punto (`\.'). Il file ottenuto quando si esporta verso lo standard output contiene questo simbolo di conclusione.

Il file di testo in questione può contenere anche altre sequenze di escape, che si trovano descritte nella tabella 191.6.

Escape Descrizione
\\ Una barra obliqua inversa.
\. Simbolo di conclusione del file.
\N `NULL'.
\<delimitatore> Protegge il simbolo che viene già utilizzato come delimitatore.
\<LF> Tratta <LF> in modo letterale.

Tabella 191.6: Sequenze di escape nei file di testo generati e utilizzati da `COPY'.

È importante fare mente locale al fatto che l'istruzione viene eseguita dal servente. Ciò significa che i file di testo, quando non si tratta di standard input o di standard output, sono creati o cercati secondo il filesystem che questo servente si trova ad avere sotto di sé.

COPY Indirizzi TO STDOUT;

L'esempio mostra l'istruzione necessaria a emettere attraverso lo standard output del cliente (`psql') la trasformazione in testo del contenuto della tabella `Indirizzi',

COPY Indirizzi TO '/tmp/prova' USING DELIMITER '|';

In quest'altro caso, si genera il file `/tmp/prova', nel filesystem del servente, e gli attributi sono separati attraverso una barra verticale (`|').

COPY Indirizzi FROM STDIN;

In questo caso, si aggiungono righe alla tabella `Indirizzi', utilizzando quanto proviene dallo standard input (alla fine deve apparire la sequenza di escape `\.').

COPY Indirizzi FROM '/tmp/prova' USING DELIMITER '|';

Si aggiungono righe alla tabella `Indirizzi', utilizzando quanto proviene dal file `/tmp/prova', che si trova nel filesystem del servente. In particolare, gli attributi sono separati da una barra verticale (`|').

191.7.2 Riorganizzazione del contenuto di una base di dati

Nel capitolo precedente si è già accennato all'istruzione `VACUUM', con la quale si riorganizzano i dati, eliminando i resti di una transazione interrotta, e ricostruendo gli indici e le statistiche interne. Se non si ha la pretesa di analizzare la base di dati, lo schema sintattico è molto semplice:

VACUUM [ <tabella> ]

Se non si indica una tabella particolare, si intende intervenire su tutta la base di dati su cui si sta lavorando.

È conveniente utilizzare questa istruzione tutte le volte che si annulla una transazione.

Per poter eseguire le operazioni relative all'istruzione `VACUUM', è necessario un blocco esclusivo delle tabelle coinvolte. Questo blocco è rappresentato in pratica da un file collocato nella directory che contiene i file della base di dati relativa. Il file si chiama `pg_vlock', e se si interrompe in qualche modo un'istruzione `VACUUM', questo file non viene rimosso, e impedisce tutte le attività sulla base di dati. Se questa situazione dovesse verificarsi, si può disattivare il servente, in modo da essere certi che non ci sia alcun accesso ai dati, e successivamente si potrebbe eliminare il file che rappresenta questo blocco.

191.7.3 Impostazione dell'ora locale

L'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 cliente, mentre il 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 cliente può essere preconfigurato attraverso la variabile di ambiente `PGTZ', assegnandole gli stessi valori che si possono utilizzare per l'istruzione `SET TIME ZONE'.

191.8 Riferimenti

---------------------------

Appunti Linux 2000.04.12 --- Copyright © 1997-2000 Daniele Giacomini --  daniele @ pluto.linux.it


[inizio] [indice generale] [precedente] [successivo] [indice analitico] [contributi]