Como funcionam as Views no PostgreSQL

Neste artigo trataremos da criação e exclusão das Views no PostgreSQL. Conheceremos sua estrutura básica e aprenderemos a usá-las em nossas consultas para obter resultados de forma simplificada.

Primeiramente, precisamos entender o que são as Views, que são consideradas pseudo-tables, ou seja, elas são usadas junto a instrução SELECT para apresentar subconjuntos de dados presentes em tabelas reais. Assim, podemos apresentar as colunas e linhas que foram selecionadas da tabela original ou associada. E como as Views possuem permissões separadas, podemos utilizá-las para restringir mais o acesso aos dados pelos usuários, para que veja apenas o que é necessário.

Vamos começar apresentando a sintaxe básica de uma view, conforme a Listagem 1.

Listagem 1. Sintaxe de criação de uma View.

CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] VIEW name 
    [ ( column_name [, ...] ) ] 
    [ WITH ( view_option_name [= view_option_value] [, ... ] ) ]
    AS query
    [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

O código começa com a instrução de criação CREATE VIEW. E como ela é virtual, sempre que uma consulta é executada, a View é referenciada na consulta.

A instrução CREATE [ OR REPLACE ] VIEW é semelhante a anterior, mas verifica se já existe uma view com o mesmo nome e, caso exista, será substituída pela mais recente. O nome dado a nova view não pode ser o mesmo já atribuído também a qualquer outro item presente no banco.

Caso o nome do Schema tenha sido fornecido, a View será criada com base nele e não no Schema global. Quando temos um Schema sendo especificado, este não pode ser temporário, assim como as Views.

O parâmetro [ TEMPORARY | TEMP ] é especificado quando queremos que a View seja temporária, as quais são eliminadas de forma automática quando a sessão atual é finalizada. Caso tenhamos referenciado tabelas temporárias para a View, esta será criada como temporária mesmo que não tenhamos especificado este parâmetro.

Já o RECURSIVE cria Views recursivas, ou seja, aquelas que trabalham dentro delas próprias, como na sintaxe a seguir:

    CREATE VIEW name AS WITH RECURSIVE name (columns) AS (SELECT ...) SELECT columns FROM name;

Podemos também utilizar a seguinte sintaxe, que é uma versão reduzida:

CREATE RECURSIVE VIEW name (columns) AS SELECT ...;

Um exemplo real da aplicação desse conceito seria ter uma tabela de usuários, onde procuraríamos dentre estes quais são administradores:

CREATE VIEW administradores AS WITH RECURSIVE usuarios (columns) AS (SELECT ...) SELECT isAdministrador FROM usuarios;

O Name é o nome da View, podendo este ser opcional já o column_name, que é uma lista opcional que contém os nomes a serem utilizados para as colunas da View.

O check_option (string), pode ser local ou em cascata, e é equivalente à quando especificamos o WITH [CASCADED | LOCAL] CHECK OPTION. Esta opção pode ser alterada em Views existentes quando utilizamos a instrução ALTER VIEW. O security_barrier (string) é utilizado quando a View é criada com o intuito de fornecer segurança a nível de linha.

O WITH [ CASCADE | LOCAL] CHECK OPTION é uma opção utilizada para controle do comportamento da View com base nas atualizações automáticas. No caso desta opção ser especificada, os comandos de Insert e Update são verificados para garantir que as novas linhas satisfaçam as condições que definem a View. Caso não estejam em conformidade, a atualização não ocorrerá. Caso o CHECK OPTION não seja especificado, os comandos INSERT e UPDATE presentes na View serão autorizados a criar linhas não visíveis através da View.

As opções de verificação suportadas, são as seguintes:

• LOCAL – onde novas linhas são verificadas apenas contra as condições definidas diretamente na própria View.
• CASCADED – aqui novas linhas são verificadas em relação às condições da View e todas as Views subjacentes.

Se a cláusula CHECK OPTION for especificada e não tivermos a especificação da LOCAL nem da CASCADED, então o CASCADED é assumido. Precisamos ter cuidado apenas com as Views recursivas, pois o CHECK OPTION não pode ser utilizado nelas. Ou seja, esse parâmetro só é suportado apenas em Views que são atualizadas automaticamente e não possuem triggers do tipo Instead Of ou mesmo que contenham regras do tipo Instead. Se uma View que é atualizada automaticamente for definida com base em uma View que contenha uma trigger Instead of, então a LOCAL CHECK OPTION pode ser utilizada para verificar as condições sobre esta View, mas as condições referentes a View que contém a trigger INSTEAD OF não será verificada.

Vamos criar uma tabela funcionarios que será responsável por manter os registros reais, como mostra a Listagem 2.

Listagem 2. Criando a tabela de funcionários.

CREATE TABLE funcionarios
    (
      codigo integer NOT NULL,
      nome_func character varying(100) NOT NULL,
      data_entrada date,
      profissao character varying(100) NOT NULL,
      salario real,
      CONSTRAINT funcionarios_pkey PRIMARY KEY (codigo)
    )
    WITH (
      OIDS=FALSE
    );
    ALTER TABLE funcionarios
      OWNER TO postgres;

Vamos criar uma view para essa tabela usando a sintaxe a seguir:

CREATE VIEW view_funcionarios AS SELECT * FROM Funcionarios;

A view_funcionarios contém uma instrução SELECT para receber os dados da tabela. Para que possamos ver as views que criamos, podemos utilizar a seguinte declaração:

SELECT codigo, nome_func, profissao FROM view_funcionarios;

Uma View é um objeto que permite a visualização de dados da tabela a qual esteja associada. Como ela não existe por conta própria, é criada com base em consultas nas tabelas para selecionar colunas, dando assim acessos restritos ou com privilégios. Além disso possuem a capacidade de juntar informações contidas em diversas tabelas para representar em um único lugar como, por exemplo, na geração de relatórios.

Devido as suas especificações de restrição, os dados retornados são apenas aqueles que o usuário pode ver. E por serem tabelas virtuais, não podemos realizar por elas as operações DML de inserção, atualização e exclusão, mas sim apenas usando o SELECT.

Para exemplificarmos esse ponto utilizaremos a tabela funcionários em conjunto com uma nova tabela registro_ponto que conterá o registro de entrada e saída de cada um da empresa, conforme o código da Listagem 3. Veja que obteremos apenas o nome do funcionário, profissão, data e a hora de entrada.

Listagem 3. Criação da tabela de registro_ponto.

CREATE TABLE registro_ponto
    (
      registro_ponto_id integer NOT NULL,
      hora_entrada time without time zone,
      "codFunc" integer NOT NULL,
      entrada date,
      CONSTRAINT registro_ponto_pkey PRIMARY KEY (registro_ponto_id),
      CONSTRAINT "codFuncFK" FOREIGN KEY ("codFunc")
          REFERENCES funcionarios (codigo) MATCH SIMPLE
          ON UPDATE NO ACTION ON DELETE NO ACTION
    )
    WITH (
      OIDS=FALSE
    );
    ALTER TABLE registro_ponto
      OWNER TO postgres;
     
    -- Index: "fki_codFuncFK"
     
    -- DROP INDEX "fki_codFuncFK";
     
    CREATE INDEX "fki_codFuncFK"
      ON registro_ponto
      USING btree
      ("codFunc");

Agora criaremos uma segunda view View_Ponto_funcionario que utilizará o código do funcionário na cláusula where, como vemos a seguir:

CREATE VIEW View_Ponto_funcionario AS SELECT nome_func, profissao, entrada, hora_entrada FROM funcionarios, registro_ponto WHERE funcionarios.codigo = registro_ponto."codFunc";

Feito isso, podemos executar a instrução a seguir para ver a View criada:

SELECT * FROM View_Ponto_funcionario;

Para vermos o resultado inseriremos alguns registros em ambas as tabelas, como mostra a Listagem 4.

Listagem 4. Inserção de dados nas tabelas funcionarios e registro_ponto.

INSERT INTO funcionarios(codigo, nome_func, data_entrada, profissão, salario) VALUES (1, 'Edson Dionisio', '2015-09-01', 'Desenvolvedor Web', 2000.00);
    INSERT INTO funcionarios(codigo, nome_func, data_entrada, profissao) VALUES (2, 'Marília Késsia', '2013-02-01', 'Coordenadora', 5000.00);
    INSERT INTO funcionarios(codigo, nome_func, data_entrada, profissao) VALUES (3, 'Caroline França', '2015-06-15', 'Estéticista', 2500.00);
    INSERT INTO registro_ponto(registro_ponto_id, entrada, hora_entrada, "codFunc") VALUES (1, '2015-10-03', '13:00:00', 1);
    INSERT INTO registro_ponto(registro_ponto_id, entrada, hora_entrada, "codFunc") VALUES (2, '2015-11-04', '08:00:00', 2);
    INSERT INTO registro_ponto(registro_ponto_id, entrada, hora_entrada, "codFunc") VALUES (3, '2015-10-05', '08:00:00', 1);
    Feita a inserção dos dados, podemos ver as mudanças na nossa segunda View utilizando a seguinte instrução:
    SELECT * FROM View_Ponto_funcionario;

Como mostra a Figura 1, as informações sendo apresentadas na View contendo os dados solicitados das duas tabelas.

Figura 1. Resultado da consulta a View View_Ponto_funcionario.

Para excluir uma view usamos o seguinte comando:

DROP VIEW view_funcionarios;

Trabalhando com as Views materializadas

Como as Views são apenas para leitura e representação lógica dos dados que estão armazenados nas tabelas do banco de dados, podemos “materializadas”, ou seja, armazená-las fisicamente no disco.

A criação dessas Views, ao invés de novas tabelas, melhora o desempenho em operações de leitura. Os dados das Views materializadas serão então armazenados em uma tabela que pode ser indexada rapidamente quando esta for associada e também em momentos que precisemos atualizar as Views materializadas. Isso ocorre com frequência em Data warehouse e aplicações de Business Intelligence, por exemplo.

Ao contrário da View tradicional, que nos apresentam dados atualizados automaticamente, as Views materializadas precisam de um mecanismo de atualização. A sintaxe é apresentada na Listagem 5.

Listagem 5. Sintaxe básica de uma View materializada.

CREATE MATERIALIZED VIEW table_name
        [ (column_name [, ...] ) ]
        [ WITH ( storage_parameter [= value] [, ... ] ) ]
        [ TABLESPACE tablespace_name ]
        AS query
        [ WITH [ NO ] DATA ]

A instrução CREATE MATERIALIZED VIEW cria View e a consulta é executada para preenche-la. Para atualizar os dados usamos o comando REFRESH MATERIALIZED VIEW. Uma View materializada tem muitas das mesmas propriedades de uma tabela, mas não há suporte para materialização temporária ou geração automática de OIDs.

O parâmetro TABLESPACE tablespace_name é o nome do espaço de tabelas no qual a nova View materializada deve ser criada. Se este não for especificado, a consulta será realizada no default_tablespace.

Com base no exemplo que desenvolvemos nesse artigo, a instrução a seguir cria uma view materializada para a tabela funcionarios:

CREATE MATERIALIZED VIEW view_materializada_funcionario AS SELECT * FROM funcionarios WITH NO DATA;

Para inserir dados nessa view usaremos o código da Listagem 6.

Listagem 6. Inserindo dados na view materializada

INSERT INTO funcionarios (codigo, nome_func, data_entrada, profissao) VALUES (5, 'Gustavo França', '2014-10-11', 'Estagiário');
    INSERT INTO funcionarios (codigo, nome_func, data_entrada, profissao) VALUES (6, 'Mayara Silva', '2015-06-10', 'Analista de testes');
    INSERT INTO funcionarios (codigo, nome_func, data_entrada, profissao) VALUES (7, 'João dos testes', '2011-01-01', 'Gerente de negócios');
    INSERT INTO funcionarios (codigo, nome_func, data_entrada, profissao) VALUES (8, 'Marina França', '2012-03-07', 'Analista de negócios');
    INSERT INTO funcionarios (codigo, nome_func, data_entrada, profissao) VALUES (9, 'Paulo Dionisio', '2013-07-07', 'DBA Sênior');

Agora utilizaremos o comando SELECT para verificarmos se os registros foram realmente inseridos com sucesso:

SELECT * FROM view_materializada_funcionario;

Podemos ver que obtivemos um erro ao tentarmos consultar a nossa View, como mostra a Figura 2.

Figura 2. Visualização de erro ao consultar View materializada.

Este erro ocorreu porque a view não se atualizou automaticamente, então precisamos do comando Refresh Materialized View:

REFRESH MATERIALIZED VIEW view_materializada_funcionario;

Feito isso, e em seguida, tentando consultar novamente os dados e assim obtemos êxito.

Com o PostgreSQL 9.4 podemos consultar Views materializadas enquanto são atualizadas, porém, nas versões anteriores isso não é possível. Para esse procedimento utilizamos a palavra-chave CONCURRENTLY, como mostra o comando a seguir:

REFRESH MATERIALIZED VIEW CONCURRENTLY view_materializada_funcionario;

Para que isso ocorra, um índice exclusivo passa a ser necessário para existir na View materializada. Sendo assim, ao executarmos o comando select novamente, teremos todos os dados atualizados, como podemos ver na Figura 3.

Figura 3. Selecionando dados da View Materializada.

Para excluir esse tipo de view basta utilizarmos o seguinte comando:

DROP MATERIALIZED VIEW view_materializada_funcionario;

Todos os recursos de otimização são importantes, pois lembre-se que as views são apenas visões dos dados e são carregadas apenas quando necessárias. Mas elas criam uma camada extra para administrar e podem limitar exageradamente, impedindo certas tarefas.

Além disso, não confunda uma view materializada com uma trigger, pois apesar de funcionarem de forma semelhante, a trigger tem muito mais poder sobre a tabela.

Esperamos que tenham gostado. Até a próxima!

Links

Documentação da View
http://www.postgresql.org/docs/9.4/static/sql-createview.html