Conceito de Schema no PostgreSQL

Entenda um pouco mais sobre Schema no PostgreSQL

Muitas vezes sentimos a necessidade de usar o conceito de cross-database, isto é, criar consultas SQL cruzando informações entre campos de tabelas que estão em Banco de Dados distintos. Exemplo, a tabela pessoa tem o campo id do banco database1, vamos cruzar informações com a tabela pessoa com o campo id do banco database2 gerando uma consulta SQL como esta:

SELECT db1.id, db2.id FROM database1.pessoa db1, Database2.pessoa db2

Logicamente, esta é uma query ilustrativa, mas serve como exemplo para demonstrar o que é um Cross-Database. Fazendo uma referência como esta, é possível acessar diversos objetos do banco, por exemplo, db2 (tables, views, indexes, functions etc.).

Apenas com a query ilustrativa podemos imaginar o quanto isso é útil. Nossas aplicações agora podem ser divididas em diversos bancos por diversos motivos: Segurança e organização são apenas dois exemplos.

A partir da versão 7.3, o PostgreSQL iniciou o suporte a Schema, no qual é possível criar um espaço lógico (namespace) dentro do banco de dados para armazenar os objetos: Dados, tabelas, funções, sequence, etc.

Este conceito – SCHEMA - é semelhante ao cross-database, a diferença é que o cross-database relaciona objetos (Tabelas, índices, sequence, etc) de banco de dados distintos, já o Schema relaciona objetos (Tabelas, índices, sequence, etc) que estão no MESMO BANCO DE DADOS, mas em estruturas lógicas (namespace) distintas – schema.

Exemplo

Vejamos uma demonstração básica do Esquema, dentro do banco teste:

teste_carlos=# CREATE SCHEMA sch_sqlmagazine1; CREATE SCHEMA teste_carlos=# CREATE TABLE sch_sqlmagazine1.info (id INT, txt TEXT); CREATE TABLE teste_carlos=# INSERT INTO sch_sqlmagazine1.info VALUES(1, 'Este é o Schema sch_sqlmagazine1'); INSERT 149312822 1 teste_carlos=# CREATE SCHEMA sch_sqlmagazine2; CREATE SCHEMA teste_carlos=# CREATE TABLE sch_sqlmagazine2.info (id INT, txt TEXT); CREATE TABLE teste_carlos=# INSERT INTO sch_sqlmagazine2.info VALUES(1, 'Este é o Schema sch_sqlmagazine2'); INSERT 149312829 1 teste_carlos=# SELECT sch_sqlmagazine1.info.txt, teste_carlos=# sch_sqlmagazine2.info.txt teste_carlos-# FROM sch_sqlmagazine1.info, sch_sqlmagazine2.info teste_carlos-# WHERE teste_carlos-# sch_sqlmagazine1.info.id=sch_sqlmagazine2.info.id;

txt

txt

Este é o Schema sch_sqlmagazine1
(1 row)

Este é o Schema sch_sqlmagazine2

SELECT sch_sqlmagazine1.info.txt Sch_sqlmagazine1 = Nossa Schema Info = Tabela Txt = Coluna.

Logo, estou buscando os dados da coluna txt que está na tabela info do Schema sch_sqlmagazine1.

O Schema Público

Quando criamos um novo Banco de Dados, o schema “public” é criado por padrão e qualquer objeto que criarmos neste banco, por padrão será criado no schema public:

teste_carlos=# create table tabela_exemplo(id int4); teste_carlos=# \dt

List of relations

Schema

Name

Type

Owner

public
(1 rows)

tabela_exemplo

table

postgres

Uso Prático do Schema

Como foi comentado, qualquer objeto criado sem especificar um SCHEMA é criado por padrão no public. No entanto, é possível definirmos um OUTRO schema como padrão através de uma variável interna do PostgreSQL chamado search_path. Esta variável informa ao banco PostgreSQL qual é o Usuário Padrão e o Schema padrão para manipular os objetos.

teste_carlos=# SHOW search_path; search_path -------------- $user,public (1 row)

Nestas configurações-padrão, o $user é o nome do usuário corrente, ou seja, o usuário no qual logamos no Banco de Dados:

postgres@venus:~$ psql teste_carlos -U postgres
Welcome to psql 7.3.6, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

teste_carlos=#

Podemos mudar o Schema padrão alterando nosso Search_Path:

teste_carlos=# SET search_path TO sch_sqlmagazine1; SET teste_carlos=# SHOW search_path; search_path ------------------ sch_sqlmagazine1 (1 row) teste_carlos=# SET search_path TO sch_sqlmagazine1; teste_carlos=# \dv No relations found. teste_carlos=# \dt

List of relations

Schema

Name

Type

Owner

sch_sqlmagazine1
(1 rows)

info

table

postgres

Veja como alterar o Schema padrão de forma permanente:

ALTER Database teste_carlos SET search_path TO $user,sch_sqlmagazine1; teste=# SET search_path TO sch_sqlmagazine2, sch_sqlmagazine1; SET

Como o Schema sch_sqlmagazine1 é agora o padrão:

teste_carlos=# SELECT txt FROM info; Txt ---------------------------------- Este é o Schema sch_sqlmagazine1 (1 row) teste_carlos-# \dt

List of relations

Schema

Name

Type

Owner

sch_sqlmagazine1
(1 rows)

info

table

postgres

Permissão e Segurança

Um schema só pode ser criado por usuários com permissão para isso (geralmente o usuário ”postgres”).

Para criar um Schema no qual queremos que OUTRO usuário (que não seja o usuário “postgres”) acesse o Schema (Tarzan), fazemos o seguinte:

CREATE SCHEMA tarzan AUTHORIZATION tarzan;

ou

CREATE SCHEMA AUTHORIZATION tarzan;

No caso, criamos o schema tarzan que será acessado pelo usuário tarzan.

Schemas Especiais

Todo Banco de Dados PostgreSQL contém schemas especiais que são necessários para o Backend (processo postgreSQL que estabelece a conexão com o cliente) por isso, eles não podem ser removidos ou renomeados.

Os Schemas especiais:

Limitações

Não é possível transferir objetos entre Schemas. Para solucionar isso, precisamos contornar o problema usando SQL. Por exemplo: com a query a seguir, iremos criar um novo schema com uma nova tabela e preencher esta tabela com dados do schema antigo:

CREATE TABLE new_schema.tabela AS SELECT * FROM old_schema.tabela

Na versão 7.4 é possível renomear um schema através do seguinte comando:

ALTER SCHEMA .. RENAME TO ...

Abraços e até logo.

Artigos relacionados