Documentazione di PostgreSQL 9.0 > Il linguaggio SQL > Tipi di Dato > Array
PrecedenteTipo XMLComposite TypesSuccessivo

8.14. Array

PostgreSQL™ consente che le colonne di una tabella siano definite come array multidimensionali a lunghezza variabile. Si possono creare array incorporati o definiti dall'utente, di tipo enum o di tipo composto. Gli array di dominio non sono finora gestiti.

8.14.1. Dichiarare i tipi Array

Per spiegare l'uso dei tipi array, creare questa tabella:

CREATE TABLE sal_emp (
    name            text,
    pay_by_quarter  integer[],
    schedule        text[][]
);

Come mostrato, un tipo dato array viene creato aggiungendo parentesi quadre ([]) al nome del tipo di dato degli elementi dell'array. Il comando mostrato sopra crea una tabella chiamata sal_emp con una colonna di tipo text (name), un array di tipo unidimensionale integer (pay_by_quarter), che rappresenta il salario trimestrale del dipendente, e un array a due dimensioni di tipo text (schedule), che rappresenta il piano di lavoro settimanale dell'impiegato.

La sintassi di CREATE TABLE consente di specificare la dimensione esatta dell'array, per esempio:

CREATE TABLE tictactoe (
    squares   integer[3][3]
);

Tuttavia, l'implementazione corrente non impone il rispetto dei limiti di dimensione dell'array -- il comportamento è identico agli array di lunghezza non specificata.

L'implementazione corrente non impone di dichiarare neanche il numero delle dimensioni. Gli array di un particolare tipo di elemento sono tutti considerati essere dello stesso tipo, senza tener conto della dimensione o del numero di dimensioni. Per cui, dichiarare il numero di dimensioni o il formato in CREATE TABLE è semplice documentazione, in quanto ciò non influisce sul comportamento al momento dell'esecuzione.

Una sintassi alternativa, conforme allo standard SQL, può essere usata per gli array unidimensionali. pay_by_quarter potrebbe essere definito come:

    pay_by_quarter  integer ARRAY[4],

Oppure, se non viene specificata la dimesione dell'array:

    pay_by_quarter  integer ARRAY,

Come prima, tuttavia, PostgreSQL™ non impone restrizioni di dimensione in nessun caso.

8.14.2. Inserimento di valori negli Array

Per scrivere un valore di array come una costante letterale, racchiudere i valori degli elementi tra parentesi graffe separarati da virgole. (Se si conosce il C, questo non è dissimile alla sintassi del C per l'inizializzazione delle strutture). Potete inserire doppi apici intorno ad ogni valore dell'elemento, dovrete farlo se esso contiene virgole o parentesi graffe. (Maggiori dettagli appaiono sotto). Quindi, il formato generale di una costante array è il seguente:

'{ val1 delim val2 delim ... }'

dove delim è il carattere di delimitazione per il tipo, come registrato nelle sua voce pg_type. Tra i tipi dati standard forniti nella distribuzione PostgreSQL™, il tipo box usa una punto e virgola (;) ma tutti gli altri usano la virgola (,). Ogni val può essere una costante di tipo elemento dell'array, o un subarray. Un esempio di una costante array è:

'{{1,2,3},{4,5,6},{7,8,9}}'

Questa costante è un array bidimensionale 3-per-3, consistente in tre subarray di interi.

Per impostare una elemento di un array a NULL, scrivere NULL come valore dell'elemento. (Sia i caratteri maiuscoli che i minuscoli vanno bene per NULL). Se si vuole un effettivo valore di stringa «NULL», bisogna racchiuderlo tra doppi apici.

(Questi tipi di array costanti sono attualmente solo un caso speciale delle costanti di tipo generico discusse in Sezione 4.1.2.7, «Costanti di altri tipi». La costante viene inizialmente trattata come una stringa e passata poi all'input della routine di conversione array. Potrebbe essere necessario specificare esplicitamente il tipo).

Si possono ora vedere alcuni comandi INSERT:

INSERT INTO sal_emp
    VALUES ('Bill',
    '{10000, 10000, 10000, 10000}',
    '{{"meeting", "lunch"}, {"training", "presentation"}}');

INSERT INTO sal_emp
    VALUES ('Carol',
    '{20000, 25000, 25000, 25000}',
    '{{"breakfast", "consulting"}, {"meeting", "lunch"}}');

Il risultato dei precedenti due inserimenti sarà simile a questo:

SELECT * FROM sal_emp;
 name  |      pay_by_quarter       |                 schedule
-------+---------------------------+-------------------------------------------
 Bill  | {10000,10000,10000,10000} | {{meeting,lunch},{training,presentation}}
 Carol | {20000,25000,25000,25000} | {{breakfast,consulting},{meeting,lunch}}
(2 rows)

Gli array multidimansionali devono avere corrispondenti intervalli per ogni dimensione. Una non-corrispondenza causa un errore, per esempio:

INSERT INTO sal_emp
    VALUES ('Bill',
    '{10000, 10000, 10000, 10000}',
    '{{"meeting", "lunch"}, {"meeting"}}');
ERROR:  multidimensional arrays must have array expressions with matching dimensions

Si può anche utilizzare la sintassi del costruttore di ARRAY:

INSERT INTO sal_emp
    VALUES ('Bill',
    ARRAY[10000, 10000, 10000, 10000],
    ARRAY[['meeting', 'lunch'], ['training', 'presentation']]);

INSERT INTO sal_emp
    VALUES ('Carol',
    ARRAY[20000, 25000, 25000, 25000],
    ARRAY[['breakfast', 'consulting'], ['meeting', 'lunch']]);

Notare che gli elementi degli array sono normali costanti SQL o espressioni; per esempio, i caratteri sono racchiusi tra singoli apici, invece dei doppi apici. La sintassi del costruttore di ARRAY viene discussa con maggiori dettagli in Sezione 4.2.11, «Costruttori di array».

8.14.3. Accedere agli array

Si possono ora eseguire alcune query sulla tabella. Prima, si vedrà come accedere ad un singolo elemento di array alla volta. Questa query richiama i nomi degli impiegati la cui paga è stata cambiata nel secondo trimestre:

SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];

 name
-------
 Carol
(1 row)

I numeri di indice dell'array sono scritti tra parentesi quadre. Come impostazione predefinita PostgreSQL™ usa per gli array la convenzione di numerazione basata su uno, cioè: un array di n elementi che cominciano con array[1] e terminano con array[n].

Questa query richiama la paga del terzo trimestre di tutti gli impiegati:

SELECT pay_by_quarter[3] FROM sal_emp;

 pay_by_quarter
----------------
          10000
          25000
(2 rows)

Si può anche accedere ad una parte rettangolare di un array, o di un sotto-array. Una parte di array è denotata scrivendo limite-inferiore:limite-superiore per una o più dimensioni dell'array. Per esempio, questa query richiama il primo elemento del piano di lavoro di Bill per i primi due giorni della settimana:

SELECT schedule[1:2][1:1] FROM sal_emp WHERE name = 'Bill';

        schedule
------------------------
 {{meeting},{training}}
(1 row)

Se una dimesione è scritta come una parte, cioè contiene un due punti, allora tutte le dimensioni vengono trattate come una parte. Ogni dimesione che ha un solo numero (senza due punti) viene trattata come se andasse da 1 al numero specificato. Per esempio, [2] viene trattato come se fosse scritto [1:2], come in questo esempio:

SELECT schedule[1:2][2] FROM sal_emp WHERE name = 'Bill';

                 schedule
-------------------------------------------
 {{meeting,lunch},{training,presentation}}
(1 row)

Per evitare confusione, è meglio usare la sintassi di intervallo per tutte le dimensioni, ad es. [1:2][1:1], non [2][1:1].

Un espressione di indice di array restituirà null se lo stesso array o una delle espressioni di indice è null. Viene restituito null anche se, un indice è fuori dei limiti dell'array (questo caso non genera un errore). Per esempio se schedule avesse le dimensioni [1:3][1:2] il riferimento schedule[3][3] produrrebbe NULL. Similmente, un riferimento di array con il numero di indici errato produrrebbe un null anzichè un errore.

Un espressione di una parte di array produce similmente null se l'array stesso o una delle espressioni di indice è null. Tuttavia, in altri casi, come la selezione di una parte di array che è completamente al di fuori dei limiti correnti dell'array, un'espressione di parte dell'array produrrà un array vuoto (di dimensione uguale a 0) invece di null. Se la parte richiesta sovrappone parzialmente i limiti dell'array, essa viene automaticamente ridotta alla sola regione di sovrapposizione invece di ritornare null.

Le dimensioni correnti di ogni valore dell'array possono essere recuperate con la funzione array_dims:

SELECT array_dims(schedule) FROM sal_emp WHERE name = 'Carol';

 array_dims
------------
 [1:2][1:2]
(1 row)

Il risultato prodotto da array_dims è di tipo text, che è conveniente per le persone che lo leggono ma forse non è altrettanto conveniente per i programmi. Le dimensioni possono anche essere recuperate con array_upper e con array_lower, che restituiscono, rispettivamente, i limiti superiore e inferiore di una specificata dimensione dell'array:

SELECT array_upper(schedule, 1) FROM sal_emp WHERE name = 'Carol';

 array_upper
-------------
           2
(1 row)

array_length ritornerà la lunghezza di una specificata dimensione di un array:

SELECT array_length(schedule, 1) FROM sal_emp WHERE name = 'Carol';

 array_length
--------------
            2
(1 row)

8.14.4. Modificare gli Array

Un valore di array può essere sostituito completamente:

UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
    WHERE name = 'Carol';

oppure utilizzando la sintassi espressione ARRAY:

UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000]
    WHERE name = 'Carol';

Un array può anche essere aggiornato in un singolo elemento:

UPDATE sal_emp SET pay_by_quarter[4] = 15000
    WHERE name = 'Bill';

oppure aggiornato in una parte:

UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}'
    WHERE name = 'Carol';

Un array memorizzato può essere allargato assegnandogli elementi/o non già presenti. Ogni posizione tra quelle precedentemente presenti e gli elementi/o ora assegnati sarà riempito con null. Per esempio, se l'array myarray attualmente ha 4 elementi, esso avrà sei elementi, dopo un aggiornamento che assegnerà a myarray[6]; myarray[5] conterrà un null. Attualmente, l'allargamento in questo modo è solamente consentito per array unidimensionali e non per array multidimensionali.

L'assegnazione di indici permette la creazione di array che non usano indici che partono da 1. Per esempio, si potrebbe assegnare a myarray[-2:7] per creare un array con valori degli indici che vanno da -2 a 7.

Nuovi valori di array possono essere costruiti usando gli operatori di concatenazione, ||:

SELECT ARRAY[1,2] || ARRAY[3,4];
 ?column?
-----------
 {1,2,3,4}
(1 row)

SELECT ARRAY[5,6] || ARRAY[[1,2],[3,4]];
      ?column?
---------------------
 {{5,6},{1,2},{3,4}}
(1 row)

L'operatore di concatenazione consente ad un singolo elemento di essere spinto all'inizio o alla fine di un array unidimensionale. Esso accetta anche due array N-dimensionali, o un array N-dimensionale e uno N+1-dimensionale.

Quando un singolo elemento viene spinto all'inizio o alla fine di un array unidimensionale, il risultato è un array con lo stesso limite inferiore di indice dell'operatore dell'array. Per esempio:

SELECT array_dims(1 || '[0:1]={2,3}'::int[]);
 array_dims
------------
 [0:2]
(1 row)

SELECT array_dims(ARRAY[1,2] || 3);
 array_dims
------------
 [1:3]
(1 row)

Quando due array con un uguale numero di dimensioni vengono concatenati, il risultato mantiene il limite inferiore della dimensione esterna dell'operatore di sinistra. Il risultato è un array comprendente ogni elemento dell'operando di sinistra seguito da ogni elemento dell'operando di destra. Per esempio:

SELECT array_dims(ARRAY[1,2] || ARRAY[3,4,5]);
 array_dims
------------
 [1:5]
(1 row)

SELECT array_dims(ARRAY[[1,2],[3,4]] || ARRAY[[5,6],[7,8],[9,0]]);
 array_dims
------------
 [1:5][1:2]
(1 row)

Quando un array N-dimensionale viene spinto all'inizio o alla fine di un array N+1-dimensionale, il risultato è identico al caso visto sopra. Ogni sotto-array N-dimensionale è un elemento dell'array di dimensione esterna N+1-dimensionale. per esempio:

SELECT array_dims(ARRAY[1,2] || ARRAY[[3,4],[5,6]]);
 array_dims
------------
 [1:3][1:2]
(1 row)

Un array puè essere costruito anche usando le funzioni array_prepend, array_append, o array_cat. Le prime due supportano solo array unidirezionali, ma array_cat supporta array multidimensionali. Notare che l'operatore di concatenamento discusso sopra è preferito nell'uso diretto di queste funzioni. Infatti, le funzioni esistono principalmente per usare nelle implementazioni l'operatore di concatenamento. Tuttavia, esse possono essere direttamente utilizzate in creazioni di aggregati definiti dall'utente. Alcuni esempi:

SELECT array_prepend(1, ARRAY[2,3]);
 array_prepend
---------------
 {1,2,3}
(1 row)

SELECT array_append(ARRAY[1,2], 3);
 array_append
--------------
 {1,2,3}
(1 row)

SELECT array_cat(ARRAY[1,2], ARRAY[3,4]);
 array_cat
-----------
 {1,2,3,4}
(1 row)

SELECT array_cat(ARRAY[[1,2],[3,4]], ARRAY[5,6]);
      array_cat
---------------------
 {{1,2},{3,4},{5,6}}
(1 row)

SELECT array_cat(ARRAY[5,6], ARRAY[[1,2],[3,4]]);
      array_cat
---------------------
 {{5,6},{1,2},{3,4}}

8.14.5. Cercare negli Array

Per ricercare un valore in un array, bisogna controllare ogni valore dell'array. Questo può essere fatto a mano, se si conosce la dimensione dell'array. Per esempio:

SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR
                            pay_by_quarter[2] = 10000 OR
                            pay_by_quarter[3] = 10000 OR
                            pay_by_quarter[4] = 10000;

Tuttavia, questo diventa estremamente noioso per grossi array, e non è di grande aiuto se la dimensione dell'array è dubbia. Un metodo alternativo è descritto in Sezione 9.21, «Confronti di array e righe». La query vista sopra può essere rimpiazzata con:

SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);

In aggiunta, si potrebbero cercare le righe in cui l'array ha tutti i valori uguali a 10000 con:

SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);

In alternativa, può essere usata la funzione generate_subscripts. Per esempio:

SELECT * FROM
   (SELECT pay_by_quarter,
           generate_subscripts(pay_by_quarter, 1) AS s
      FROM sal_emp) AS foo
 WHERE pay_by_quarter[s] = 10000;

Questa funzione è descritta in Tabella 9.46, «Sottoscript che generano funzioni».

[Suggerimento]

Suggerimento

Gli array non sono insiemi; ricerche per uno specifico elemento di array possono essere un segno di cattiva progettazione del database. Considerare la possibilità di usare una tabella separata con una riga per ogni elemento che sarebbe un elemento dell'array. Questo faciliterà la ricerca, e sarà verosimilmente un miglioramento per grandi numeri di elementi.

8.14.6. Sintassi di input e output degli array

La rappresentazione in testo di un valore di array consiste di elementi che sono interpretati secondo le regole di conversione di I/O per il tipo elemento di array, più un contorno che indica la struttura dell'array. Il contorno consiste di parentesi graffe ({ e }) attorno ai valori dell'array più caratteri di delimitazione tra elementi adiacenti. Il carattere di delimitazione è normalmente una virgola (,) ma può anche essere qualche altro: dipende dall'impostazione di typdelim per il tipo elemento di array. (Tra i tipi dati standard forniti nella distribuzione PostgreSQL™, il tipo box usa punto e virgola (;) ma tutti gli altri usano la virgola). In un array multidimensionale, ogni dimensione (riga, piano, cubo, ecc.) accetta il suo proprio livello di parentesi graffe, ed i delimitatori devono essere scritti tra adiacenti entità parentesi graffe dello stesso livello.

La routine di output degli array metterà doppi apici intorno ai valori di elementi se sono stringhe vuote, se contengono parentesi graffe, caratteri delimitatori, doppi apici, barre inverse, o spazi vuoti, o contengono la parola NULL. Doppi apici e barre inverse incorporate dentro valori di elementi saranno preceduti da barre inverse. Per tipi dati numerici è sicuro presumere che i doppi apici non compariranno mai, ma per i tipi dati testuali bisognerà essere preparati a far fronte alla presenza o all'assenza di apici.

Per impostazione predefinita, il valore dell'indice del limite inferiore della dimensione di un array è impostato a uno. Per rappresentare array con altri limiti inferiori, la gamma degli indici dell'array deve essere specificata esplicitamente prima di scrivere il contenuto dell'array. Questo consiste nel contorno di parentesi quadre ([]) intorno ai limiti inferiore e superiore di dimensione dell'elemento dell'array, con un carattere delimitatore due punti (:) posto tra di loro. Il contorno della dimensione di un array è seguito da un segno uguale (=). Per esempio:

SELECT f1[1][-2][3] AS e1, f1[1][-1][5] AS e2
 FROM (SELECT '[1:1][-2:-1][3:5]={{{1,2,3},{4,5,6}}}'::int[] AS f1) AS ss;

 e1 | e2
----+----
  1 |  6
(1 row)

La routine di output degli array includerà dimensioni esplicite nel suo risultato solo quando ci sono uno o più limiti inferiori diversi da uno.

Se il valore scritto per un elemento è NULL (sia maiuscolo che minuscolo), l'elemento viene considerato essere NULL. La presenza di apici o barre inverse disabilita ciò e consente l'immissione del valore stringa «NULL». Inoltre, per la compatibilità all'indietro con la la versione precedente la 8.2 di PostgreSQL™, il parametro di configurazione array_nulls può essere messo a off per sopprimere l'identificazione di NULL come un NULL.

Come visto precedentemente, quando si scrive un valore di array si possono usare i doppi apici intorno ad ogni individuale elemento dell'array. Bisogna fare così se il valore dell'elemento può confondere l'analizzatore di valori dell'array. In particolare, elementi contenenti parentesi graffe, virgole (o qualsiasi delimitatore di caratteri), doppi apici, barre inverse, o spazi bianchi in testa o in coda devono essere racchiusi tra doppi apici. Anche le stringhe vuote e le stringhe che contengono la parola NULL devono essere racchiuse tra apici. Per inserire un doppio apice o una barra inversa in un valore di elemento di array racchiuso tra apici, usare la sintassi di escape delle stringhe e precederli con una barra inversa. In alternativa, si può usare la barra inversa di escape per proteggere tutti i caratteri che sarebbero altrimenti presi per sintassi di array.

Si possono scrivere spazi bianchi prima della parentesi graffa di sinistra o dopo quella di destra. Si possono anche scrivere spazi bianchi prima o dopo ogni individuale elemento stringa. In tutti questi casi gli spazi bianchi saranno ignorati. Tuttavia, spazi bianchi dentro elementi racchiusi tra doppi apici, o attorniati in ambedue i lati da caratteri di un elemento diversi da spazi bianchi, non saranno ignorati.

[Nota]

Nota

Ricordarsi che ciò che si scrive in un comando sql sarà prima interpretato come una stringa, e dopo come un array. Questo raddoppia il numero di barre inverse necessarie. Per esempio, per inserire un valore di array di tipo text contenente una barra inversa e un doppio apice, bisognerà scrivere:

INSERT ... VALUES (E'{"\\\\","\\""}');

Il processore di stringhe di escape rimuove un livello di barre inverse, per cui ciò che arriverà all'analizzatore di valori array sarà questo {"\\","\""}. Per cui, le stringhe che alimentano la routine di entrata del tipo di dati text saranno rispettivamente \ e ". (Se si stesse lavorando con tipi di dato le cui routine di input trattano specialmente anche le barre inverse, per esempio bytea, potrebbero essere necessarie ben otto barre inverse nel comando per inserire una barra inversa nell'elemento di array memorizzato). La quotazione con dollaro (vedere Sezione 4.1.2.4, «Costanti stringa quotate da dollari») può essere usata per evitare la necessità di raddoppiare le barre inverse.

[Suggerimento]

Suggerimento

La sintassi del costruttore di ARRAY (vedere Sezione 4.2.11, «Costruttori di array») è spesso più facile da usare quando si scrivono valori di array nei comandi SQL. Negli ARRAY, i valori degli elementi individuali vengono scritti nello stesso modo in cui sarebbero scritti se non fossero membri di un array.

Documentazione di PostgreSQL 9.0 > Il linguaggio SQL > Tipi di Dato > Array
PrecedenteTipo XMLComposite TypesSuccessivo