183
Laboratório de Banco de Dados Introdução ao PostgreSQL Profs. Valéria Gonçalves Soares Mateus Fernandes Cunha DIMAp/UFRN

Aula PostgreSQL

Embed Size (px)

Citation preview

Page 1: Aula PostgreSQL

Laboratório de Banco de Dados

Introdução ao PostgreSQL

Profs. Valéria Gonçalves SoaresMateus Fernandes Cunha

DIMAp/UFRN

Page 2: Aula PostgreSQL

Conteúdo

� Introdução ao PostgreSQL� Conceitos Básicos

� Acessando o Banco de Dados� Linguagem SQL

� Definição de Dados� Manipulação de Dados� Consulta aos Dados� Funções e Operadores

� Gatilhos e Procedimentos� Administração da Base de Dados

� Conceitos Básicos� Desenvolvimento de Aplicativos

Page 3: Aula PostgreSQL

Objetivo da Disciplina

� Oferecer aos alunos condições de trabalharcom um Banco de Dados Relacional, deixando-osaptos a:� Criar uma Base de Dados;� Manipular a Base de Dados;� Consultar a Base de Dados.

� Oferecer subsídios ao desenvolvimento de umaplicativo que acesse o banco de dados edisponibilize todas as funcionalidades básicasrequeridas de um sistema de BD.

Page 4: Aula PostgreSQL

Introdução ao PostgreSQL

� Embora o PostgreSQL seja um sistemagerenciador de banco de dados objeto-relacional, nesta disciplina iremos abordarapenas os aspectos relacionais do mesmo.� O PosgreSQL é baseado no POSGRES que foi

pioneiro em muitos conceitos que se tornaramdisponíveis posteriormente em muitossistemas de bancos de dados comerciais.

� O PosgreSQL é open-source e suporta tantoo SQL92 como o SQL99, além de oferecermuitas características modernas, tais como:

Page 5: Aula PostgreSQL

Introdução ao PostgreSQL

� Características PostgreSQL:� Consultas complexas� Chaves estrangeiras� Gatilhos� Visões� Integridade transacional� Controle de concorrência

multiversão

� PostgreSQL pode serestendido pelo usuário demuitas maneiras, porexemplo, pela adição denovos:� Tipos de dados� Funções� Operadores� Funções de Agregação� Métodos de Indexação� Linguagens Procedurais

Page 6: Aula PostgreSQL

Arquitetura PostgreSQL

� O PostgreSQL usa um modelo cliente/servidor. Umasessão PostgreSQL consiste dos seguintes programasque cooperam entre si:� Um processo servidor que gerencia os arquivos de bancos de

dados, aceita as conexões ao banco a partir das aplicaçõesclientes, e executa ações no banco de dados solicitadas pelosclientes. O programa servidor de banco de dados é chamadopostmaster.

� Cliente(front-end): São as aplicações que precisam acessar aobanco:

� Pode ser ferramentas gráficas, modo texto, via Web,ferramentas de manutenção e gerenciamento de banco,ferramentas de programação de triggers e procedures, ouqualquer outra ferramenta que precise interagir com oPostgreSQL.

� A comunicação entre Cliente e Servidor é feita via TCP/IP.

Page 7: Aula PostgreSQL

Arquitetura PostgreSQL

� O servidor PostgreSQL pode gerenciarmúltiplas conexões de clientes de modoconcorrente.

� Para cada conexão, o servidor inicia um novoprocesso servidor para a comunicação com ocliente (“fork”).

� A comunicação entre o servidor e o cliente éfeita sem intervenção do postmaster.

� Quando o postmaster recebe um novo pedido deconexão, ele cria um novo fork que fará acomunicação entre o cliente e o servidorPostgreSQL.

Page 8: Aula PostgreSQL

Primeiros Passos

� Para acessar o PostgreSQL dentro do DIMApinicialmente precisa-se acessar a máquina loucoservidora do bd:ssh nome_da_maquina ou ssh (ip da maquina)

� Ou direto, através do psql:psql meubanco –U nomeusuario –h

nome_da_maquina

Page 9: Aula PostgreSQL

Criando um Banco de Dados

� Um servidor PostgreSQL pode gerenciar vários bancos.Normalmente, um banco separado é usado para cada usuário ouprojeto. Para criar um novo banco de dados, que neste exemplo,estamos chamando de meubanco utiliza-se o seguinte comando:

$ createdbcreatedbcreatedbcreatedb meubancomeubancomeubancomeubancoQue irá produzir a seguinte resposta:

CREATE DATABASE� Um nome de banco de dados deverá ter um primeiro caractere

alfabético e são limitados a 63 caracteres no máximo. Pode-setambém criar um banco de dados com o mesmo nome no usuáriocorrente. Para isso basta digitar:

$ createdbcreatedbcreatedbcreatedb� Para remover um banco de dados, se você for o dono do mesmo,

basta digitar:$ dropdbdropdbdropdbdropdb meubancomeubancomeubancomeubanco

Page 10: Aula PostgreSQL

Acessando um Banco de Dados

� Um Banco de Dados pode ser acessado pordiversas maneiras:1. Terminal interativo do PostgreSQL (psql);2.Ferramentas gráficas para a criação,

administração e manipulação de bancos(pgAdmin);

3.Aplicações desenvolvidas pelos usuáriosutilizando conexões via ODBC ou JDBC.

Page 11: Aula PostgreSQL

Acessando o Banco de Dados

� O psql permite ao usuário entrar, editar e executarinterativamente comandos SQL

$ psql meubanco

Welcome to psql 7.4.1, 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

treino=#

Page 12: Aula PostgreSQL

Acessando o Banco de Dados

� Verificando a versão do PostgreSQL:$ SELECT version();

� Help:\h� Sair do psql:\q

Page 13: Aula PostgreSQL

Comandos Básicos psql

� Pode-se criar um arquivo texto com todas asoperações de criação de tabelas e de consultas,e posteriormente executa-lo da seguinte forma:\i caminho/nomedoarquivosql

� Para ver a descrição da Tabela\d nomedatabela

� Para ver todas as tabelas criadas\d

Page 14: Aula PostgreSQL

PostgreSQL

Linguagem SQL

Page 15: Aula PostgreSQL

Introdução

� A Structured Query Language (SQL) permite aosusuários acessar, definir e manipular dados em bancos dedados relacionais, como PostgreSQL, Oracle, Sybase,Informix, Microsoft SQL Server, MySql, entre outros.

� Nos RDBMS os dados são armazenados em tabelas.� Uma tabela pode ser definida como uma coleção de linhas,

onde cada linha em uma tabela é formada pelo mesmoconjunto de colunas.

� Cada coluna deve possuir um tipo de dado especifico.� Exemplos de tipos de dados no PostgreSQL:

� int, smallint, double precision, char(n), varchar(n), real,date, time, timestamp e interval.

Page 16: Aula PostgreSQL

Comandos Básicos

� DDL - Data Definition Language (Linguagem de Definição deDados):� CREATE TABLE� DROP TABLE

� DML – Data Manipulation Language (Linguagem de Manipulaçãode Dados):� SELECT� INSERT� UPDATE� DELETE

Page 17: Aula PostgreSQL

PostgreSQL

Definição de Dados

Page 18: Aula PostgreSQL

Linguagem de Definição de Dados

� Criação das estruturas do banco de dados.� Criação, modificação e eliminação de tabelas.� Controle dos dados armazenados nas tabelas.� Privilégios associados a tabelas.

Page 19: Aula PostgreSQL

Definição de Tabelas

� Para se criar uma nova tabela especifica-se o nome damesma e os nomes de seus atributos colunas com os seusrespectivos tipos.� O comando não acaba enquanto o psql não encontrar

o ponto-e-vírgula.� Espaços em branco podem ser usados livremente em

comandos SQL.� Como exemplos de tipos permitidos, o tipo varchar(80)

especifica um tipo que pode armazenar strings de até 80caracteres no seu tamanho, o tipo int é o tipo inteironormal, o tipo real armazena números de ponto flutuante desimples precisão, o tipo date armazena datas.

Page 20: Aula PostgreSQL

Criando uma Nova TabelaCREATE TABLE

CREATE TABLE pessoa (nome VARCHAR(20),cpf CHAR(11)endereco VARCHAR(30),cidade VARCHAR(15),estado CHAR(2),nascimento DATE

);

Use o comando \d pessoa para verificar a estrutura da tabela.

Table "public.pessoa"Column | Type | Modifiers

------------+-----------------------+-----------nome | character varying(20) |cpf | character(11) |endereco | character varying(30) |cidade | character varying(15) |estado | character(2) |nascimento | date |

Page 21: Aula PostgreSQL

Eliminando uma Tabela

� Se não se desejar mais a tabela, por fim, pode-seremovê-la com o seguinte comando:

DROP TABLE tablename;

� O comando DROP TABLE elimina permanentemente umatabela do banco de dados. Exemplo:

DROP TABLE pessoa;

Page 22: Aula PostgreSQL

Valores Default

� Um coluna pode receber um valor padrão toda vez queuma nova linha for inserida no sistema, e nenhum valorfor especificado para ela.

CREATE TABLE pessoa (idpessoa integer,nome text,...estado char(2) DEFAULT ‘NI’);

Page 23: Aula PostgreSQL

Restrições

� Check Constraints;� Not-Null Constraints;� Unique Constraints;� Primary Keys;� Foreign Keys.

Page 24: Aula PostgreSQL

Check Constraints

� Ao inserir ou atualizar uma coluna, o novovalor só será aceito se satisfazer aexpressão de restrição.

CREATE TABLE pessoa (idpessoa integer,nome text,...sexo char(1)CHECK sexo IN (‘M’, ‘F’)

);

Page 25: Aula PostgreSQL

Check Constraints

CREATE TABLE pessoa (idpessoa integer,nome text,...sexo char(1)CONSTRAINT ck_sexo CHECK (sexo IN (‘M’, ‘F’))

);

Page 26: Aula PostgreSQL

Check Constraints

� Outro Exemplo de Restrição.

CREATE TABLE produtos (idproduto integer,preco numeric,desconto numeric,CONSTRAINT ck_preco CHECK (preco > 0)CONSTRAINT ck_desconto CHECK (desconto > 0 AND

preco > desconto));

Page 27: Aula PostgreSQL

Restrição Not Null

� Não permite que a coluna assuma um valor nulo.

CREATE TABLE pessoa (idpessoa integer NOT NULL,nome text NOT NULL,...sexo char(1),CHECK sexo IN (‘M’, ‘F’)

);

Page 28: Aula PostgreSQL

Restrição UNIQUE

� Garante que o valor de uma coluna ou grupo de colunas éúnico para todas as linhas da tabela.

CREATE TABLE pessoa (idpessoa integer UNIQUE,nome text);

Page 29: Aula PostgreSQL

Restrição UNIQUE: Outros Formatos

CREATE TABLE pessoa (idpessoa integer,nome text,UNIQUE (idpessoa));

CREATE TABLE pessoa (a integer,b integer,c integerUNIQUE (a,b));

Page 30: Aula PostgreSQL

PRIMARY KEY (Chave Primária)

� Normalmente usada como identificador únicopara linhas na tabela.

� Tecnicamente pode ser considerada como umajunção das restrições Not-Null e Unique.

� Uma tabela só pode ter uma única chaveprimária, que pode ser simples ou composta.

CREATE TABLE pessoa (idpessoa integer PRIMARY KEY,nome text);

Page 31: Aula PostgreSQL

FOREIGN KEY (Chave Estrangeira)

� Especifica que os valores em uma coluna ou grupo decolunas dependem da existência em alguma linha deoutra tabela.

� Mantém a integridade referencial entre as duastabelas.

CREATE TABLE aluno (matricula numeric PRIMARY KEYidpessoa integer REFERENCES pessoa(idpessoa),ira real);

Page 32: Aula PostgreSQL

FOREIGN KEY (Chave Estrangeira)

� ON DELETE CASCADE� ON DELETE SET NULL� ON DELETE SET DEFAULT

CREATE TABLE aluno (matricula numeric PRIMARY KEY,idpessoa integerREFERENCES pessoa(idpessoa) ON DELETE CASCADE,

ira real);

Page 33: Aula PostgreSQL

Modificando Tabelas

� Adicionar Colunas.� Remover Colunas.� Adicionar Restrições.� Remover Restrições.� Mudar Valor Default.� Renomear Colunas.� Renomear Tabelas.

Page 34: Aula PostgreSQL

Adicionando Uma Nova Coluna

� Adiciona uma nova coluna em uma tabela existente.ALTER TABLE pessoa ADD COLUMN email text;

� A definição da restrição pode ser feita no momento dacriação da coluna, com exceção de Not-Null.

ALTER TABLE pessoaADD COLUMN sexo char(1)CHECK (sexo IN (‘M’,’F’));

Page 35: Aula PostgreSQL

Removendo Uma Coluna

� Também pode-se remover uma coluna de uma tabela. Ocomando abaixo especificado remove uma coluna databela pessoa.

ALTER TABLE pessoa DROP COLUMN email;

Page 36: Aula PostgreSQL

Adicionando Uma Nova Restrição

� Pode-se também adicionar uma nova restrição em umatabela existente. Os exemplos abaixo alteram asdefinições das tabelas pessoa e aluno.

ALTER TABLE pessoa ADD CHECK (nome <> ‘ ’);

ALTER TABLE aluno ADD FOREIGN KEY (idpessoa)REFERENCES pessoa;

Page 37: Aula PostgreSQL

Removendo Uma Restrição

� É possível remove uma restrição existente.� O nome da restrição deve ser informado no

comando. Se ela tiver sido gerada pelo sistemapode ser consultada através do comando \dnome_da_tabela.

ALTER TABLE pessoa DROP CONSTRAINT restricao;

ALTER TABLE pessoaALTER COLUMN pessoa DROP NOT NULL;

Page 38: Aula PostgreSQL

Alterando o Valor Default

� Alterando o valor Default de uma coluna:ALTER TABLE pessoa ALTER COLUMN estado SET

DEFAULT ‘RN’;

� Removendo o valor Default de uma coluna:ALTER TABLE pessoa ALTER COLUMN estado DROP

DEFAULT;

Page 39: Aula PostgreSQL

Renomeando Coluna e Tabela

� Para renomear uma coluna:

ALTER TABLE pessoaRENAME COLUMN telefne TO telefone;

� Para renomear uma tabela:

ALTER TABLE pssoa RENAME TO pessoa;

Page 40: Aula PostgreSQL

PostgreSQL

Manipulação de Dados

Page 41: Aula PostgreSQL

Inserindo Dados

INSERT INTO pessoaVALUES ('JOSE MARIA DA SILVA', '12345678901', 'RUA DAS FLORES 22','NATAL', 'RN', TO_DATE('23/03/1961','DD/MM/YYYY'));

� Para inserir dados em uma tabela, utiliza-se o comandoINSERT , da seguinte forma:

INSERT INTO pessoa (nome, cpf, endereco, cidade, estado, nascimento)VALUES ('MARIA DAS DORES', '01234567890', 'RUA SOL 85',

'NATAL', 'RN', TO_DATE('15/11/1956','DD/MM/YYYY'));

� Aconselha-se listar as colunas explicitamente para facilitara compreensão:

Page 42: Aula PostgreSQL

Inserindo Dados

INSERT INTO pessoa (cpf, nome, nascimento, endereco, cidade, estado)

VALUES ('9876543210', 'RITA DE CASSIA', TO_DATE('15/11/1956','DD/MM/YYYY'),'RUA SOL 85', 'NATAL', 'RN');

� Também pode-se listar as colunas em diferente ordem:

Page 43: Aula PostgreSQL

Inserindo Dados

INSERT INTO marias (nome,nascimento)SELECT nome, nascimentoFROM pessoaWHERE nome like ‘%MARIA%’;

� Pode-se copiar os dados de uma tabela diretamentepara outra:

Page 44: Aula PostgreSQL

Inserindo dados na Tabela

� Pode-se utilizar o comando COPY para copiaruma grande quantidade de dados em arquivostexto previamente formatados. Istonormalmente é bem rápido porque o comandoCOPY é bem mais otimizado do que o comandotradicional INSERT.

COPY pessoa FROM ´home/user/pessoa.txt´;

Page 45: Aula PostgreSQL

Removendo Linhas

� A instrução DELETE é utilizada para removeruma ou mais linhas de uma tabela.

� A forma básica da instrução é:DELETE FROM tabela;

� A instrução acima removerá todas as linhas databela, portanto, deve ser usado com muitacautela.

Page 46: Aula PostgreSQL

Removendo Linhas

� A clausula WHERE pode ser usada pararestringir as linhas que serão eliminadas. Comopor exemplo:

DELETE FROM automovel

WHERE modelo = 'GOL';

Page 47: Aula PostgreSQL

Modificando Dados

� O comando UPDATE permite a modificação dedados existentes no Banco de Dados.

� Pode-se atualizar uma ou mais colunas de umaou mais linhas de uma tabela.

� A estrutura do comando é composta de trêspartes:

1. O nome da tabela e coluna(s) a serematualizadas;

2. O novo valor da(s) coluna(s) atualizadas;3. Qual(is) linha(s) atualizar.

Page 48: Aula PostgreSQL

Modificando Dados

� Forma básica:UPDATE tabela SET coluna = novo_valor;

� A instrução acima atualizará o valor da coluna emtodas as linhas da tabela, portanto deve serusado com cautela.

Page 49: Aula PostgreSQL

Modificando Dados

� Também pode-se usar a clausula WHERE pararestringir as linhas que serão atualizadas.

UPDATE automovelSET valor = 10000WHERE modelo = 'UNO' AND ano = 2001;

Page 50: Aula PostgreSQL

Modificando Dados

� Pode-se atualizar mais de uma coluna ao mesmotempo:

UPDATE automovelSET valor = valor*1.1, proprietario = 'JORGE FILHO'

WHERE modelo = 'UNO' AND ano = 2001;

Page 51: Aula PostgreSQL

Modificando Dados

� Também pode-se usar subconsultas no comandoupdate.

UPDATE automovelSET valor = valor*1.1

WHERE proprietario IN (SELECT nomeFROM pessoa

WHERE cidade = ‘NATAL’);

Page 52: Aula PostgreSQL

PostgreSQL

SELECT

Page 53: Aula PostgreSQL

Consultando uma Tabela

� A instrução SELECT é utilizada para recuperarinformações de uma ou mais tabelas.

� A estrutura básica do SELECT é composta detrês partes principais:1. Lista das colunas a serem retornadas;2.Lista das tabelas a serem consultadas;3.Restrições (opcional).

Page 54: Aula PostgreSQL

O Comando SELECT

� A instrução SELECT tem a seguinte estruturabásica:SELECT select_list FROM tabela(s);

� Também pode compor a instrução as seguintescláusulas opcionais:� WHERE� GROUP BY� HAVING� ORDER BY

Page 55: Aula PostgreSQL

Consultando uma Tabela

SELECT * FROM pessoa;* indica que todas as colunas da tabela serão apresentadas.

nome | cpf | endereco | cidade | estado | nascimento

---------------------+-------------+-------------------+--------+--------+------------

JOSE MARIA DA SILVA | 12345678901 | RUA DAS FLORES 22 | NATAL | RN | 1961-03-23

MARIA DAS DORES | 01234567890 | RUA SOL 85 | NATAL | RN | 1956-11-15

RITA DE CASSIA | 9876543210 | RUA SOL 85 | NATAL | RN | 1956-11-15

(3 rows)

Page 56: Aula PostgreSQL

Consultando uma Tabela

� Pode-se especificar na consulta apenas os camposdesejados:

SELECT nome, cidade, estadoFROM pessoa;

nome | cidade | estado---------------------+--------+--------JOSE MARIA DA SILVA | NATAL | RNMARIA DAS DORES | NATAL | RNRITA DE CASSIA | NATAL | RN(3 rows)

Page 57: Aula PostgreSQL

A Cláusula SELECT

A cláusula SELECT pode:● Listar todas as colunas de uma tabela:

SELECT * FROM pessoa;● Listar colunas especificas de uma tabela:

SELECT nome, cidade FROM pessoa;● Executar operações entre colunas listadas:

SELECT matricula, (nota1 + nota2 + nota3)/3 FROM notas;

SELECT nome || sobrenome FROM pessoa;

Page 58: Aula PostgreSQL

A Cláusula SELECT

● Executar operações aritméticas como umacalculadora:

SELECT 3*4;● Chamar funções nativas ou programadas pelo

usuário:SELECT random();

● Aplicar funções sobre as colunas listadas:SELECT substr(nome,1,4) FROM pessoa;SELECT length(nome) FROM pessoa;

Page 59: Aula PostgreSQL

A Cláusula SELECT

● O nome, de uma coluna, mostrado na saídapode ser alterado através do uso de aliases:

SELECT matricula, (nota1 + nota2 + nota3)/3 AS mediaFROM notas;

matricula | media-----------+------------------200123810 | 6.79999987284342200278231 | 3.70000012715658200144609 | 6.73333358764648(3 rows)

Page 60: Aula PostgreSQL

A Cláusula SELECT

● Pode-se eliminar resultados duplicados, nasaída, usando a palavra reservada DISTINCT:

SELECT DISTINCT modelo FROM automovel;treino=# SELECT modelo

FROM automovel;

modelo

--------

UNO

PALIO

CORSA

GOL

UNO

PALIO

CORSA

GOL

(8 rows)

treino=# SELECT DISTINCT modelo

modelo

FROM automovel;

modelo

--------

CORSA

GOL

PALIO

UNO

(4 rows)

Page 61: Aula PostgreSQL

A Cláusula FROM

A cláusula FROM indica a origem dos dados de umaconsulta, que pode ser:

● Uma tabela:SELECT * FROM pessoa;

● Duas ou mais tabelas (Joined Tables);● Subqueries;● Views.

Page 62: Aula PostgreSQL

A Cláusula WHERE

● Após ser processada pela cláusula FROM, cadalinha da tabela virtual derivada é checada nacondição de busca.

● Se o resultado da condição for verdadeiro, alinha é colocada na tabela de saída.

● Caso contrário, a linha é descartada doresultado.

Page 63: Aula PostgreSQL

A Cláusula WHERE

� A clausula WHERE restringe as linhas retornadasem uma consulta.

SELECT modelo, ano, cor, valorFROM automovelWHERE modelo = 'GOL';

modelo | ano | cor | valor--------+------+--------+-------GOL | 1998 | AZUL | 9000GOL | 2002 | BRANCO | 14000(2 rows)

Page 64: Aula PostgreSQL

A Cláusula WHERE

� Os operadores AND, OR e NOT são permitidosna qualificação de uma Consulta.SELECT modelo, ano, cor, valor

FROM automovelWHERE modelo = 'GOL' AND ano < 2000;

modelo | ano | cor | valor--------+------+------+-------GOL | 1998 | AZUL | 9000(1 row)

Page 65: Aula PostgreSQL

A Cláusula WHERE

SELECT modelo, ano, cor, valorFROM automovel

WHERE modelo = 'GOL' OR modelo = 'PALIO';

modelo | ano | cor | valor--------+------+--------+--------GOL | 1998 | AZUL | 9000PALIO | 2000 | BRANCO | 10500GOL | 2002 | BRANCO | 14000(3 rows)

Page 66: Aula PostgreSQL

A Cláusula WHERE

� Outros Exemplos de WHERE:

Page 67: Aula PostgreSQL

A Cláusula GROUP BY

● A cláusula GROUP BY é usada para agrupar todasas linhas que compartilham os mesmos valores emtodas as colunas listadas.

● A ordem das colunas não é importante.● O objetivo é reduzir cada grupo de linhas que

compartilham os mesmos valores em comum numúnico grupo que representa todas as linha nogrupo.

Page 68: Aula PostgreSQL

A Cláusula GROUP BYEXEMPLO:

SELECT modeloFROM automovel;

SELECT modeloFROM automovel

GROUP BY modelo;modelo--------UNOPALIOCORSAGOLUNOPALIOCORSAGOL(8 rows)

modelo--------CORSAGOLPALIOUNO(4 rows)

Page 69: Aula PostgreSQL

A Cláusula GROUP BY

EXEMPLO 2:SELECT modelo, SUM(valor)FROM automovelGROUP BY modelo;

modelo | sum--------+-------UNO | 22000CORSA | 12000GOL | 23000PALIO | 21000(4 rows)

Page 70: Aula PostgreSQL

A Cláusula HAVING

● Em muitas consultas nem todos os gruposretornados pela cláusula GROUP BY sãoimportantes para o usuário.

● A cláusula HAVING serve para eliminar gruposde uma tabela agrupada.

● Expressões na cláusula HAVING podem sereferir a expressões agrupadas ou nãoagrupadas.

Page 71: Aula PostgreSQL

A Cláusula HAVINGEXEMPLOS:

Page 72: Aula PostgreSQL

Subqueries

● SUBQUERIES:● Uma consulta dentro de outra consulta.● O resultado da subquery retorna uma tabela

virtual derivada que será usada pela consultaprincipal.

● Deve ser escrita entre parênteses.● Deve ser identificada por um alias.

Page 73: Aula PostgreSQL

Subqueries

● SUBQUERIES - Exemplo:● Retorne todos os modelos de carro que

possuem mais de duas unidades no estoque.SELECT a.modelo, quantidadeFROM automovel a INNER JOIN (SELECT modelo AS m,

count(*) AS quantidadeFROM automovelGROUP BY modelo) AS contagem

ON a.modelo = mWHERE quantidade > 2;

Page 74: Aula PostgreSQL

Joined Tables

� Joined Tables● Uma joined table é uma tabela derivada de

duas outras (real ou derivada) tabelas.● Os tipos de junção disponíveis no PostgreSQL

são:� Inner Join;� Outer Join;� Cross Join.

Page 75: Aula PostgreSQL

Tabelas Exemplo

As tabelas abaixo serão usadas nos próximosexemplos.

T1 T2

Page 76: Aula PostgreSQL

Joined Tables : CROSS JOIN

� T1 CROSS JOIN T2;� Para cada combinação de linhas de T1 e T2, a

tabela derivada irá conter uma linha consistindode todas as colunas de T1 seguidas por todas ascolunas de T2.

� Se as tabelas possuem N e M linhasrespectivamente, a tabela resultante terá N*Mlinhas.

� O CROSS JOIN é normalmente executado pordefault, com a seguinte consulta simples:

SELECT * FROM T1,T2;

Page 77: Aula PostgreSQL

Joined Tables

Page 78: Aula PostgreSQL

Joined Tables● INNER JOIN:

● Para cada linha R1 de T1, a tabela resultantetem uma linha para cada linha em T2 quesatisfaz a condição de junção com R1.

Page 79: Aula PostgreSQL

Joined Tables● LEFT OUTER JOIN:

● Primeiro, um inner join é executado. Então,para cada linha em T1 que não satisfaz acondição com nenhuma linha em T2, a linha éadicionada com valores nulos nas colunas deT2.

Page 80: Aula PostgreSQL

Joined Tables● RIGHT OUTER JOIN:

● Primeiro, um inner join é executado. Então,para cada linha em T2 que não satisfaz acondição com nenhuma linha em T1, a linha éadicionada com valores nulos nas colunas deT1.

Page 81: Aula PostgreSQL

Joined Tables● FULL OUTER JOIN:

● É a união do RIGHT OUTER JOIN e LEFTOUTER JOIN.

Page 82: Aula PostgreSQL

Operações em Conjuntos

Page 83: Aula PostgreSQL

Tabelas Exemplo

� Tabela Clima � Tabela Cidades

cidade | data | precp--------+------------+--------Natal | 18-03-2004 | 3.2 Caico | 18-03-2004 | 4.7Touros | 19-03-2004 | 18.5Patu | 20-03-2004 | 7.9

cidade | ano | temp_media--------+------------+--------Natal | 2004 | 30 Macau | 2003 | 33 Caico | 2004 | 32Touros | 2004 | 29Apodi | 2003 | 31Buzios | 2004 | 25

Page 84: Aula PostgreSQL

Operações em Conjunto

� Assim como na álgebra relacional, as relaçõesparticipantes das operações de união, interseção ediferença em SQL precisam ser compatíveis entre si, ouseja, elas precisam ter o mesmo conjunto de atributos.� Vamos dar exemplos dessas operações, usando dois

conjuntos básicos: (a) o conjunto de todos os nomesdas cidades que possuem valores de precipitação, e(b) o conjunto de todos os nomes das cidades quepossuem valores de temperatura.

� Onde o conjunto (a) pode E o conjunto (b) podeser obtido com: ser obtido com:

SELECT cidade SELECT cidadeFROM clima; FROM cidades;

Page 85: Aula PostgreSQL

Operação União

� Para se obter todos os nomes de cidades quepossuem valores de precipitação ou detemperatura, ou ambos, escreve-se:

Resultado obtido:(SELECT cidadeFROM clima)UNION(SELECT cidadeFROM cidades);

cidade--------NatalCaicoTourosPatuMacauApodiBuzios

Page 86: Aula PostgreSQL

Combinando Consultas

� Consulta1 UNION Consulta2:� Retorna a união do resultado da consulta1 com

o resultado da consulta2, onde as linhaduplicadas são eliminadas.

� Consulta1 UNION ALL Consulta2:� Semelhante ao UNION sendo que as linhas

duplicadas não são eliminadas

Page 87: Aula PostgreSQL

Operação Interseção

� Para encontrar todas as cidades que contenhamtanto valores de precipitação como valores detemperatura, escreve-se:

(SELECT cidade

FROM clima)

INTERSECT

(SELECT cidade

FROM cidades);

Resultado obtido:cidade--------NatalCaicoTouros

Page 88: Aula PostgreSQL

Combinando Consultas

� Consulta1 INTERSECT Consulta2:� Retorna a interseção do resultado da

consulta1 com o resultado da consulta2, ondeas linha duplicadas são eliminadas.

� Consulta1 INTERSECT ALL Consulta2:� Semelhante ao INTERSECT sendo que as

linhas duplicadas não são eliminadas.

Page 89: Aula PostgreSQL

Operador EXCEPT

� Para encontrar todas as cidades que contenhamvalores de precipitação e não contenham valoresde temperatura, escreve-se:

(SELECT cidade

FROM clima)

EXCEPT

(SELECT cidade

FROM cidades);

cidade--------Patu

Resultado obtido:

Page 90: Aula PostgreSQL

Combinando Consultas

� Consulta1 EXCEPT Consulta2:� Retorna todas as linhas que estão no resultado

da consulta1 mas não estão no resultado daconsulta2, as linha duplicadas são eliminadas.

� Consulta1 EXCEPT ALL Consulta2:� Semelhante ao EXCEPT sendo que as linhas

duplicadas não são eliminadas.

Page 91: Aula PostgreSQL

PostgreSQL

Funções e Operadores

Page 92: Aula PostgreSQL

Operadores Lógicos

� Os operadores lógicos disponíveis no PostgreSQL são:AND, OR e NOT.

Page 93: Aula PostgreSQL

Operadores de Comparação

Page 94: Aula PostgreSQL

Operadores de Comparação

� BETWEEN:a BETWEEN x AND yEquivalente a: a >= x AND a <= y

� NOT BETWEENa NOT BETWEEN x AND yEquivalente a: a < x OR a > y

Page 95: Aula PostgreSQL

Operadores de Comparação

� IS NULL: Retorna TRUE se o valor da coluna fornulo.SELECT nome

FROM pessoaWHERE cpf IS NULL;

� IS NOT NULL: Retorna TRUE se o valor dacoluna não for nulo.SELECT COUNT(*)

FROM pessoaWHERE cpf IS NOT NULL;

Page 96: Aula PostgreSQL

Operadores e FunçõesMatemáticas

Page 97: Aula PostgreSQL

Operadores e FunçõesMatemáticas

Page 98: Aula PostgreSQL
Page 99: Aula PostgreSQL

Operadores e FunçõesMatemáticas

Page 100: Aula PostgreSQL

Operadores e Funções de String

Page 101: Aula PostgreSQL
Page 102: Aula PostgreSQL
Page 103: Aula PostgreSQL
Page 104: Aula PostgreSQL
Page 105: Aula PostgreSQL
Page 106: Aula PostgreSQL

Funções Para Formatação de Datas

Page 107: Aula PostgreSQL

Padrões de Template Para Formataçãode Data e Hora

Page 108: Aula PostgreSQL
Page 109: Aula PostgreSQL
Page 110: Aula PostgreSQL

Funções de Agregação

Page 111: Aula PostgreSQL
Page 112: Aula PostgreSQL

PostgreSQL

Índices

Page 113: Aula PostgreSQL

Índices

� Usados para melhorar a performance deconsultas ao banco de dados.

� Úteis em consultas que retornam linhasespecificas e utilizam na cláusula WHERE acoluna ou colunas nas quais o índice foiconstruído.

� Ao se criar um índice, o servidor constrói umaárvore de busca para a(s) coluna(s) indexada(s).

� Inúteis no caso de FULL TABLE SCANS oucolunas não indexadas na cláusula WHERE.

Page 114: Aula PostgreSQL

Índices

EXEMPLO:SELECT nome FROM pessoa WHERE idpessoa = 3145;

� Se a coluna idpessoa não for indexada o servidor faráuma busca em toda a tabela, linha por linha, retornandoa(s) linha(s) com idpessoa 3145.

� Caso a coluna seja indexada, o servidor fará a buscadiretamente na árvore, tornando a consulta bem maiseficiente.

Page 115: Aula PostgreSQL

Índices

� Exemplo de criação de índice:CREATE INDEX idpessoa_index ON pessoa(idpessoa);

� Para remover o índice:DROP INDEX idpessoa_index;

Page 116: Aula PostgreSQL

Índices

Por que não criar índices em todas as colunas?1. Espaço: A criação de um índice implica na

criação de uma árvore de busca, aumentandoassim a necessidade de espaço físico paraarmazenar o banco de dados;

2. Overhead nas operações de INSERT,UPDATE e DELETE: Toda vez que um valor nacoluna indexada é atualizada ou uma linha éinserida ou apagada a árvore precisa ser“rebalanceada”, causando um consideráveloverhead no desempenho do sistema.

Page 117: Aula PostgreSQL

Índices Multicoluna

� Até 32 colunas podem ser especificadas no índice.CREATE INDEX tb_xy_idx ON tb(x,y);

� Útil em consultas que utilizam o operador AND nascolunas do índice:SELECT x, y FROM tb WHERE x = 3 AND y = 8;

� Inútil no caso do operador OR:SELECT x, y FROM tb WHERE x = 3 OR y = 8;

� Também é usado no caso:SELECT x, y FROM tb WHERE x = 3;

� Inútil no caso:SELECT x, y FROM tb WHERE y = 8;

Page 118: Aula PostgreSQL

Unique Indexes

� Não permite valores duplicados em uma coluna:CREATE UNIQUE INDEX idpessoa_index ON pessoa(idpessoa);

� No caso de um índice multicoluna, serão rejeitados oscasos onde todas as colunas indexadas são iguais emmais de uma linha.

� Toda vez que uma “Unique Constraint” ou uma chaveprimária é criada, um unique index é criadoautomaticamente.

Page 119: Aula PostgreSQL

Índice de Expressão

� Um índice também pode ser baseado em uma função ouexpressão escalar.

CREATE INDEX test1_lower_col1_idx ON test1(lower(col1));

� Útil para consultas como:

SELECT * FROM test1 WHERE lower(col1) = 'value';

Page 120: Aula PostgreSQL

Seqüências

� Objeto usado para gerar números inteirosexclusivos.

� Pode ser compartilhada por vários usuários.� Normalmente usadas para gerar

automaticamente valores de chave primária.� Os números são gerados por uma rotina interna

do PostgreSQL.� Uma mesma seqüência pode ser usado por várias

tabelas.

Page 121: Aula PostgreSQL

Seqüências

� Uma seqüência é criada através do comandoCREATE SEQUENCE:

Page 122: Aula PostgreSQL

Seqüências

Parâmetros:1. Temporary: O objeto seqüência é criado

somente para a sessão e apagado no fim dasessão.

2. Increment by i: Especifica o intervalo entrenúmeros de seqüência onde i é um númerointeiro, o valor default é 1, caso o valor de iseja negativo a seqüência serádecrementada.

Page 123: Aula PostgreSQL

Seqüências

Parâmetros:3. Minvalue: Especifica o menor valor possível

na seqüência.4. No Minvalue: Usa o valor default (1 ou-2^63

-1).5. Maxvalue: Especifica o valor máximo que a

seqüência pode gerar.6. No Maxvalue: Usa o valor default (2^63 -1

ou -1).

Page 124: Aula PostgreSQL

Seqüências

Parâmetros:7. Start With: Especifica o primeiro número a

ser gerado pela seqüência.8. Cache: Especifica quantos valores serão

gerados previamente e alocados na memória.9. Cycle: Especifica o valor máximo que a

seqüência pode gerar (Default 1).

Page 125: Aula PostgreSQL

Seqüências

Parâmetros:10. Cycle: Permite a seqüência continuar a gerar

valores depois de atingir o maxvalue(ascendente) ou minvalue (descendente).Caso o limite seja atingido, o próximonúmero a ser gerado será o minvalue oumaxvalue. Deve ser usado com cuidado paragerar valores de chave primária. O valordefault é No Cycle.

Page 126: Aula PostgreSQL

Seqüências

Funções de Manipulação:� nextval: Avança e seqüência e retorna um novo

número.� nextval(‘sequencia’)

� currval: Retorna o valor atual da seqüência.� currval(‘sequencia’)

� setval: Seta um novo valor atual para aseqüência.� setval(‘sequencia’,valor)

Page 127: Aula PostgreSQL

Seqüências

� Para listar todas as seqüências do banco:� \ds

� Para ver todos os parâmetros da sequencia:� SELECT * FROM nome_da_sequencia;

Page 128: Aula PostgreSQL

Seqüências

Exemplo:

INSERT INTO pessoa (idpessoa, nome)VALUES (nextval(‘seq_pessoa’, ‘ROSANGELA MARIA’);

INSERT INTO aluno (idpessoa, matricula)VALUES (currval(‘seq_pessoa’, 200425484);

SELECT currval(‘seq_pessoa’);

Page 129: Aula PostgreSQL

Seqüência

� Para remover uma sequencia:DROP SEQUENCE sequence_name;

� Para alterar uma seqüência: ALTER SEQUENCE.

Page 130: Aula PostgreSQL

PostgreSQL

Funções e Triggers

Page 131: Aula PostgreSQL

Funções

� Também conhecidas como Stored Procedures, sãofunções escritas em linguagem procedural, que sãoarmazenadas e executadas dentro do próprio banco.

� Se muitas aplicações utilizam uma mesma função, évantagem armazená-la dentro do servidor de banco,evitando assim cópias redundantes da mesma nasdiferentes aplicações.

Page 132: Aula PostgreSQL

Funções

� A transferência dos códigos SQL, como SELECT, INSERT,UPDATE e DELETE, ou de funções que necessitam de acessosa banco, para dentro do servidor pode resultar em um grandeganho de performance. Dentre as vantagens podemos citar:1. Normalmente o DBA possui um melhor conhecimento

da linguagem SQL que os programadores.2.Evita redundância de código, evitando que a mesma

instrução seja escrita várias vezes em diferentespontos da aplicação.

Page 133: Aula PostgreSQL

Funções

3.Ao invés de se passar o código SQL inteiro para oservidor, a aplicação passa apenas os parâmetros daconsulta e espera o resultado, diminuído assim acomunicação interprocessos e o tráfego de rede.

Em um sistema com centenas ou milhares de usuáriospor exemplo, esse ganho pode ser imenso.

Page 134: Aula PostgreSQL

Funções

4. Toda vez que o servidor “recebe” uma instruçãoSQL diferente da aplicação, ele necessita fazer oparse e gerar o plano de execução antes deexecutar a busca na base, que são etapas queconsomem um tempo considerável.

No caso de uma stored procedure, o parser e o planode execução são gerados somente na primeira vezque a função é chamada, ficando armazenados paraas chamadas subseqüentes, gerando assim umaconsiderável economia de tempo.

Page 135: Aula PostgreSQL

Funções

� A atual versão do PostgreSQL (7.4.1) suporta as seguinteslinguagens procedurais: PL/pgSQL, PL/Tcl, PL/Perl, PL/TclU,PL/PerlU, e PL/PythonU.

� A linguagem PL/psSQL possui um padrão bastante semelhanteao PL/SQL do Oracle e do SQL/Server, sendo portanto a maiscomum de ser utilizada.

Page 136: Aula PostgreSQL

Funções PL/pgSQL

� As linguagens procedurais não vem instaladas por Default nosbancos de dados.

� Para instalar a linguagem PL/pgSQL o usuário deve executar oseguinte comando:$ createlang plpgsql meu_banco

� Caso se deseje que todas os bancos que venham a ser criadossuportem o PL/pgSQL:$ createlang plpgsql template1

Page 137: Aula PostgreSQL

Funções PL/pgSQL

Sintaxe básica para a criação de funções PL/pgSQL.

CREATE [OR REPLACE] FUNCTION nome(parametro1, parametro2…)RETURNS tipo_de_dado_retornado AS '

DECLAREvariaveis;

BEGINcorpo da procedure

END;' LANGUAGE plpgsql;

Page 138: Aula PostgreSQL

Funções PL/pgSQL

� Para se escrever uma função o usuário pode usar qualquereditor de texto, ou alguma ferramenta gráfica como o PgAdminou o PgAccess.

CREATE OR REPLACE FUNCTION helloworld()RETURNS text AS '

DECLAREa text;

BEGINa := ''Hello World'';RETURN a;

END;' LANGUAGE plpgsql;

Page 139: Aula PostgreSQL

Funções PL/pgSQL

treino=# \i ‘helloworld.sql’CREATE FUNCTIONtreino=# select helloworld();helloworld

-------------Hello World

(1 row)

Page 140: Aula PostgreSQL

Funções PL/pgSQL - Estrutura

Declaração:� Todas as variáveis que serão usadas no bloco, devem ser

declaradas no sessão DECLARATION.� A única exceção são as variáveis definidas implicitamente num

loop FOR.� As variáveis podem assumir os mesmos tipos suportados na

linguagem SQL do PostgreSQL, como; integer, varchar,numeric, float e date.

Page 141: Aula PostgreSQL

Funções PL/pgSQL - Estrutura

Declaração:� Quando se quer utilizar o mesmo tipo de uma coluna de uma

tabela, aconselha-se usar o %TYPE. Pois garante a consistênciacaso o tipo da coluna venha a mudar.

� Ao invés dev_nome varchar(30);

� É melhor usarv_nome pessoa.nome%TYPE;

Page 142: Aula PostgreSQL

Funções PL/pgSQL - Estrutura

Declaração:� Uma variável composta pode ser declarada com todas as

colunas de uma tabela %ROWTYPE (semelhante a um registro).

DECLARE

r_pessoa pessoa%ROWTYPE;BEGIN

r_pessoa.nome := 'CREMILDA';

Page 143: Aula PostgreSQL

Funções PL/pgSQL - Estrutura

Declaração:� Os parâmetros de entrada da função são acessados no corpo

como $1 (parametro1), $2 (parametro2). Pode-se usarALIASES para se tornar mais amigável.

CREATE FUNCTION teste(real) RETURNS real AS ‘DECLARE

v_valor ALIAS FOR $1;BEGIN

RETURN 10*v_valor;END; ‘ LANGUAGE plpgsql;

Page 144: Aula PostgreSQL

Funções PL/pgSQL - Estrutura

Instruções básicas: Atribuição� Um valor pode ser atribuído a uma variável ou um registro.� Exemplos:

v_idade := 23;v_taxa := v_preco * 0.1

Page 145: Aula PostgreSQL

Funções PL/pgSQL - Estrutura

Instruções básicas: SELECT INTO� Permite que o resultado de uma consulta select seja atribuído

a uma variável, lista de variáveis ou um registro.� A consulta só pode retornar uma única linha.

SELECT nome, cpfINTO v_nome, v_cpf

FROM pessoaWHERE idpessoa = 12548;

Page 146: Aula PostgreSQL

Funções PL/pgSQL - Estrutura

� Estruturas de Controle: RETURN� Sintaxe:

RETURN <expressao>;� Retorna o valor da expressão e finaliza a função.

Page 147: Aula PostgreSQL

Funções PL/pgSQL - Estrutura

Estruturas de Controle: Condicionais IF� A instrução IF permite a execução de comandos se uma

determinada condição for satisfeita.� O postgreSQL tem 4 formas de IF.

� IF ... THEN� IF ... THEN ... ELSE� IF ... THEN ... ELSE IF� IF ... THEN ... ELSIF ... THEN ... ELSE

Page 148: Aula PostgreSQL

Funções PL/pgSQL - Estrutura

Loops Simples: LOOP

Page 149: Aula PostgreSQL

Funções PL/pgSQL - Estrutura

Loops Simples: WHILE� Repete a execução das instruções enquanto a condição

for verdadeira.

Page 150: Aula PostgreSQL

Funções PL/pgSQL - Estrutura

Loops Simples: FOR� Repete a execução sobre uma escala de valores

inteiros.

Page 151: Aula PostgreSQL

Funções PL/pgSQL - Estrutura

Loop sobre o resultado de uma consulta: FOR� Existe uma variação do FOR que permite interagir com os

resultados retornados de uma consulta.

FOR c_aluno IN SELECT matricula, iraFROM aluno WHERE status = 'A' LOOP

IF c_aluno.ira >= 9 THENINSERT INTO elite (matricula, ira)

VALUES (c_aluno.matricula, c_aluno.ira);END IF;END LOOP;

Page 152: Aula PostgreSQL

Funções PL/pgSQL - Estrutura

Erros e Mensagens� A instrução RAISE reporta mensagens e erros.� Exemplos:

RAISE EXCEPTION '‘Preço nao pode ser negativo %'', NEW.minprice;�

RAISE NOTICE ''O valor de i neste ponto é %'', i;

Page 153: Aula PostgreSQL

Triggers

� Triggers são utilizadas para definir regras de restriçõescomplexas, ou executar alguma ação ou função depois de algumevento especifico.

� Uma trigger pode ser disparada antes ou depois de umaoperação INSERT, UPDATE ou DELETE, tanto a cada linhamodificada como após uma instrução SQL inteira.

Page 154: Aula PostgreSQL

Triggers

� Uma trigger é criada através do comando CREATE FUNCTIONcomo uma função sem argumentos e retorno do tipo trigger.

� Exemplo de utilização de Triggers:� Chamar uma função que verifique se o número do

CPF é válido toda vez que uma linha é inserida natabela pessoa ou o valor do campo CPF é atualizado.

Page 155: Aula PostgreSQL

Triggers

� O exemplo a seguir ilustra a criação de uma triggerprocedure que não permite preços negativos natabela de preços.

CREATE OR REPLACE FUNCTION public.preco_min()RETURNS trigger AS

'BEGINIF NEW.minprice < 0 THEN

RAISE EXCEPTION ‘‘Preço nao pode ser negativo %’', NEW.minprice;END IF;RETURN NEW;

END;'LANGUAGE 'plpgsql' VOLATILE;

Page 156: Aula PostgreSQL

Triggers

� Na tabela, deve-se criar o gatilho que disparará a triggerprocedure nas atualizações e inserções na tabela price.

CREATE TRIGGER price_minimoBEFORE INSERT OR UPDATEON public.priceFOR EACH ROWEXECUTE PROCEDURE public.preco_min();�

Page 157: Aula PostgreSQL
Page 158: Aula PostgreSQL

Usuários e Privilégios de Banco deDados

Page 159: Aula PostgreSQL

Usuários

� Todo cluster de banco de dados possui um conjunto deum ou mais usuários.

� Os usuários do banco não possuem nenhumacorrespondência com os do S.O. onde o servidor roda.

� Um nome de usuário é global para todo o cluster debanco, e não para cada banco individual.

Page 160: Aula PostgreSQL

Usuários

� Usuários podem possuir objetos (como tabelas),conceder acesso a seus objetos à outros usuários, ereceber autorização de acesso a objetos de outrosusuários.

� Durante a instalação, um usuário predefinido é criado eatravés dele pode-se conectar ao cluster para criarnovos usuários ou executar tarefas administrativas.Normalmente o nome do usuário inicial é postgres.

Page 161: Aula PostgreSQL

Usuários

� Para criar um novo usuário:CREATE USER nome;

� Para remover um usuário:DROP USER nome;

� Também podem ser usados, a partir do S.O. osprogramas createuser e dropuser:createuser nomedropuser nome

Page 162: Aula PostgreSQL

Atributos de Usuários

� Um usuário pode possuir determinados atributos quedeterminam seus privilégios e interage com o sistema deautenticação com o cliente.

� Superuser: Possui todos os privilégios do banco,somente um superusuário pode criar novos usuários.CREATE USER valeria CREATEUSER

Page 163: Aula PostgreSQL

Atributos de Usuários

� Database creation: Concede ao usuário a permissão decriar novos bancos de dados.CREATE USER valeria CREATEDB

� Password: Significativo somente se o método deautenticação requerer um password. Os métodospassword, md5 e crypt requerem passwords.CREATE USER valeria PASSWORD ‘senha’

� Todos os atributos podem ser modificados através doALTER USER.

Page 164: Aula PostgreSQL

Grupos

� Para simplificar a administração, pode-se criar gruposde usuário.

� Cada privilégio concedido ou revogado a um grupo, éconcedido ou revogado a todos os usuários do grupo.CREATE GROUP turma01;ALTER GROUP turma01 ADD USER xico, maria;ALTER GROUP turma01 DROP USER xico, maria;

Page 165: Aula PostgreSQL

Privilégios

� Quando um usuário cria um novo objeto no banco, ele setorna o proprietário do objeto.

� Por default, somente o proprietário e o superusuáriotem acesso total ao objeto.

� O proprietário pode conceder privilégios sobre seusobjetos a outros usuários.

� Exemplos de privilégios: SELECT, INSERT, UPDATE, DELETE,RULE, REFERENCES, TRIGGER, CREATE, TEMPORARY, EXECUTE,USAGE e ALL PRIVILEGES.

Page 166: Aula PostgreSQL

Privilégios

� Concedendo um privilégio:GRANT UPDATE ON pessoa TO maria;GRANT SELECT ON pessoa TO GROUP turma01;GRANT EXECUTE ON function_name TO PUBLIC;GRANT ALL PRIVILEGES ON pessoa TO coordenador;

� Revogando um privilégio:REVOKE ALL ON pessoa FROM maria;REVOKE INSERT ON pessoa FROM GROUP turma01;

Page 167: Aula PostgreSQL

Privilégios� WITH GRANT OPTION permite ao usuário que recebe o privilégio

concedê-lo a outros usuários:

GRANT REFERENCES

ON pessoa TO benedita WITH GRANT OPTION;

Page 168: Aula PostgreSQL

Transações em PostgreSQL

Page 169: Aula PostgreSQL

Transações

� As transações são um conceito fundamental em sistemasde bancos de dados. Uma transação envolve váriospassos em uma operação de tudo-ou-nada.

� Os passos intermediários de uma transação não sãovisíveis por outras transações, porque se alguma coisafalhar, nenhum passo será efetivamente executado.

Page 170: Aula PostgreSQL

Transações

� Considere um exemplo que se deseja fazer umatransferência bancária da conta de Alice para a contade Bob.UPDATE conta SET saldo=saldo-100.00

WHERE nome=´Alice´;UPDATE conta SET saldo=saldo+100.00WHERE nome=´Bob´;

� Desta maneira, não existe nenhuma garantia que as duas operaçõesserão efetivamente executadas ou nenhuma delas. Isto só é obtidoagrupando-se as duas operações em uma transação.

Page 171: Aula PostgreSQL

Transações

� Uma transação é atômica: ou ela acontececompletamente ou não acontece.

� Quando uma operação é completada ela é efetivamentepercebida pelo sistema de bd e suas alterações sãoverdadeiramente armazenadas no bd.

� Transações concorrentes não enxergam resultadosparciais umas das outras.� Uma transação em PostgreSQL é denotada pelos

comandos BEGIN e COMMIT.

Page 172: Aula PostgreSQL

Transações

� A transação bancária pode ser escrita como:BEGIN;

UPDATE conta SET saldo=saldo-100.00WHERE nome=´Alice´;UPDATE conta SET saldo=saldo+100.00WHERE nome=´Bob´;

COMMIT;� Pode-se desejar que a transação não seja efetivamente executada.

Neste caso, utiliza-se o comando ROLLBACK no lugar do COMMIT.

Page 173: Aula PostgreSQL

Manutenção de Rotina

Page 174: Aula PostgreSQL

Manutenção de Rotina

� No PostgreSQL existem algumas rotinas de manutençãoque devem ser executadas rotineiramente, afim demanter o servidor rodando eficientemente.

� Essas tarefas podem ser agendadas para seremexecutadas regulamente através de ferramentas do SO,como o CRON, por exemplo.

Page 175: Aula PostgreSQL

Vacuuming

� O comando VACUUM tem a função de:1. Recuperar espaço em disco ocupador por linhas

apagadas ou atualizadas;2. Atualizar as estatísticas usadas pelo PostgreSQL

query planner (gerador de planos de consultas doPostgreSQL).

� A freqüência do uso do comando VACUUM vaidepender da necessidade de cada base.

Page 176: Aula PostgreSQL

Reindexação

� Em algumas situações é necessário reconstruir osíndices periodicamente através do comando REINDEX.

� No PostgreSQL 7.4, a necessidade de reconstrução deíndices foi reduzida drasticamente em relação a versõesanteriores do PostgreSQL.

Page 177: Aula PostgreSQL

Backup e Restore

Page 178: Aula PostgreSQL

SQL Dump

� Gera um arquivo texto com comandos SQL, que quandoexecutado no servidor de bancos, recria a base dedados no mesmo estado do momento que o arquivo foigerado.pg_dump nome_do_banco > arquivo

� Por default o pg_dump se conecta ao banco usando omesmo nome de usuário do SO. Para usar outro usuáriouse o parâmetro –U.

� O usuário precisa ter acesso a todos os objetos, porisso aconselha-se usar um superusuário para executar obackup.

Page 179: Aula PostgreSQL

Recuperando

� O arquivo texto gerado pelo pg_dump pode ser lido pelopsql.

� Recuperando o backup.psql nome_do_banco < arquivo

� O banco terá que ter sido previamente criado atravésdo comando createdb (createdb –T template0nome_do_banco).

Page 180: Aula PostgreSQL

Pg_dumpall

� O comando pg_dump faz o backup de um banco por vez,para se fazer o backup de todo o database cluster,deve-se usar o programa pg_dumpall.pg_dumpall > arquivo

� Recuperando o Backup:psql template1 < arquivo

Page 181: Aula PostgreSQL

Compressão de Backups

� Para poupar espaço em disco, pode-se gerar os backupscomprimidos.pg_dump dbname | gzip > filename.gz

pg_dump dbname | split -b 1m - filename� Recuperando:

createdb dbname gunzip -c filename.gz | psql dbnamecreatedb dbname cat filename* | psql dbname

Page 182: Aula PostgreSQL

File System Level Backup

� Outra forma de backup é simplesmente copiar todos osarquivos de dados que o PostgreSQL usa paraarmazenar os dados.

� Só pode ser executado com o banco off-line, ou seja,deve-se dar um shutdown no banco antes de começar obackup.tar -cf backup.tar /usr/local/pgsql/data

Page 183: Aula PostgreSQL

Bibliografia

� PostgreSQL 7.4.1 Documentation by The PostgreSQLGlobal Development Group Copyright © 1996-2003 ThePostgreSQL Global Development Group