5.12. Matrizes

O PostgreSQL permite que colunas de uma tabela sejam definidas como matrizes (arrays) multidimensionais de comprimento variável. Podem ser criadas matrizes de qualquer tipo de dado, nativo ou definido pelo usuário. Para ilustrar a utilização a tabela abaixo é criada:

CREATE TABLE sal_emp (
    nome               text,
    pagamento_semanal  integer[],
    agenda             text[][]
);

Conforme visto, o tipo de dado matricial é definido adicionando colchetes ([]) ao nome do tipo de dado dos elementos da matriz. O comando acima cria uma tabela chamada sal_emp, contendo uma coluna para cadeia de caracteres do tipo de dado text (nome), uma coluna contendo a matriz unidimensional do tipo de dado integer (pagamento_semanal), representando o pagamento semanal do empregado, e uma coluna contendo a matriz de duas dimensões do tipo de dado text (agenda), que representa a agenda semanal do empregado.

Agora iremos realizar alguns comandos de inserção (INSERT). Observe que um valor matricial para ser escrito deve estar entre chaves e separado por vírgulas. Aqueles que conhecem a linguagem C vão ver que não é diferente da sintaxe para inicializar estruturas (Abaixo são mostrados mais detalhes).

INSERT INTO sal_emp
    VALUES ('Bill',
    '{10000, 10000, 10000, 10000}',
    '{{"reunião", "almoço"}, {}}');

INSERT INTO sal_emp
    VALUES ('Carol',
    '{20000, 25000, 25000, 25000}',
    '{{"palestra", "consultoria"}, {"reunião"}}');

Agora podem ser feitas algumas consultas à tabela sal_emp. Primeiro será mostrado como acessar um único elemento de uma matriz de cada vez. Esta consulta obtém os nomes dos empregados cujos pagamentos mudaram na segunda semana:

SELECT nome FROM sal_emp WHERE pagamento_semanal[1] <> pagamento_semanal[2];

 nome
-------
 Carol
(1 row)

Os números dos elementos da matriz são escritos entre colchetes. Por padrão, o PostgreSQL utiliza a convenção de base um para a numeração dos elementos da matriz, ou seja, as matrizes com n elementos começam por array[1] e terminam por array[n].

A consulta abaixo obtém o pagamento da terceira semana de todos os empregados:

SELECT pagamento_semanal[3] FROM sal_emp;

 pagamento_semanal
------------------
            10000
            25000
(2 rows)

Também podem ser acessadas faixas retangulares de uma matriz, ou submatrizes. Uma faixa de uma matriz é especificada escrevendo-se limite-inferior:limite-superior para uma ou mais dimensões da matriz. A consulta abaixo obtém o primeiro item da agenda do Bill para os primeiros dois dias da semana:

SELECT agenda[1:2][1:1] FROM sal_emp WHERE nome = 'Bill';

      agenda
--------------------
 {{reunião},{""}}
(1 row)

Também é possível escrever

SELECT agenda[1:2][1] FROM sal_emp WHERE nome = 'Bill';

para obter o mesmo resultado. Uma operação para identificar elementos de uma matriz é considerada como representando uma faixa da matriz se for escrita na forma inferior:superior. Um limite inferior igual a 1 é estabelecido para qualquer índice quando somente um valor for especificado.

O valor de uma matriz pode ser inteiramente substituído:

UPDATE sal_emp SET pagamento_semanal = '{25000,25000,27000,27000}'
    WHERE nome = 'Carol';

ou pode ser atualizado um único elemento:

UPDATE sal_emp SET pagamento_semanal[4] = 15000
    WHERE nome = 'Bill';

ou pode ser atualizada uma faixa:

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

Uma matriz pode ser ampliada fazendo atribuição a um elemento adjacente aos já existentes, ou fazendo atribuição a uma faixa que é adjacente ou se sobrepõe aos dados já existentes. Por exemplo, se uma matriz possui atualmente quatro elementos, esta matriz terá cinco elementos após uma atualização fazer a atribuição de array[5]. Atualmente, as ampliações desta forma somente são permitidas para matrizes unidimensionais, não sendo permitidas para matrizes multi-dimensionais.

A atribuição de fatia de matriz permite a criação de matrizes que não utilizam índices baseados em um. Por exemplo, pode ser feita a atribuição array[-2:7] para criar uma matriz com índices variando de -2 a 7.

A sintaxe do CREATE TABLE permite a criação de matrizes de comprimento fixo:

CREATE TABLE jogo_da_velha (
    casa   integer[3][3]
);

Entretanto, a implementação atual não impõe os limites do tamanho da matriz --- o comportamento é o mesmo das matrizes de comprimento não especificado.

Na verdade, a implementação atual também não impõe o número declarado de dimensões. As matrizes de um determinado tipo de elemento são todos considerados como sendo do mesmo tipo, não importando o tamanho ou o número de dimensões. Portanto, a declaração do número de dimensões ou dos tamanhos no comando CREATE TABLE é simplesmente uma documentação, que não afeta o comportamento em tempo de execução.

As dimensões atuais de qualquer valor de uma matriz podem ser obtidas por meio da função array_dims:

SELECT array_dims(agenda) FROM sal_emp WHERE nome = 'Carol';

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

A função array_dims produz um resultado do tipo text conveniente para as pessoas lerem, mas talvez não muito conveniente para os programas.

Para procurar um valor em uma matriz deve ser verificado cada valor da matriz. Pode ser feito à mão (se for conhecido o tamanho da matriz):

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

Entretanto, esta forma pode ser entediante para matrizes grandes, e não servirá se a matriz for de tamanho desconhecido. Embora não faça parte da distribuição primária do PostgreSQL, existe uma extensão disponível que define novas funções e operadores para interagir com valores de matrizes. Utilizando esta extensão, a consulta poderia ser:

SELECT * FROM sal_emp WHERE pagamento_semanal[1:4] *= 10000;

Para procurar em toda a matriz (e não apenas nas colunas especificadas), poderia ser utilizado:

SELECT * FROM sal_emp WHERE pagamento_semanal *= 10000;

Além disso, poderiam ser encontradas as linhas onde a matriz tivesse todos os valores iguais a 10 000 com:

SELECT * FROM sal_emp WHERE pagamento_semanal **= 10000;

Para instalar este módulo opcional veja o diretório contrib/array da distribuição do fonte do PostgreSQL.

Dica: Matrizes não são conjuntos; a utilização de matrizes da forma descrita no parágrafo anterior é geralmente um indicativo de um projeto ruim de banco de dados. O campo matriz deve geralmente ser separado em uma tabela a parte. As tabelas obviamente podem ser pesquisadas com facilidade.

Nota: Uma limitação da implementação atual das matrizes é que os elementos individuais de uma matriz não podem ter atribuídos o valor nulo do SQL. Toda a matriz pode ser definida como nulo, mas não é possível ter uma matriz com alguns elementos nulo e alguns não. A solução deste problema está na lista de coisas a fazer.

Sintaxe de entrada e de saída das matrizes. A representação externa do valor de uma matriz é composta por itens que são interpretados de acordo com as regras de conversão de I/O para o tipo do elemento da matriz, mais os elementos que indicam a estrutura da matriz. Estes elementos são compostos por chaves ({ e }) em torno do valor da matriz, mais os caracteres delimitadores entre os itens adjacentes. O caractere delimitador geralmente é a vírgula (,), mas pode ser outra coisa: é determinado pela definição de typdelim para o tipo de elemento da matriz (Entre os tipos de dado padrão fornecidos na distribuição do PostgreSQL, o tipo box utiliza o ponto-e-vírgula (;), mas todos os outros utilizam a vírgula). Em uma matriz multidimensional cada dimensão (linha, plano, cubo, etc.) recebe seu nível próprio de chaves, e os delimitadores devem ser escritos entre entidades de chaves adjacentes do mesmo nível. Podem ser colocados espaços antes da chave de abertura, após a chave de fechamento, ou antes de qualquer item individual cadeia de caracteres. Espaços em branco após um item não são ignorados, entretanto: após saltar os espaços em branco iniciais, tudo até a próxima chave de fechamento ou delimitador é tomado como sendo o valor do item.

Colocando elementos da matriz entre aspas. Conforme mostrado acima, ao escrever um valor de uma matriz pode-se colocar aspas em torno de qualquer elemento individual da matriz. Isto deve ser feito se o valor do elemento puder de alguma forma confundir o analisador de elemento da matriz. Por exemplo, os elementos contendo chaves, vírgulas (ou qualquer que seja o caractere delimitador), aspas, contrabarras ou espaços iniciais devem estar entre aspas. Para colocar aspas ou contrabarras no valor do elemento da matriz, estes devem ser precedidos por uma contrabarra. Como alternativa, pode ser utilizado o escape de contrabarra para proteger todos os caracteres de dado que seriam de outra forma considerados como sintaxe da matriz ou espaços em branco ignoráveis.

A rotina de saída da matriz coloca aspas em torno dos valores dos elementos caso estes sejam cadeias de caracteres vazias ou contenham chaves, caracteres delimitadores, aspas, contrabarras ou espaços em branco. Aspas ou contrabarras embutidas nos valores dos elementos têm a contrabarra de escape. Para os tipos de dado numéricos é seguro supor que as aspas nunca vão aparecer, mas para os tipos de dado textuais deve-se estar preparado para lidar tanto com a presença quanto com a ausência das aspas (Esta é uma mudança de comportamento em relação às versões anteriores a 7.2 do PostgreSQL).

Dica: Lembre-se que quando se escreve um comando SQL este é interpretado primeiro como um literal cadeia de caracteres e depois como uma matriz. Isto dobra o número de contrabarras necessárias. Por exemplo, para inserir um valor do tipo text contendo uma contrabarra e uma aspa, deve ser escrito

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

O processador de literal cadeia de caracteres remove um nível de contrabarras, portanto o que chega ao analisador de valor de matriz se parece com {"\\","\""}. Por sua vez, as cadeias de caracteres introduzidas na rotina de entrada de dado do tipo text se tornan \ e ", respectivamente (Se estivéssemos trabalhando com um tipo de dado cuja rotina de entrada também tratasse contrabarras de forma especial como, por exemplo, bytea, seriam necessárias oito contrabarras no comando para obter uma contrabarra armazenada no elemento da matriz).