Uma coluna gerada é uma coluna especial, sempre calculada a partir de outras colunas. Assim, está para as colunas o que uma visão está para as tabelas. Existem dois tipos de colunas geradas: armazenadas e virtuais. Uma coluna gerada armazenada é computada quando é escrita (inserida ou atualizada), e ocupa o espaço de armazenamento como se fosse uma coluna normal. Uma coluna gerada virtual não ocupa espaço de armazenamento, sendo computada quando é lida. Assim, uma coluna gerada virtual equivale a uma visão, e uma coluna gerada armazenada é semelhante a uma visão materializada (exceto por ser atualizada automaticamente).
Para criar uma coluna gerada, deve ser usada a cláusula
GENERATED ALWAYS AS do comando
CREATE TABLE, como, por exemplo:
CREATE TABLE pessoa (
...,
altura_cm numeric,
altura_pol numeric GENERATED ALWAYS AS (height_cm / 2.54)
);
Uma coluna gerada é, por padrão, do tipo virtual.
Deve ser usada uma das palavras-chave
VIRTUAL ou STORED
para tornar a escolha explícita.
Veja CREATE TABLE para obter mais detalhes.
Uma coluna gerada não pode receber valor diretamente.
Nos comandos INSERT e UPDATE
não é possível especificar um valor para uma coluna gerada, mas pode
ser especificada a palavra-chave DEFAULT.
Vejamos as diferenças entre uma coluna com valor padrão e uma
coluna gerada. O valor padrão da coluna é avaliado só uma vez quando
a linha é inserida pela primeira vez, se nenhum outro valor for
fornecido; uma coluna gerada é atualizada sempre que a linha é
alterada e não pode ser mudada. Um valor padrão de coluna não pode
se referir a outras colunas da tabela; uma expressão de geração
normalmente faria isso. Um valor padrão de coluna pode usar
funções voláteis como, por exemplo, random(), ou
funções referentes à hora corrente; isso não é permitido para as
colunas geradas.
Várias restrições se aplicam à definição de colunas geradas e tabelas envolvendo colunas geradas:
A expressão de geração só pode usar funções imutáveis, e não pode usar subconsultas ou fazer referência a nada além da linha corrente.
Uma expressão de geração não pode fazer referência a outra coluna gerada.
Uma expressão de geração não pode fazer referência a uma coluna
do sistema, exceto tableoid.
Uma coluna virtual gerada não pode ter um tipo de dados definido pelo usuário, e a expressão de geração de uma coluna virtual gerada não deve fazer referência a funções ou tipos de dados definidos pelo usuário, ou seja, só pode usar funções ou tipos de dados predefinidos. Isto também se aplica indiretamente, como em funções ou tipos de dados que estão na base de operadores ou conversões de tipos de dados. (Esta restrição não se aplica para colunas geradas armazenadas.)
Uma coluna gerada não pode ter valor padrão ou ser definida como coluna de identidade.
Uma coluna gerada não pode fazer parte de uma chave de partição.
Tabelas estrangeiras podem ter colunas geradas. Veja CREATE FOREIGN TABLE para obter mais detalhes.
Para herança e particionamento:
Se uma coluna mãe for uma coluna gerada, sua coluna filha também deverá ser uma coluna gerada do mesmo tipo (armazenada ou virtual); Entretanto, a coluna filha pode ter uma expressão de geração diferente.
Para colunas geradas armazenadas, a expressão de geração que é efetivamente aplicada durante a inserção ou atualização de uma linha é aquela associada à tabela na qual a linha está fisicamente localizada. (Isso difere do comportamento dos valores padrão de coluna: para estes, aplica-se o valor padrão associado à tabela indicada na consulta.) Para colunas virtuais geradas, a expressão de geração da tabela indicada na consulta se aplica quando uma tabela é lida.
Se uma coluna mãe não for uma coluna gerada, sua coluna filha também não deverá ser uma coluna gerada.
Para tabelas herdadas, se for escrita uma definição de coluna
filha sem nenhuma cláusula GENERATED
no comando CREATE TABLE ... INHERITS,
então sua cláusula GENERATED
será copiada automaticamente da tabela mãe.
O comando ALTER TABLE ... INHERIT irá insistir
que as colunas mãe e filha se correspondam com relação ao status
de geração, mas não irá exigir que suas expressões de geração
correspondam.
Da mesma forma, para tabelas particionadas se for escrita uma
definição de coluna filha sem nenhuma cláusula
GENERATED no comando
CREATE TABLE ... PARTITION OF,
então será copiada a cláusula GENERATED
de sua mãe automaticamente.
O comando ALTER TABLE ... ATTACH PARTITION
irá insistir que as colunas mãe e filha se correspondam com
relação ao status de geração, mas não irá exigir que suas
expressões de geração correspondam.
No caso de herança múltipla, se uma coluna mãe for uma coluna gerada, então todas as colunas mãe também deverão ser colunas geradas. Caso nem todas possuam a mesma expressão de geração, a expressão desejada para a filha deverá ser especificada explicitamente.
Considerações adicionais se aplicam ao uso de colunas geradas.
As colunas geradas mantêm os privilégios de acesso em separado de suas colunas-base subjacentes. Portanto, pode ser organizado de tal forma que uma função específica possa ler os dados de uma coluna gerada, mas não possa ler os dados de suas colunas-base subjacentes.
Para colunas virtuais geradas, isto só é totalmente seguro se a expressão de geração usar apenas funções à prova de vazamento de dados (veja CREATE FUNCTION), mas isto não é imposto pelo sistema.
Os privilégios das funções usadas nas expressões de geração são
verificados quando a expressão é efetivamente executada,
seja na escrita ou na leitura, respectivamente, como se a
expressão de geração tivesse sido chamada diretamente da consulta
usando a coluna gerada.
O usuário de uma coluna gerada deve ter permissão para chamar
todas as funções usadas pela expressão de geração.
As funções na expressão de geração são executadas com os
privilégios do usuário que executa a consulta ou do dono da função,
dependendo se as funções foram definidas como
SECURITY INVOKER ou
SECURITY DEFINER.
Conceitualmente, as colunas geradas são atualizadas após a
execução dos gatilhos BEFORE.
Portanto, as alterações feitas nas colunas base em um gatilho
BEFORE serão refletidas nas colunas geradas.
Mas, por outro lado, não é permitido acessar colunas geradas
nos gatilhos BEFORE.
As colunas geradas podem ser replicadas durante a replicação
lógica usando o parâmetro
publish_generated_columns
do comando CREATE PUBLICATION,
ou incluindo-as na lista de colunas do comando
CREATE PUBLICATION.
No momento, isto só é permitido para colunas geradas armazenadas.
Veja a Seção 29.6 para oter
mais detalhes.
Exemplo 5.1. Exemplo do tradutor
Complementado o exemplo das colunas de identidade visto no tópico anterior com o exemplo deste tópico teremos:
DROP TABLE pessoa;
CREATE TABLE pessoa (
id bigint GENERATED BY DEFAULT AS IDENTITY,
nome text,
endereço text,
altura_cm numeric,
altura_pol numeric GENERATED ALWAYS AS (altura_cm / 2.54) STORED
);
INSERT INTO pessoa (nome, endereço, altura_cm) VALUES ('A', 'foo', 150);
INSERT INTO pessoa (nome, endereço, altura_cm) VALUES ('B', 'bar', 160);
INSERT INTO pessoa (id, nome, endereço, altura_cm) VALUES (DEFAULT, 'C', 'baz', 170);
INSERT INTO pessoa (id, nome, endereço, altura_cm) VALUES (4, 'D', 'bax', 175);
INSERT INTO pessoa VALUES (DEFAULT, 'E', 'baz', 180, DEFAULT);
SELECT * FROM pessoa;
id | nome | endereço | altura_cm | altura_pol ----+------+----------+-----------+--------------------- 1 | A | foo | 150 | 59.0551181102362205 2 | B | bar | 160 | 62.9921259842519685 3 | C | baz | 170 | 66.9291338582677165 4 | D | bax | 175 | 68.8976377952755906 4 | E | baz | 180 | 70.8661417322834646 (5 linhas)