As regras ON SELECT são aplicadas a todas as consultas como último passo, mesmo que o comando seja um INSERT, UPDATE, ou DELETE. E elas têm uma semântica diferente das regras nos outros tipos de comando, porque modificam a árvore de consulta, em vez de criar uma nova. Portanto, as regras SELECT são descritas primeiro.

No momento, pode haver apenas uma ação em uma regra ON SELECT, e deve ser uma ação SELECT incondicional que é INSTEAD. Esta restrição foi necessária para tornar as regras seguras o suficiente para abri-las para os usuários comuns, restringindo as regras ON SELECT para agir como visões.

Os exemplos desse capítulo são duas visões de junção que fazem alguns cálculos, e mais algumas visões usando-as alternadamente. Uma das duas primeiras visões é personalizada posteriormente, adicionando regras para as operações INSERT, UPDATE, e DELETE, para que o resultado final seja uma visão que se comporte como uma tabela real com algumas funcionalidades mágicas. Este não é um exemplo tão simples para começar, tornando as coisas mais difíceis de entender. Mas é melhor ter um exemplo que abranja todos os pontos discutidos passo a passo, em vez de ter muitos exemplos diferentes que podem se misturar na mente.

As tabelas reais necessárias para as duas primeiras descrições do sistema de regras são as seguintes:

CREATE TABLE sapato_dados (
    sapato_nome         text,       -- chave primária
    sapato_disp         integer,    -- número de pares disponíveis
    cadarço_cor_pref    text,       -- cor do cadarço preferida
    cadarço_min_comp    real,       -- comprimento mínimo do cadarço
    cadarço_max_comp    real,       -- comprimento máximo do cadarço
    cadarço_sap_unid    text,       -- unidade de comprimento
    CONSTRAINT pk_sapato_dados PRIMARY KEY(sapato_nome)
);

CREATE TABLE cadarço_dados (
    cadarço_nome        text,       -- chave primária
    cadarço_disp        integer,    -- número de pares disponíveis
    cadarço_cor         text,       -- cor do cadarço
    cadarço_comp        real,       -- comprimento do cadarço
    cadarço_unid        text,       -- unidade de comprimento
    CONSTRAINT pk_cadarço_dados PRIMARY KEY(cadarço_nome)
);

CREATE TABLE unidade (
    unidade_nome        text,       -- chave primária
    unidade_fator       real,       -- fator para transformar em cm
    CONSTRAINT pk_unidade PRIMARY KEY(unidade_nome)
);

Como se pode ver, estas tabelas representam os dados de lojas de calçados.

As visões são criadas como:

CREATE VIEW sapato AS
    SELECT s.sapato_nome,
           s.sapato_disp,
           s.cadarço_cor_pref,
           s.cadarço_min_comp,
           s.cadarço_min_comp * u.unidade_fator AS cadarço_min_comp_cm,
           s.cadarço_max_comp,
           s.cadarço_max_comp * u.unidade_fator AS cadarço_max_comp_cm,
           s.cadarço_sap_unid
    FROM sapato_dados s, unidade u
    WHERE s.cadarço_sap_unid = u.unidade_nome;

CREATE VIEW cadarço AS
    SELECT c.cadarço_nome,
           c.cadarço_disp,
           c.cadarço_cor,
           c.cadarço_comp,
           c.cadarço_unid,
           c.cadarço_comp * u.unidade_fator AS cadarço_comp_cm
    FROM cadarço_dados c, unidade u
    WHERE c.cadarço_unid = u.unidade_nome;

CREATE VIEW sapato_pronto AS
    SELECT s.sapato_nome,
           s.sapato_disp,
           c.cadarço_nome,
           c.cadarço_disp,
           least(s.sapato_disp, c.cadarço_disp) AS total_disp
    FROM sapato s, cadarço c
    WHERE c.cadarço_cor = s.cadarço_cor_pref
      AND c.cadarço_comp_cm >= s.cadarço_min_comp_cm
      AND c.cadarço_comp_cm <= s.cadarço_max_comp_cm;

O comando CREATE VIEW para a visão cadarço (que é a mais simples que temos) irá criar a relação cadarço, e uma entrada em pg_rewrite informando que existe uma regra de reescrita que deve ser aplicada sempre que a relação cadarço for referenciada em tabela de intervalos de uma consulta. A regra não tem qualificação de regra (discutida mais tarde, com as regras não-SELECT, já que as regras SELECT, no momento, não podem tê-las), sendo INSTEAD. Note que as qualificações de regra não são iguais às qualificações de consulta. A ação de nossa regra possui uma qualificação de consulta. A ação da regra é uma árvore de consulta que é uma cópia da instrução SELECT no comando de criação de visão.

Agora vamos carregar as tabelas unidade, sapato_dados e cadarço_dados, e executar uma consulta simples em uma visão:

INSERT INTO unidade VALUES ('cm', 1.0);
INSERT INTO unidade VALUES ('m', 100.0);
INSERT INTO unidade VALUES ('pol', 2.54);

INSERT INTO sapato_dados VALUES ('sap1', 2, 'preto', 70.0, 90.0, 'cm');
INSERT INTO sapato_dados VALUES ('sap2', 0, 'preto', 30.0, 40.0, 'pol');
INSERT INTO sapato_dados VALUES ('sap3', 4, 'marrom', 50.0, 65.0, 'cm');
INSERT INTO sapato_dados VALUES ('sap4', 3, 'marrom', 40.0, 50.0, 'pol');

INSERT INTO cadarço_dados VALUES ('cad1', 5, 'preto', 80.0, 'cm');
INSERT INTO cadarço_dados VALUES ('cad2', 6, 'preto', 100.0, 'cm');
INSERT INTO cadarço_dados VALUES ('cad3', 0, 'preto', 35.0 , 'pol');
INSERT INTO cadarço_dados VALUES ('cad4', 8, 'preto', 40.0 , 'pol');
INSERT INTO cadarço_dados VALUES ('cad5', 4, 'marrom', 1.0 , 'm');
INSERT INTO cadarço_dados VALUES ('cad6', 0, 'marrom', 0.9 , 'm');
INSERT INTO cadarço_dados VALUES ('cad7', 7, 'marrom', 60 , 'cm');
INSERT INTO cadarço_dados VALUES ('cad8', 1, 'marrom', 40 , 'pol');

SELECT * FROM cadarço;

 cadarço_nome | cadarço_disp | cadarço_cor | cadarço_comp | cadarço_unid | cadarço_comp_cm
--------------+--------------+-------------+--------------+--------------+-----------------
 cad1         |            5 | preto       |           80 | cm           |              80
 cad2         |            6 | preto       |          100 | cm           |             100
 cad3         |            0 | preto       |           35 | pol          |            88.9
 cad4         |            8 | preto       |           40 | pol          |           101.6
 cad5         |            4 | marrom      |            1 | m            |             100
 cad6         |            0 | marrom      |          0.9 | m            |              90
 cad7         |            7 | marrom      |           60 | cm           |              60
 cad8         |            1 | marrom      |           40 | pol          |           101.6
(8 linhas)

Este é o comando SELECT mais simples que se pode fazer com estas visões, então vamos aproveitar a oportunidade para explicar o básico das regras de visão. O comando SELECT * FROM cadarço foi interpretado pelo analisador e produziu a árvore de consulta:

SELECT cadarço.cadarço_nome, cadarço.cadarço_disp,
       cadarço.cadarço_cor, cadarço.cadarço_comp,
       cadarço.cadarço_unid, cadarço.cadarço_comp_cm
  FROM cadarço cadarço;

e isto é entregue ao sistema de regras. O sistema de regras percorre a tabela de intervalos e verifica se existem regras para qualquer relação. Ao processar a entrada da tabela de intervalos para cadarço (única até agora), encontra a regra _RETURN com a árvore de consulta:

SELECT s.cadarço_nome, s.cadarço_disp,
       s.cadarço_cor, s.cadarço_comp, s.cadarço_unid,
       s.cadarço_comp * u.unidade_fator AS cadarço_comp_cm
  FROM cadarço old, cadarço new,
       cadarço_dados s, unidade u
 WHERE s.cadarço_unid = u.unidade_nome;

Para expandir a visão, o reescritor simplesmente cria uma entrada na tabela de intervalos da subconsulta contendo a árvore de consulta de ação da regra, e substitui esta entrada de tabela de intervalos pela original que referenciava a visão. A árvore de consulta reescrita resultante é quase a mesma como se tivesse sido digitado:

SELECT cadarço.cadarço_nome, cadarço.cadarço_disp,
       cadarço.cadarço_cor, cadarço.cadarço_comp,
       cadarço.cadarço_unid, cadarço.cadarço_comp_cm
  FROM (SELECT s.cadarço_nome,
               s.cadarço_disp,
               s.cadarço_cor,
               s.cadarço_comp,
               s.cadarço_unid,
               s.cadarço_comp * u.unidade_fator AS cadarço_comp_cm
          FROM cadarço_dados s, unidade u
         WHERE s.cadarço_unid = u.unidade_nome) cadarço;

Porém, há uma diferença: a tabela de intervalos da subconsulta tem duas entradas extras cadarço old e cadarço new. Estas entradas não participam diretamente da consulta, porque não são referenciadas pela árvore de junção ou lista de destino da subconsulta. O reescritor as usa para armazenar as informações de verificação de privilégio de acesso que estavam originalmente presentes na entrada da tabela de intervalos que referenciava a visão. Dessa forma, o executor ainda verificará se o usuário possui os privilégios adequados para acessar a visão, mesmo que não haja uso direto da visão na consulta reescrita.

Esta foi a primeira regra aplicada. O sistema de regras continuará verificando as entradas restantes da tabela de intervalos na consulta principal (neste exemplo não há mais), e verificará recursivamente as entradas da tabela de intervalos na subconsulta adicionada, para ver se alguma delas faz referência a visões. (Mas não expandirá old ou new — caso contrário, teríamos recursão infinita!) Neste exemplo, não há regras de reescrita para cadarço_dados ou unidade, então a reescrita está completa, e o resultado acima é o resultado final entregue ao planejador.

Agora, pode-se escrever uma consulta que descubra para quais sapatos atualmente na loja tem os cadarços correspondentes (cor e comprimento), e onde o número total de pares exatamente correspondentes é maior ou igual a dois.

SELECT * FROM sapato_pronto WHERE total_disp >= 2;

 sapato_nome | sapato_disp | cadarço_nome | cadarço_disp | total_disp
-------------+-------------+--------------+--------------+-------------
 sap1        |           2 | cad1         |            5 |           2
 sap3        |           4 | cad7         |            7 |           4
(2 linhas)

A saída do analisador dessa vez é a árvore de consulta:

SELECT sapato_pronto.sapato_nome, sapato_pronto.sapato_disp,
       sapato_pronto.cadarço_nome, sapato_pronto.cadarço_disp,
       sapato_pronto.total_disp
  FROM sapato_pronto sapato_pronto
 WHERE sapato_pronto.total_disp >= 2;

A primeira regra aplicada será a da visão sapato_pronto, resultando na árvore de consulta:

SELECT sapato_pronto.sapato_nome, sapato_pronto.sapato_disp,
       sapato_pronto.cadarço_nome, sapato_pronto.cadarço_disp,
       sapato_pronto.total_disp
  FROM (SELECT s.sapato_nome,
               s.sapato_disp,
               c.cadarço_nome,
               c.cadarço_disp,
               least(s.sapato_disp, c.cadarço_disp) AS total_disp
          FROM sapato s, cadarço c
         WHERE c.cadarço_cor = s.cadarço_cor_pref
           AND c.cadarço_comp_cm >= s.cadarço_min_comp_cm
           AND c.cadarço_comp_cm <= s.cadarço_max_comp_cm) sapato_pronto
 WHERE sapato_pronto.total_disp >= 2;

Da mesma forma, as regras para sapato e cadarço são substituídas na tabela de intervalos da subconsulta, levando a uma árvore de consulta final de três níveis:

SELECT sapato_pronto.sapato_nome, sapato_pronto.sapato_disp,
       sapato_pronto.cadarço_nome, sapato_pronto.cadarço_disp,
       sapato_pronto.total_disp
  FROM (SELECT rs.sapato_nome,
               rs.sapato_disp,
               rc.cadarço_nome,
               rc.cadarço_disp,
               least(rs.sapato_disp, rc.cadarço_disp) AS total_disp
          FROM (SELECT s.sapato_nome,
                       s.sapato_disp,
                       s.cadarço_cor_pref,
                       s.cadarço_min_comp,
                       s.cadarço_min_comp * un.unidade_fator AS cadarço_min_comp_cm,
                       s.cadarço_max_comp,
                       s.cadarço_max_comp * un.unidade_fator AS cadarço_max_comp_cm,
                       s.cadarço_sap_unid
                  FROM sapato_dados s, unidade un
                 WHERE s.cadarço_sap_unid = un.unidade_nome) rs,
               (SELECT c.cadarço_nome,
                       c.cadarço_disp,
                       c.cadarço_cor,
                       c.cadarço_comp,
                       c.cadarço_unid,
                       c.cadarço_comp * u.unidade_fator AS cadarço_comp_cm
                  FROM cadarço_dados c, unidade u
                 WHERE c.cadarço_unid = u.unidade_nome) rc
         WHERE rc.cadarço_cor = rs.cadarço_cor_pref
           AND rc.cadarço_comp_cm >= rs.cadarço_min_comp_cm
           AND rc.cadarço_comp_cm <= rs.cadarço_max_comp_cm) sapato_pronto
 WHERE sapato_pronto.total_disp >= 2;

Isto pode parecer ineficiente, mas o planejador irá reduzir a uma árvore de consulta de nível único, “incorporando” as subconsultas e, em seguida, planejando as junções como se tivessem sido escritas manualmente. Portanto, colapsar a árvore de consulta é uma otimização com a qual o sistema de reescrita não precisa se preocupar.

Dois detalhes da árvore de consulta não foram tocados na descrição das regras de visão acima. Eles são o tipo de comando e a relação de resultado. Na verdade, o tipo de comando não é necessário para as regras de visão, mas a relação de resultado pode afetar a maneira como o reescritor de consulta funciona, porque é necessário ter um cuidado especial se a relação de resultado for uma visão.

Existem apenas algumas diferenças entre uma árvore de consulta para um comando SELECT e para qualquer outro comando. Obviamente, eles têm um tipo de comando diferente, e para um comando diferente de SELECT a relação do resultado aponta para a entrada da tabela de intervalos onde o resultado deve ir. Todo o resto é absolutamente o mesmo. Então, tendo as duas tabelas t1 e t2, com as colunas a e b, as árvores de consulta para os dois comandos

SELECT t2.b FROM t1, t2 WHERE t1.a = t2.a;

UPDATE t1 SET b = t2.b FROM t2 WHERE t1.a = t2.a;

são quase idênticas. Em particular:

A consequência é que as duas árvores de consulta resultam em planos de execução semelhantes: as duas são junções nas duas tabelas. Para o UPDATE, as colunas ausentes de t1 são adicionadas à lista de destino pelo planejador, e a árvore de consulta final será lida como:

UPDATE t1 SET a = t1.a, b = t2.b FROM t2 WHERE t1.a = t2.a;

e, portanto, o executor executado na junção produzirá exatamente o mesmo conjunto de resultados como:

SELECT t1.a, t2.b FROM t1, t2 WHERE t1.a = t2.a;

Mas há um pequeno problema no UPDATE: a parte do plano do executor que faz a junção não se importa para o que os resultados da junção se destinam. Apenas produz um conjunto de resultados de linhas. O fato de um ser um comando SELECT e o outro ser um comando UPDATE, é tratado num nível mais alto no executor, onde ele sabe que isto é um UPDATE, e sabe que o resultado deverá ir para a tabela t1. Mas qual das linhas existentes deverá ser substituída pela nova linha?

Para resolver este problema, outra entrada é adicionada à lista de destino nas instruções UPDATE (e também em DELETE): o ID da tupla corrente (CTID). Esta é uma coluna do sistema que contém o número do bloco de arquivo e a posição no bloco para a linha. Conhecendo a tabela, o CTID pode ser usado para recuperar a linha original de t1 a ser atualizada. Após adicionar o CTID à lista de destino, a consulta realmente se parece com:

SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a;

Agora outro detalhe do PostgreSQL entra em cena. As linhas antigas da tabela não são substituídas, sendo por isto que o ROLLBACK é rápido. Em um UPDATE, a nova linha do resultado é inserida na tabela (após remover o CTID), e no cabeçalho da linha antiga, para a qual o CTID apontava, as entradas cmax e xmax são definidas para o contador de comando corrente e ID da transação corrente. Assim, a linha antiga fica oculta e, após a efetivação da transação, o coletor de lixo (vacuum cleaner) poderá, por fim, remover a linha morta.

Sabendo de tudo isto, pode-se simplesmente aplicar as regras de visão absolutamente da mesma maneira a qualquer comando. Não há diferença.

Acima foi mostrado como o sistema de regras incorpora definições de visão na árvore de consulta original. No segundo exemplo, um simples SELECT de uma visão criou uma árvore de consulta final que é a junção de 4 tabelas (unidade foi usada duas vezes com nomes diferentes).

O benefício de implementar visões com o sistema de regras, é que o planejador tem todas as informações sobre quais tabelas devem ser verificadas, mais as relações entre estas tabelas, mais as qualificações restritivas das visões, mais as qualificações da consulta original, em uma única árvore de consulta. E esta ainda é a situação quando a consulta original já é uma junção de visões. O planejador tem que decidir qual é o melhor caminho para executar a consulta, e quanto mais informações o planejador tiver, melhor será esta decisão. E o sistema de regras implementado no PostgreSQL garante que esta seja toda a informação disponível sobre a consulta até aquele ponto.

O que acontece se uma visão for indicada como a relação de destino para um comando INSERT, UPDATE, DELETE ou MERGE? Fazer as substituições descritas acima daria uma árvore de consulta na qual a relação de resultado aponta para uma entrada da tabela de intervalos da subconsulta, o que não irá funcionar. Entretanto, existem várias maneiras pelas quais o PostgreSQL pode oferecer suporte à aparente atualização de uma visão. Em ordem de complexidade da experiência do usuário, elas são: substituir automaticamente na tabela subjacente à visão, executar um gatilho definido pelo usuário, ou reescrever a consulta de acordo com uma regra definida pelo usuário. Estas opções são discutidas a seguir.

Se a subconsulta selecionar uma única relação de base, e for simples o suficiente, o reescritor poderá substituir automaticamente a subconsulta pela relação base subjacente, para que o comando INSERT, UPDATE, DELETE ou MERGE seja aplicado à relação base da forma apropriada. As visões que são “simples o suficiente” para isto acontecer são chamadas de atualizáveis automaticamente. Para obter informações detalhadas sobre os tipos de visão que podem ser atualizadas automaticamente, veja CREATE VIEW.

Como alternativa, a operação pode ser tratada por um gatilho INSTEAD OF fornecido pelo usuário na visão (veja CREATE TRIGGER). A reescrita funciona de maneira ligeiramente diferente neste caso. Para o comando INSERT, o reescritor não faz nada com a visão, deixando-a como a relação de resultado para a consulta. Para os comandos UPDATE, DELETE e MERGE ainda será necessário expandir a consulta da visão para produzir as linhas “antigas” que o comando tentará atualizar ou excluir. Portanto, a visão é expandida normalmente, mas é adicionada à consulta outra entrada não expandida na tabela de intervalos para representar a visão em sua capacidade como a relação de resultado.

O problema que surge agora é como identificar as linhas a serem atualizadas na visão. Lembre-se de que quando a relação de resultado é uma tabela, a entrada especial CTID é adicionada à lista de destino para identificar os locais físicos das linhas a serem atualizadas. Isto não funciona se a relação de resultado for uma visão, porque uma visão não possui nenhum CTID, porque suas linhas não possuem localizações físicas reais. Em vez disso, para uma operação de UPDATE, DELETE ou MERGE, a entrada especial wholerow é adicionada à lista de destino, que se expande para incluir todas as colunas da visão. O executor usa este valor para fornecer a linha “old” para o gatilho INSTEAD OF. Cabe ao gatilho descobrir o que atualizar com base nos valores de linha antigos e novos.

Outra possibilidade é o usuário definir regras INSTEAD especificando ações substitutas para os comandos INSERT, UPDATE e DELETE em uma visão. Estas regras vão reescrever o comando, geralmente em um comando que atualiza uma ou mais tabelas, em vez de visões. Este é o tópico de Regras para INSERT, UPDATE e DELETE. Note que isto não funcionará com o comando MERGE, que no momento não oferece suporte a regras na relação de destino além das regras do comando SELECT.

Note que as regras são avaliadas primeiro, reescrevendo a consulta original antes que ela seja planejada e executada. Portanto, se a visão tiver gatilhos INSTEAD OF, bem como regras para INSERT, UPDATE, ou DELETE, então as regras serão avaliadas primeiro e, dependendo do resultado, os gatilhos poderão não ser usados.

A reescrita automática de uma consulta INSERT, UPDATE, DELETE ou MERGE em uma visão simples é sempre tentada por último. Portanto, se uma visão tiver regras ou gatilhos, eles substituirão o comportamento padrão de visões atualizáveis automaticamente.

Se não houver regras INSTEAD ou gatilhos INSTEAD OF para a visão, e o reescritor não puder reescrever automaticamente a consulta como uma atualização na relação base subjacente, será relatado um erro, porque o executor não pode atualizar uma visão diretamente.