Postgresql

Tomando as Rdeas do Grande Elefante dos Dados
PostgreSQL
Rev. 2
Juliano Atanazio
Tomando as Rdeas do Grande Elefante dos Dados - PostgreSQL
ndice 0 - Sobre a Apostila 0.1 - Padres Usados 1 - O que o PostgreSQL? 1.1 - Como se Fala e como se Escreve 1.2 - Limites do PostgreSQL 1.3 - SGBD Sistemas Gerenciadores de Bancos de Dados 1.4 - Cluster de Banco de Dados 1.5 - Instalao do PostgreSQL 1.6 - Primeiro contato 1.7 - O psql 1.7.1 - Comandos internos do psql 1.7.2 - Variveis do psql 1.7.2.1 - Criando uma nova varivel 1.7.2.2 - Destruindo uma varivel 2 - SQL (Structured Query Language Linguagem Estruturada de Consulta) 2.1 - DDL (Data Definition Language Linguagem de Definio de Dados) 2.2 - DML (Data Manipulation Language Linguagem de Manipulao de Dados) 2.3 - DCL (Data Control Language Linguagem de Controle de Dados) 2.4 - Criao de Usurio 3 - Autenticao de cliente e configurao geral do PostgreSQL (pg_hba.conf e postgresql.conf) 4 - Importando um banco de dados j pronto de um arquivo 5 - Identificadores 6 - Modelos de Banco de Dados Templates 7 - Tipos de Dados 7.1 - Numricos 7.1.1 - Mscaras para formatos numricos 7.2 - De Caracteres 7.3 - Valor nulo (null) 7.4 - Lgico ou Booleano 7.5 - Data e Hora 7.5.1 - Mscaras de Data e Hora 8 - Obtendo ajuda de comandos SQL dentro do psql 9 - Comentrios do PostgreSQL 10 - Conectando-se a outro banco de dados dentro do psql 11 - Criando, Modificando e Apagando Objetos 12 - Tabelas 12.1 - Criao de Tabelas 12.2 - Tabelas temporrias 12.3 - Exibindo tabelas criadas 12.4 - Exibindo a estrutura de uma tabela 12.5 - Alterando Tabelas 12.6 - Apagando uma tabela
13 - Tablespaces 14 - Restries (Constraints) 14.1 - Tipos de Restries 14.1.1 - CHECK 14.1.2 - NOT NULL 14.1.3 UNIQUE 14.1.4 - PRIMARY KEY 14.1.5 - FOREIGN KEY 14.1.6 - O Modificador DEFAULT 15 - Descrio de Objetos 16 - Operadores 17 - SELECT - Consultas (Queries) 17.1 - O Comando TABLE 17.2 - SELECT sem FROM 17.3 - Type Casts 17.4 - Case - Um IF disfarado 17.5 - COALESCE 17.6 - NULLIF 17.7 - GREATEST e LEAST 17.8 - Conjuntos 17.8.1 - Fazendo operaes de conjunto 17.8.1.1 - UNION 17.8.1.2 - INTERSECT 17.8.1.3 - EXCEPT 17.9 - Expresses Regulares 17.10 - WHERE 17.10.1 - OR, AND, IN, agrupamento de comparaes e BETWEEN 17.10.2 - O Operador LIKE ou ~~ 17.10.3 - O Operador ILIKE ou ~~* 17.10.4 - SIMILAR TO 17.10.5 - O operador IS NULL 17.10.6 - O operador NOT 17.11 - A Clusula ORDER BY 17.12 - SELECT INTO 17.13 - CREATE TABLE AS 17.14 - Selecionando dados de diferentes tabelas 17.14.1 - Aliases de Tabelas (apelidos de tabelas) 17.14.2 - Joins 17.14.2.1 - Tipos de Joins 17.14.2.1.1 - CROSS JOIN (Juno cruzada) 17.14.2.1.2 - NATURAL JOIN (Juno natural) 17.14.2.1.3 - INNER JOIN (Juno interna) 17.14.2.1.4 - OUTER JOIN (Juno externa) 17.14.2.1.5 - SELF JOIN 17.14.3 - Sub-consultas 17.14.3.1 - Sub-consulta no WHERE 17.14.3.2 - Sub-consulta no SELECT 17.14.3.3 - Sub-consulta no FROM 17.14.3.4 - EXISTS 17.14.3.5 - IN e NOT IN 17.14.3.6 - ANY ou SOME 17.14.3.7 - ALL 18 - VIEW
3
19 - Esquemas (Schemas) 20 - Funes 20.1 - Funes Matemticas 20.2 - Funes de Data e Hora 20.2.1 - extract(...) 20.3 - Funes de Strings 20.3.1 - to_char() 20.4 - Funes de Sistema 20.5 - Funes de Agregao (ou de Grupos de Dados) 20.6 - A Clusula GROUP BY 20.7 - A Clusula HAVING 21 - INSERT insero de Dados 21.1 - Inserindo Registros com SELECT 21.2 - INSERT Agrupado 22 - Dollar Quoting 23 - UPDATE Alterando Registros 23.1 - Atualizando Mais de Um Campo 23.2 - Atualizando com Sub-consulta 24 - DELETE Removendo Registros 25 - TRUNCATE Redefinindo uma Tabela Como Vazia 26 - COPY 26.1 - Formato CSV 26.2 - Inserindo Dados com o COPY pelo Teclado 27 - SEQUENCE Sequncia 27.1 - Funes de Manipulao de Sequncia 27.2 - Usando Sequncias em Tabelas 27.3 - Alterando uma Sequncia 27.4 - Deletando uma Sequncia 28 - INDEX ndice 28.1 - Mtodos de Indexao 28.1.1 - BTREE 28.1.2 - GIST 28.1.3 - GIN 28.1.4 - HASH 28.2 - ndices Compostos 28.3 - ndices Parciais 28.4 - Antes de Excluir um ndice 28.5 - Excluindo um ndice 28.6 - Reconstruo de ndices REINDEX 28.7 - CLUSTER 29 - DOMAIN (Domno) 30 - Transaes / Controle de Simultaneidade 30.1 - MVCC Multi Version Concurrency Control (Controle de Concorrncia Multi Verso) 30.2 - Utilidade de uma transao 30.3 - ACID 30.4 - Nveis de Isolamento
4
30.4.1 - Nvel de Isolamento Read Commited (L Efetivado) 30.4.2 - Nvel de Isolamento Serializable (Serializvel) 30.5 - Fazendo uma transao 30.6 - SAVEPOINT - Ponto de Salvamento 30.6.1 - ROLLBACK TO SAVEPOINT 30.6.2 - RELEASE SAVEPOINT 30.7 - SELECT FOR UPDATE 31 - Cursores 31.1 - FETCH - Recuperando linhas de um cursor 31.2 - MOVE Movendo Cursores 31.3 - CLOSE Fechando Cursores 32 - BLOB Binary Large OBject (Objeto Grande Binrio) 32.1 - Removendo BLOBs 33 - Funes Criadas Pelo Usurio 33.1 - Funes SQL 33.1.1 - Funes com Retorno Nulo 33.1.2 - Funes com Retorno No Nulo 33.1.3 - Apagando uma Funo 33.1.4 - Funes que Retornam Conjuntos 33.1.5 - Funes com Parmetros 33.1.6 - Sobrecarga de Funo 33.2 - Funes internas 33.3 - Funes na linguagem C 33.4 - Linguagens procedurais 33.4.1 - Instalao de linguagem procedural 33.4.2 - PL/pgSQL 33.4.2.1 - Estrutura PL/pgSQL 33.4.2.2 - Declaraes 33.4.2.3 - Aliases de Parmetros de Funo 33.4.2.4 - SELECT INTO 33.4.2.5 - Atributos 33.4.2.5.1 - Tipo de Varivel Copiado varivel %TYPE 33.4.2.5.2 - Varivel Tipo Linha - %ROWTYPE 33.4.2.6 - Tipo Registro RECORD 33.4.2.7 - RENAME 33.4.2.8 - Atribuies 33.4.2.9 - PERFORM - Execuo sem Resultado 33.4.2.10 - NULL - No Fazer Nada 33.4.2.11 - EXECUTE - Execuo Dinmica de Comandos 33.4.2.12 - Status de um Resultado 33.4.2.12.1 - 1 Mtodo - GET DIAGNOSTICS 33.4.2.12.2 - 2 Mtodo - FOUND 33.4.2.13 - Retorno de Uma Funo 33.4.2.13.1 - RETURN 33.4.2.13.2 - RETURN NEXT 33.4.2.14 - Condicionais 33.4.2.14.1 - IF-THEN 33.4.2.14.2 - IF-THEN-ELSE 33.4.2.14.3 - IF-THEN-ELSE IF 33.4.2.14.4 - IF-THEN-ELSIF-ELSE 33.4.2.14.5 - IF-THEN-ELSEIF-ELSE 33.4.2.15 - Laos 33.4.2.15.1 - LOOP 33.4.2.15.2 - EXIT 33.4.2.15.3 - WHILE 33.4.2.15.4 - FOR (variao inteira)
5
33.4.2.15.5 - Loop por Queries 33.4.2.16 - FOR-IN-EXECUTE 33.4.2.17 - Tratamento de Erros 33.4.2.18 - Mensagens e Erros 33.4.2.19 - Cursores em Funes PL/pgSQL 33.4.2.19.1 - Abertura de Cursores 33.4.2.19.2 - Utilizao e Fechamento de Cursores 33.4.2.19.3 - Funes PL/pgSQL que Retornam Cursores 34 - Gatilhos Triggers 35 - Personalizando Operadores 36 - Regras Rules 36.1 - Atualizando Views com Rules 36.2 - Removendo Rules 36.3 - Rules X Trigggers 37 - Herana de Tabelas 37.1 - Inserindo Dados em uma Tabela-Filha 38 - Array de Colunas 38.1 - Inserindo Dados em Arrays 38.2 - Selecionando Dados de Arrays 39 - Particionamento de Tabelas 39.1 - Formas de Particionamento do PostgreSQL 39.2 - Implementao do Particionamento 40 - Administrao de Usurios 40.1 - Roles 40.2 - Apagando Roles 40.3 - Grupos 40.4 - Concedendo ou Revogando Acesso a Objetos 40.5 - Verificando os Privilgios de Objetos 40.6 - Concedendo ou Revogando Privilgios em Colunas 41 - Dblink Acessando um Outro Banco de Dados 42 - VACUUM Alocando Espaos sem Desperdcio 43 - ANALYZE - Coleta de Estatsticas Sobre uma Base de Dados 44 - EXPLAIN 45 - Backup & Restore 45.1 - Backup SQL 45.1.1 - pg_dump 45.1.1.1 - Restaurando do pg_dump e o Utilitrio pg_restore 45.1.2 - pg_dumpall 45.2 - Backups para Bancos de Dados Grandes 45.3 - Backup em Nvel de Sistema de Arquivos 45.4 - Arquivamento Contnuo 45.4.1 - WAL (Write-Ahead-Log: Registro Prvio de Escrita) 45.4.1.1 - Benefcios do WAL 45.4.1.2 - PITR - Point In Time Recovery 45.4.1.2.1 - Configurao do Arquivamento 45.4.1.2.2 - Fazendo um Backup da Base 45.4.1.2.3 - Recuperao PITR
6
45.4.1.2.4 - Configurao de Restaurao (recovery.conf) 45.4.2 - Cronologias Timelines 45.4.2.1 - Recuperao PITR (continuao) 46 - Configuraes de Memria e Ajustes de Desempenho 46.1 - Shared Memory 46.1.1 - Visualizando os Valores de Memria Compartilhada 46.1.2 - Modificando os Valores de Memria Compartilhada 46.2 - Configuraes de Consumo de Recursos no postgresql.conf 46.2.1 - Gerenciamento de Trava Lock Management 46.2.1.1 - deadlock_timeout 46.2.1.2 - max_locks_per_transaction 46.2.2 - Memria 46.2.2.1 - shared_buffers 46.2.2.2 - temp_buffers 46.2.2.3 - max_prepared_transactions 46.2.2.4 work_mem 46.2.2.5 - maintenance_work_mem 46.2.2.6 - max_stack_depth 46.2.3 - Uso de Recursos do Kernel 46.2.3.1 - max_files_per_process 46.2.3.2 - shared_preload_libraries 46.2.4 - Custo Baseado no Tempo de VACUUM 46.2.4.1 - vacuum_cost_delay 46.2.4.2 - vacuum_cost_page_hit 46.2.4.3 - vacuum_cost_page_miss 46.2.4.4 - vacuum_cost_page_dirty 46.2.4.5 - vacuum_cost_limit 46.2.5 - Background Writer 46.2.5.1 - bgwriter_delay 46.2.5.2 - bgwriter_lru_maxpages 46.2.5.3 - bgwriter_lru_multiplier 46.2.6 - Comportamento Assncrono 46.2.6.1 - effective_io_concurrency 46.2.7 - Constantes de Custo do Planejador 46.2.7.1 - seq_page_cost 46.2.7.2 - random_page_cost 46.2.7.3 - cpu_tuple_cost 46.2.7.4 - cpu_index_tuple_cost 46.2.7.5 - cpu_operator_cost 46.2.7.6 - effective_cache_size 47 - nstalao a Partir do Cdigo Fonte 47.1 - Instalando 48 - Instalao do PostgreSQL no Windows Extras Cdigo do Banco de Dados Usado como Exemplo (Copie, cole e salve) Dicas Bibliografia
0 - Sobre a Apostila
Esta apostila tem como objetivo explicar de forma simples e objetiva conceitos do banco de dados open source PosrgreSQL. Pode ser baixada pelo site http://excelsis.com.br/downloads/postgresql.pdf e redistribuda livremente. 0.1 - Padres Usados Para facilitar a leitura e fazer uma melhor organizao, de acordo com o tipo de texto so adotados os seguintes padres:
CdigosSQL MensagensgeradasporcomandosSQL Comandosdosistemaoperacional(shell) Mensagensgeradasporcomandosdosistemaoperacional Nomesdeobjetos Palavras em destaque e novos termos
1 - O que o PostgreSQL?
PostgreSQL um sistema gerenciador de banco de dados objeto-relacional baseado no POSTGRES, Verso 4.2, desenvolvido na Universidade da California no Departamento de Cincias da Computao em Berkeley, o qual foi pioneiro em muitos conceitos que vieram a estar disponveis em alguns bancos de dados comerciais mais tarde. O PostgreSQL um descendente open-source do cdigo original de Berkeley code. Suporta uma grande parte do padro SQL standard e oferece muitas caractersticas modernas: Consultas complexas; Chaves estrangeiras (foreign keys); Gatilhos (triggers); Vises (views ); Integridade transacional
O PostgreSQL pode tambm ser estendido pelo usurio para muitos propsitos, por exemplo adicionando novos: tipos de dados; funes; operadores; funes agregadas; mtodos de indexao; linguagens procedurais
Devido sua licena liberal, o PostgreSQL pode ser usado, modificado, e distribudo por qualquer um gratuitamente para qualquer propsito, seja privado, comercial, ou acadmico. O PostgreSQL um banco de dados objeto-relacional (nada a ver com linguagens de programao orientadas a objetos), em que cada coisa criada tratada como um objeto, tais como bancos de dados, tabelas, views, triggers, etc. Os objetos podem ter relacionamento entre si. 1.1 - Como se Fala e como se Escreve Uma dvida comum ao PostgreSQL seu nome. As formas corretas so as duas seguintes: Postgres, pronuncia-se postgres (sim o s pronunciado!); PostgreSQL, pronuncia-se postgres s quiu el.
Nunca, jamais, em hiptese nenhuma escrever postgree ou dizer postgr. Infelizmente ainda h fontes na Internet com o nome do Postgres escrito erroneamente, o que leva muita gente tambm a falar errado.
1.2 - Limites do PostgreSQL

Limite Tamanho mximo de um banco de dados Tamanho mximo de uma tabela Tamanho mximo de uma linha (registro) Tamanho mximo de um campo (coluna) Nmero mximo do linhas por tabela Nmero mximo de colunas por tabela Nmero mximo de ndices por tabela Valor Ilimitado 32 TB 1.6 TB 1 GB Ilimitado 250 - 1600 depende do tipo de coluna Ilimitado
1.3 - SGBD Sistemas Gerenciadores de Bancos de Dados o conjunto de softwares (server) que gerenciam um banco de dados, o qual disponibiliza interface para gerenciamento atravs de aplicaes clientes para manipulao dos dados. Exemplos de SGBDs: PostgreSQL, Oracle, MySQL, DB2, Firebird, etc. Aplicaes clientes SGBD Base de Dados 1.4 - Cluster de Banco de Dados Ou simplesmente cluster onde esto os arquivos fsicos de um sistema de banco de dados, os quais contm os objetos, tais como bancos de dados, tabelas e etc. 1.5 - Instalao do PostgreSQL Abra algum terminal de sua preferncia e torne-se root, no Linux, d o *comando:
sudosu
ou
su
*Depende como o sistema foi configurado Mande instalar o PostgreSQL:

aptgetyinstallpostgresql
10
1.6 - Primeiro contato Para fins de aprendizagem, nosso usurio do sistema operacional ser aluno e ento, como root d o seguinte comando:
supostgres
postgres o usurio administrador do banco de dados e agora o prompt do console mostra que viramos esse usurio. E como postgres d o comando:
psql
Entramos na ferramenta cliente do PostgreSQL, o psql e por ele faremos todo o gerenciamento de nosso banco de dados. Seu prompt mudar para: postgres=#, o que indica: postgres nome do banco de dados (quando no se declara explicitamente, o psql entende que deve se conectar a um banco de dados de mesmo nome do usurio); = (igual) indica que est pronto para se receber algum comando novo; # (sustenido) indica que o usurio que est operando o banco de dados no momento um superuser, no caso de ser um usurio comum > (maior que). Obs.: Muitas vezes no lugar do = poder conter algum outro caractere, tal como um ( (parnteses aberto), o qual indica que dever ser devidamente fechado. 1.7 - O psql o terminal interativo do PostgreSQL, um aplicativo cliente, que permite a conexo a um banco de dados. Uso: psql[OPES]...[NOMEBD[USURIO]] Dentre suas opes mais usadas:
-h -P -U -W host port user mquina do servidor de banco de dados ou diretrio do soquete (padro: "/var/run/postgresql") porta do servidor de banco de dados (padro: "5432") nome de usurio do banco de dados (padro: "postgresql") pergunta senha (pode ocorrer automaticamente)
11
1.7.1 - Comandos internos do psql D o seguinte comando para exibir ajuda dos comandos internos: \?
Comando \c[onnect] \cd \copyright \encoding \h \prompt \password \q \set \timing \unset \! [NOME] [COMANDO] [NOME [VALOR]] [CODIFICAO] [COMANDO SQL] [TEXTO] NOME [USURIO] Parmetros [NOMEBD|- USURIO|- MQUINA|- PORTA|-] [DIRETRIO] Descrio conecta a um outro banco de dados (atual "postgres") muda o diretrio de trabalho atual mostra termos de uso e distribuio do PostgreSQL mostra ou define codificao do cliente mostra sintaxe dos comandos SQL, * para todos os comandos pergunta o usurio ao definir uma varivel interna muda a senha de um usurio sair do psql define varivel interna ou lista todos caso no tenha parmetros alterna para durao da execuo de comandos (atualmente desabilitado) apaga (exclui) varivel interna executa comando na shell ou inicia shell iterativa
Buffer de consulta \e \g \p \r \s \w Entrada/Sada \echo \i \o \qecho Informativo \d \d \da \db \dc \dC \dd \dD \df \dF \dFd \dFt [MODELO] [MODELO] [MODELO] [MODELO] [MODELO] [MODELO] [NOME] {t|i|s|v|S} [MODELO] [MODELO] [MODELO] [MODELO] descreve tabela, ndice, sequncia ou viso (adicione "+" para obter mais detalhe) lista tabelas/ndices/sequncias/vises/tabelas do sistema lista funes de agregao lista tablespaces (adicione "+" para obter mais detalhe) lista converses lista converses de tipos mostra comentrio do objeto lista domnios lista funes (adicione "+" para obter mais detalhe) lista configuraes de busca textual (adicione "+" para obter mais detalhes) lista dicionrios de busca textual (adicione "+" para obter mais detalhes) lista modelos de busca textual [STRING] [ARQUIVO] [ARQUIVO] [STRING] escreve cadeia de caracteres na sada padro executa comandos de um arquivo envia todos os resultados da consulta para arquivo ou |pipe escreve cadeia de caracteres para sada da consulta (veja \o) [ARQUIVO] [ARQUIVO] [ARQUIVO] [ARQUIVO] edita o buffer de consulta (ou arquivo) com um editor externo envia o buffer de consulta para o servidor (e os resultados para arquivo ou | (pipe)) mostra o contedo do buffer de consulta reinicia (apaga) o buffer de consulta mostra histrico ou grava-o em um arquivo escreve o buffer de consulta para arquivo
12
Comando \dFp \dg \dn \do \dl \dp \dT \du \l \z Formatao \a \C \f \H \pset
Parmetros [MODELO] [MODELO] [MODELO] [NOME]
Descrio lista analisadores de busca textual (adicione "+" para obter mais detalhe) lista grupos lista esquemas (adicione "+" para obter mais detalhe) lista operadores lista objetos grandes, mesmo que \lo_list
[MODELO] [MODELO] [MODELO]
lista privilgios de acesso de tabelas, vises e sequncias lista tipos de dado (adicione "+" para obter mais detalhe) lista usurios lista todos os bancos de dados (adicione "+" para obter mais detalhe)
[MODELO]
lista privilgios de acesso de tabelas, vises e sequncias (mesmo que \dp)
alterna entre modo de sada desalinhado e alinhado [STRING] [STRING] define o ttulo da tabela, ou apaga caso nada seja especificado mostra ou define separador de campos para sada de consulta desalinhada alterna para modo de sada em HTML (atual desabilitado) NOME [VALOR] define opo de sada da tabela (NOME := {format|border|expanded|fieldsep|footer|null| numericlocale|recordsep|tuples_only|title|tableattr|pager}) mostra somente registros (atual desabilitado) [STRING] define atributos do marcador HTML <table> ou apaga caso nada seja especificado alterna para sada expandida (atual desabilitado)
\t \T \x Cpia, Objetos Grandes \copy \lo_export \lo_import \lo_list \lo_unlink OIDLOB OIDLOB ARQUIVO ARQUIVO [COMENTRIO]
realiza comando SQL COPY dos dados para mquina cliente
operaes com objetos grandes
13
1.7.2 - Variveis do psql Dentro do psql podemos acessar variveis de sistema e tambm criar nossas prprias variveis. Para exibir todas as variveis e seus respectivos contedos:
\set AUTOCOMMIT='on' PROMPT1='%/%R%#' PROMPT2='%/%R%#' PROMPT3='>>' VERBOSITY='default' VERSION='PostgreSQL8.4.2oni486pclinuxgnu,compiledbyGCCgcc4.3.real(Ubuntu4.3.2 1ubuntu11)4.3.2' DBNAME='postgres' USER='user' HOST='/var/run/postgresql' PORT='5432' ENCODING='UTF8'
Acessando o contedo de uma determinada varivel:

\echo:USER
1.7.2.1 - Criando uma nova varivel

\setnova_variavelvalor
Se dermos o comando \set novamente poderemos ver que a varivel criada se encontrar na ltima linha. 1.7.2.2 - Destruindo uma varivel
\unsetnova_variavel
Digitando o comando \set verificamos que ela deixou de existir.
14
2 - SQL (Structured Query Language Linguagem Estruturada de Consulta)

a linguagem usada nos SGBDs por padro, no entanto cada um tem suas particularidades dentro da prpria linguagem, tendo implementaes diferentes. O mesmo objetivo pode ser feito de formas SQL diferentes de um SGBD pra outro. Assim como em linguagens de programao comuns, existem palavras reservadas, as quais no podem ser usadas como identificadores. No PostgreSQL os comandos SQL so finalizados com ponto e vrgula (;) e seus comandos no so case sensitive. 2.1 - DDL (Data Definition Language Linguagem de Definio de Dados) a parte da Linguagem SQL que trata, como o prprio nome diz, da definio da estrutura dos dados, cujos efeitos se do sobre objetos. Criao de bancos de dados, tabelas, views, triggers, etc... Exemplos: CREATE (criao), ALTER(alterao), DROP (remoo), etc. 2.2 - DML (Data Manipulation Language Linguagem de Manipulao de Dados) a parte da Linguagem SQL que no altera a estrutura e sim os registros de uma base de dados, cujos efeitos se do sobre registros. So comandos que fazem consultas, inserem, alteram ou apagam registros. Exemplos: SELECT (consulta), INSERT (insero), UPDATE (alterao), DELETE (remoo), etc. 2.3 - DCL (Data Control Language Linguagem de Controle de Dados) a parte da linguagem SQL referente ao controle de acesso a objetos por usurios e seus respectivos privilgios. Os principais comandos SQL so:
GRANT:
Garante direitos a um usurio; REVOKE: Revoga (retira) direitos dados a um usurio. Os direitos dados a um usurio podem ser: ALL, etc.
CREATE, EXECUTE, REFERENCES, SELECT, TRIGGER, USAGE,
CONNECT, DELETE, INSERT, RULE, TEMPORARY, UPDATE,
15
2.4 - Criao de Usurio

CREATEUSERaluno
<aperte a tecla tab 2X>
O psql tem uma caracterstica muito interessante, a qual te ajuda a completar um comando ou mostrar outras opes possveis apertando a tecla tab. No caso do comando acima, exibir na sua tela:
ADMIN CREATEROLE CONNECTION LIMIT CREATEUSER ENCRYPTED LOGIN IN NOCREATEDB INHERIT NOCREATEROLE NOCREATEUSER NOSUPERUSER NOINHERIT NOLOGIN SYSID ROLE SUPERUSER
UNENCRYPTED CREATEDB
Essas so opes que so exibidas para completarmos nosso comando. No caso o faremos assim:
CREATEUSERalunoNOSUPERUSER;
Criamos o usurio que ir mexer no nosso banco de dados, que por convenincia o mesmo nome do usurio do sistema (Linux). Mas o mesmo, por enquanto tem aes muito limitadas, pois no um superuser. Para sair do psql:
\q
Voltamos ao prompt do console, como o usurio postgres e ento voltaremos a ser root:
exit
Agora devemos voltar a ser o usurio aluno, apenas repita o comando acima:
exit
O usurio root tem o poder de se mutar para qualquer usurio do sistema, nosso usurio aluno no tem tais poderes e precisava se transformar no usurio postgres, que como j foi dito o super usurio do PostgreSQL para adicionar usurios novos ao SGBD, no caso adicionamos o usurio aluno. Com o usurio aluno, para fins de teste, tente conectar-se ao banco de dados postgres:
psqlpostgres
Caso receber a seguinte mensagem:

psql: FATAL: autenticao do tipo Ident falhou para usurio "aluno", o arquivo de configuraodeautenticaodeclientePostgreSQLdeverserconfigurado.
O prximo tpico trata do assunto.
16
3 - Autenticao de cliente e configurao geral do PostgreSQL (pg_hba.conf e postgresql.conf)

Para podermos conectar ao PostgreSQL, nosso usurio tem que estar autorizado. Como root, editaremos o arquivo /etc/postgresql/8.4/main/pg_hba.conf e devem ser adicionadas as seguintes linhas em seu final:
local all host all aluno ident sameuser aluno 192.168.0.1/24 md5
Obs.: No lugar de 192.168.0.0/24 adapte conforme sua mquina na rede. Ao editarmos o arquivo pg_hba.conf demos a devida permisso para que o usurio possa acessar. No entanto, seria bom tambm fazer com que o servidor abra a porta do PostgreSQL (5432) em todas as interfaces. Edite tambm o arquivo /etc/postgresql/8.4/main/postgresql.conf: Mude a linha
#listen_addresses = 'localhost'
para
listen_addresses = '*'
Efetive as alteraes, aps salvar dando o comando no prompt do sistema:

/etc/init.d/postgresqlrestart
Levando-se em conta que agora somos o usurio root, mudemos para postgres:
supostgres
Entre no psql e nele crie uma senha para aluno:

ALTERUSERalunopassword'123456';
Com o usurio aluno, digite no prompt:

psqlh192.168.0.1dpostgres
Foi solicitada uma senha, aquela que foi criada pelo administrador do postgresql. Aps a senha ter sido aceita com sucesso o prompt do psql ficou da seguinte forma: postgres=> Como j foi comentado no incio, o sinal > indica que o usurio aluno limitado. Ento, por fim, mudaremos seu status para superuser. Faremos isso atravs do at ento, nico administrador do banco de dados, o usurio postgres, dentro psql:
ALTERUSERalunoSUPERUSER;
De agora em diante s usaremos o usurio aluno, pois at aqui esse rodzio de usurios foi necessrio, mas agora que tem-se o poder de superuser, s ele far os comandos.
17
4 - Importando um banco de dados j pronto de um arquivo

Nos extras voc encontra-se o cdigo do arquivo curso.sql, copie e salve-o no diretrio /tmp. Agora vamos rod-lo:
\i/tmp/curso.sql
Apesar de estarmos criando um novo banco de dados chamado curso, devemos temporariamente nos conectar ao servidor e pra isso necessrio especificar algum banco de dados pr existente, no caso o postgres que assim como o usurio de mesmo nome criado na instalao do SGBD. Poderamos ter feito de maneira diferente, no prompt do sistema operacional, com o psql:
psqlf/tmp/curso.sqlpostgres
Agora, pra entrar no banco de dados usaremos no shell:

psqlcursoh192.168.7.2
Como definimos no arquivo pg_hba.conf, ser pedida uma senha. Saia do psql:
\q
D o comando:
psqlcurso
Dessa vez no foi pedida uma senha. Pedimos a conexo na mquina local (localhost). Sugesto de estudo: arquivos pg_hba.conf e postgresql.conf.
18
5 - Identificadores
Para nomearmos identificadores devemos usar letras ou _ (underline): Certo:
CREATEDATABASEabc;
Errado:
CREATEDATASE123;
Se por acaso fosse criado um banco usando letras maisculas:

CREATEDATABASEABC;
O mesmo seria considerado como abc e facilmente apagado como se tivesse sido criado com letras minsculas:
DROPDATABASEabc;
H uma maneira de fazer com os identificadores criados sejam case-sensitive, para isso devemos criar os nomes de identificadores entre aspas dupas ( ). Inclusive, pode-se at criar identificadores iniciando com caracteres numricos:
CREATEDATABASEABc; CREATEDATABASE123tEste;
Liste os bancos de dados com:

\l
Letra L minscula
Essa no uma boa prtica e faz com que toda vez que for preciso manipul-los, devero ser referenciados tambm entre aspas:
DROPDATABASEAbc; DROPDATABASE123tEste;
19
6 - Modelos de Banco de Dados - Templates

O comando "CREATE DATABASE" funciona copiando um banco de dados existente. Por padro, ele copia o banco de dados de sistema chamado "template1", deste modo modelo para criao de outros banco de dados. Se forem adicionados objetos no "template1", esses objetos sero copiados para bancos criados posteriormente. Por exemplo, se for instalada uma linguagem procedural como a PL/pgSQL no "template1", estar automaticamente disponvel em bases de dados criadas posteriormente. H um outro banco de dados padro do sistema chamado "template0". Esse banco contm os mesmos dados que o contedo inicial de "template1", que , apenas os objetos padres pr definidos pela verso do PostgreSQL. uma base de dados imutvel e que no aceita conexes. Quando se cria um banco copiando "template0" ao invs de "template1" criado um banco de dados "virgem" que no contm nada do que foi adicionado a "template1". Sintaxe:
CREATEDATABASEnome_do_banco_de_dadosTEMPLATEbanco_modelo;
Exemplos:
/*Conexoaobancopadrotemplate1*/ \ctemplate1 /*Criaodeumasimplestabeladentrodetemplate1*/ CREATETABLEtb_exemplo(campoint); /*Inserodevaloresnatabelacriadadentrodetemplate1*/ INSERTINTOtb_exemploVALUES(1),(2),(3),(4),(5); /*Criaodeumnovobancodedados*/ CREATEDATABASExyz; /*Conectandoaonovobancodedados*/ \cxyz /*Visualizandoaexistnciadetabelasdentrodobancorecmcriado*/ \d /*Consultanatabelaexistente*/ SELECT*FROMtb_exemplo;
A partir de agora todos os bancos novos criados no sistema tero a tabela tb_exemplo com seus valores que foram inseridos dentro do banco template1. Como pde ser comprovado, ao se criar objetos dentro de template1, esses objetos sero retransmitidos para bancos de dados que forem criados posteriormente a no ser que se queira um outro banco de dados como modelo, ou mesmo um banco de dados virgem, como no exemplo a seguir:
CREATEDATABASEnovo_bancoTEMPLATEtemplate0;
O banco de dados criado acima um banco de dados virgem, pois no h qualquer objeto no mesmo, pois seu modelo foi o template0. Se for preciso criar um novo banco de dados tomando outro como modelo tambm posssvel:
CRATEDATABASEcurso2TEMPLATEcurso;
20
Ao se conectar nesse banco de dados poder ser constatado que possui os mesmos objetos e registros que seu modelo (curso). Para prosseguir com o aprendizado do PostgreSQL, aps as devidas verificaes de resultados at aqui apresentados serem comprovadas, recomendvel fazer uma limpeza do que foi feito para fins de exemplos:
/*Conectandonovamenteatemplate1*/ \ctemplate1 /*Apagandoatabelacriada*/ DROPTABLEtb_exemplo; /*Apagandoobancocriado*/ DROPDATABASExyz;
21
7 - Tipos de Dados
7.1 - Numricos
Nome smallint integer bigint Alias int2 Int, int4 int8 Faixa -32768 at +32767 -2147483648 +2147483647 -9223372036854775808 at +9223372036854775807 Sem limite 6 dgitos decimais de preciso 15 dgitos preciso decimais Tamanho 2 bytes at 4 bytes 8 bytes Descrio integer de 2 bytes com sinal integer de 4 bytes com sinal integer de 8 bytes com sinal
* numeric [ (p,s) ] real
decimal(p,s) float4
varivel 4 bytes
nmero exato customizvel
com
preciso
preciso nica de ponto flutuante preciso dupla de ponto flutuante auto incremento inteiro de 4 bytes auto incremento inteiro de 8 bytes
double precision float8 serial bigserial serial4 serial8
de 8 bytes 4 bytes 8 bytes
1 at 2147483647 1 at 9223372036854775807
* p = preciso: quantidade de dgitos (antes do ponto flutuante + depois do ponto flutuante) s = escala: quantidade de dgitos aps o ponto flutuante 7.1.1 - Mscaras para formatos numricos
Modelo 9 0 . (ponto) PR S L D G MI PL SG RN [a] TH ou th V EEEE Descrio valor com o nmero especificado de dgitos valor com zeros esquerda ponto decimal valor negativo entre < e > sinal preso ao nmero (utiliza o idioma) smbolo da moeda (utiliza o idioma) ponto decimal (utiliza o idioma) separador de grupo (utiliza o idioma) sinal de menos na posio especificada (se nmero < 0) sinal de mais na posio especificada (se nmero > 0) sinal de mais/menos na posio especificada algarismos romanos (entrada entre 1 e 3999) sufixo de nmero ordinal desloca o nmero especificado de dgitos (veja as notas sobre utilizao) notao cientfica (ainda no implementada)
, (vrgula) separador de grupo (milhares)
Notas: a. RN roman numerals.
22
7.2 - De Caracteres
Nome character(n) character varying(n) text Alias char(n) varchar(n) Descrio tamanho fixo e no varivel de caracteres tamanho varivel de caracteres com limite tamanho varivel e sem limite
7.3 - Valor nulo (null) Um valor nulo simplesmente um valor no preenchido. 7.4 - Lgico ou Booleano
Nome boolean Alias bool Descrio logical Boolean (true/false)
Admite apenas dois estados true ou false. Um terceiro estado, unknown, representado pelo valor nulo (null). Valores literais vlidos para o estado true: TRUE, t, true, y, yes, 1. Para o estado false, os seguintes valores podem ser usados: FALSE, f, false, n, no, 0. 7.5 - Data e Hora
Nome date interval [ (p) ] time [ (p) ] [ without time zone ] time [ (p) ] with time zone timestamp [ (p) ] [without time zone ] timestamp [ (p) ] with time zone timestamptz timetz Alias Descrio calendar date (year, month, day) intervalo de tempo horas horas fuso horrio data e horas Data e horas com fuso horrio
Alguns tipos de data e hora aceitam um valor com uma preciso p que especifica a quantidade de dgitos fracionais retidos no campo de segundos. Por exemplo: Se um campo for especificado como do tipo interval(4), se no mesmo for inserido um registro com valor 00:00:33.23439, o mesmo ter seu valor arredondado para 00:00:33.2344, devido customizao da preciso para 4 dgitos. A faixa de preciso aceita de 0 a 6. Alm de todos os tipos de dados apresentados aqui existem outros, porm no so to usados. A variedade muito grande. Para maiores informaes, no psql digite o comando:
\dT+
Obs.: O + (mais) nos comandos do psql, d mais informaes quando inserido ao final de um comando.
23
7.5.1 - Mscaras de Data e Hora

Formato HH HH12 HH24 MI SS SSSS AM ou PM YYYY YY Y BC ou AD MONTH MM DAY DD D DDD WW Descrio Hora do dia (De 01 a 12) Hora do dia (De 01 a 12) Hora do dia (De 00 a 23) Minuto (De 00 a 59) Segundo (De 00 a 59) Segundo do dia (De 0 a 86399) Meridiano Ano (4 dgitos) Ano (2 dgitos) Ano (ltimo dgito) Era Nome do ms Ms (De 01 a 12) Nome do dia da semana Dia do ms (De 01 a 31) Dia da semana (De 1 a 7; Domingo = 1) Dia do ano (De 1 a 366) Semana do ano (De 1 a 53)
24
8 - Obtendo ajuda de comandos SQL dentro do psql

O utilitrio cliente do PostgreSQL, o psql, dentre suas funes, possui um sistema de auto ajuda para o usurio para comandos SQL. Para ajuda geral:
\h
So mostrados os comandos SQL. Mas se por acaso necessitar de uma explicao maior sobre um determinado comando:
\h<comando>[opes]
Exemplos:
\hCREATE
So mostradas todas as formas do comando SQL CREATE.

\hCREATETABLE
Exibe apenas informaes de criao de tabelas.

\hCREATEDATABASE
Exibe apenas informaes de criao de banco de dados.
25
9 - Comentrios do PostgreSQL
Assim como os comandos SQL, devem terminar com ponto e vrgula, podem ser:
CREATEDATABASEdb_teste;nicalinha
ou
CREATEDATABASEdb_teste;/* mltiplaslinhas */
26
10 - Conectando-se a outro banco de dados dentro do psql

Estamos no banco de dados curso agora, para nos conectarmos ao banco de dados postgres:
\cpostgres
Para voltar ao curso:

\ccurso
27
11 - Criando, Modificando e Apagando Objetos

De acordo com o ttulo, temos respectivamente os seguintes comandos: CREATE, ALTER e DROP. Vejamos agora alguns exemplos dos mesmos com o tipo de objeto DATABASE (banco de dados):
CREATEDATABASEdb_teste;Bancodedadosdb_testefoicriado!; ALTERDATABASEdb_testeRENAMETOdb_teste2;/*Bancodedadosdb_testefoialterado, seunovonomeagoradb_teste2*/; DROPDATABASEdb_teste2;Bancodedadosdb_teste2foiapagado;
importante lembrar que quando o objeto em questo est sendo acessado no momento, no se pode alter-lo.
28
12 - Tabelas
a estrutura cuja funo guardar dados. Possui colunas (campos) e linhas (registros). Cada coluna tem sua estrutura da seguinte forma, na sequncia: nome, tipo e modificadores. 12.1 - Criao de Tabelas
CREATETABLEnome_tabela( nome_campo tipo_dado[DEFAULTexpressao_padrao][CONSTRAINT nome_restricaorestricao], );
12.2 - Tabelas temporrias So tabelas que s podem ser acessadas diretamente pelo usurio que a criou e na sesso corrente. Assim que o usurio criador da mesma se desconectar da sesso em que ela foi criada, a tabela temporria deixar de existir. A sintaxe de criao igual de uma tabela normal, apenas acrescenta-se a palavra TEMPORARY aps CREATE:
CREATETEMPORARYTABLEtabela_tmp( );
12.3 - Exibindo tabelas criadas

\d
12.4 - Exibindo a estrutura de uma tabela

\dnome_da_tabela
Com detalhes:
\d+nome_da_tabela
29
12.5 - Alterando Tabelas s vezes pode acontecer de aps termos criado algumas tabelas, haver a necessidade de modificlas. O quadro abaixo contm de maneira resumida e objetiva os meios para se fazer as alteraes de acordo com o que for preciso:
ADD [ COLUMN ] coluna tipo [ restrio_coluna [ ... ] ] restrio_tabela TYPE tipo [ USING expresso ] SET DEFAULT expresso ALTER [ COLUMN ] coluna DROP DEFAULT { SET | DROP } NOT NULL SET STATISTICS inteiro SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN } CLUSTER ON nome_ndice DROP [ COLUMN ] coluna [ RESTRICT | CASCADE ] CONSTRAINT nome_restrio [ RESTRICT | CASCADE ] TRIGGER [ nome_gatilho | ALL | USER ] RULE nome_regra_reescrita TRIGGER [ nome_gatilho | ALL | USER ] ALTER TABLE nome_tabela ENABLE REPLICA TRIGGER nome_gatilho ALWAYS TRIGGER nome_gatilho RULE nome_regra_reescrita REPLICA RULE nome_regra_reescrita ALWAYS RULE nome_regra_reescrita INHERIT tabela_ancestral NO INHERIT tabela_ancestral OWNER TO novo_dono RENAME TO novo_nome_da_tabela COLUMN nome_da_coluna TO novo_nome_da_coluna
DISABLE
RESET ( parmetro_armazenamento [, ... ] ) WITHOUT SET CLUSTER OIDS
( parmetro_armazenamento = valor [, ... ] ) TABLESPACE nome_tablespace SCHEMA nome_esquema
Exemplos: Antes e aps cada comando SQL abaixo d o comando \d colaboradores para verificar as alteraes: - Adicionando um novo campo:
ALTERTABLEcolaboradoresADDCOLUMNnova_colunaint2;
- Mudando o tipo de dados de um campo:

ALTERTABLEcolaboradoresALTERnova_colunaTYPEnumeric(7,2);
30
- Alterando o nome de um campo:

ALTERTABLEcolaboradoresRENAMECOLUMNnova_colunaTOultima_coluna;
- Alterando o nome de uma tabela:

ALTERTABLEpedidoRENAMETOpedidos;
- Apagando um campo de uma tabela:

ALTERTABLEcolaboradoresDROPCOLUMNultima_coluna;
12.6 - Apagando uma tabela O comando DROP, como j foi mencionado, serve pra eliminar objetos, tais como uma tabela. O comando mais simples pra se deletar uma:
DROPTABLEnome_da_tabela;
Se a mesma for referenciada por outra tabela, no poder ser apagada. A no ser que:
DROPTABLEnome_da_tabelaCASCADE;
31
13 - Tablespaces
Tablespaces no PostgreSQl permitem a administradores de bancos de dados definirem onde os arquivos fsicos que representam os objetos do banco de dados sero gravados. Ou seja, possvel definir o diretrio onde os objetos sero armazenados. Aps a criao de um tablespace, o mesmo pode ser referenciado pelo nome ao criar os objetos que sero gravados no local desejado. Sintaxe:
CREATETABLESPACEnome[OWNERusurio]LOCATION'diretrio';
Exemplos: Primeiro vamos criar um diretrio para ser o tablespace (como root):
mkdir/tbs
Dando a propriedade do diretrio para o usurio postgres e o grupo de usurios postgres:

chownRpostgres:postgres/tbs
Criando o tablespace:
CREATETABLESPACEts_tmpLOCATION'/tbs';
Criando uma tabela no tablespace ts_tmp:

CREATETABLEtb_ts( codserialPRIMARYKEY, nomevarchar(15) )TABLESPACEts_tmp;
Obs.: No diretrio do cluster, h um subdiretrio chamado pg_tblspc, o qual contm links que apontam para o local de cada tablespace criado.
32
14 - Restries (Constraints)
So regras que inserimos para que uma coluna no receba dados indesejados. Por exemplo: imagine uma tabela de produtos que tem dentre seus campos um que se refere ao preo. Se no for colocada uma restrio, sero aceitos valores negativos (<0). 14.1 - Tipos de Restries
14.1.1 - CHECK A mais genria das restries, permite especificar que valor, em uma determinada coluna, deve satisfazer uma expresso booleana. Exemplo:
CREATETABLEprodutos( cod_prodint2, preconumeric(7,2)CHECK(preco>0) );
Nesse exemplo evita que no campo preco seja inserido qualquer valor que no seja maior que zero. 14.1.2 - NOT NULL Faz com que a coluna no aceite valores nulos, ou seja, a partir do momento em que for inserido um novo registro a(s) coluna(s) com a restrio NOT NULL dever receber algum valor. 14.1.3 - UNIQUE Assegura que todos os registros, na coluna especificada no tero valores repetidos. 14.1.4 - PRIMARY KEY Tambm conhecida como Chave Primria. Seus valores so nicos (UNIQUE) e no nulos (NOT NULL), tecnicamente podemos dizer que: PRIMARY KEY = NOT NULL + UNIQUE Toda vez que criamos uma chave primria um ndice (INDEX) atribudo para a mesma. Exemplos:
CREATETABLEprodutos( cod_prodint2UNIQUENOTNULL, preconumeric(7,2)CHECK(preco>0) );
Tem o mesmo efeito de:

CREATETABLEprodutos( cod_prodint2PRIMARYKEY, preconumeric(7,2)CHECK(preco>0) );
33
Pode tambm ser da seguinte maneira:

CREATETABLEprodutos( cod_prodint2, preconumeric(7,2)CHECK(preco>0), PRIMARYKEY(cod_prod) );
Mais esta:
CREATETABLEprodutos( cod_prodint2CONSTRAINTpk_produtosPRIMARYKEY, preconumeric(7,2)CHECK(preco>0) );
E esta:
CREATETABLEprodutos( cod_prodint2, preconumeric(7,2)CHECK(preco>0), CONSTRAINTpk_produtosPRIMARYKEY(cod_prod) );
Nestas duas ltimas demos um nome chave primria. 14.1.5 - FOREIGN KEY Tambm conhecida como Chave Estrangeira. Especifica que os valores em uma coluna (ou grupo de colunas) deve corresponder aos valores que esto em um campo ou outra tabela. Quando entre tabelas chamamos de integridade relacional entre tabelas. Os valores dos campos referenciados devem ser nicos.
produtos cod_produto (pk pk_produtos) preco vendas cod_vendas (pk pk_vendas) produto (fk pk_produtos)
Acima est representado o relacionamento de vendas com produtos, vejamos como a tabela vendas foi criada:
CREATETABLEvendas( cod_vendasint2PRIMARYKEY, produtoint2REFERENCESprodutos(cod_prod) );
O campo produto, faz referncia chave primria da tabela produtos. Assim como acontece com chaves primrias, tambm podemos nomear chaves estrangeiras:
CREATETABLEvendas( cod_vendasint2PRIMARYKEY, produtoint2, CONSTRAINTfk_prodFOREIGNKEY(produto)REFERENCESprodutos(cod_prod) );
34
14.1.6 - O Modificador DEFAULT Quando uma estrutura de uma tabela construda, pode se definir a determinados campos, caso os mesmos no forem preenchidos (valores nulos), ter um valor atribudo automaticamente. Ou seja, se nada for atribudo a ele pelo usurio, o sistema o far:
CREATETABLEtb_teste_default( nomevarchar(15), ufchar(2)DEFAULT'SP' );
O exemplo de criao de tabela acima, mostra o campo uf, o qual se no for inserido algum valor, o sistema, por padro o preencher com o valor SP.
35
15 - Descrio de Objetos
Tambm conhecido como Comentrio de Objetos. Exemplo de como se insere uma descrio em uma tabela:
COMMENTONTABLEcolaboradoresIS'Funcionriosdaempresa';
Para visualizar:
\dt+colaboradores
Para excluir:
COMMENTONTABLEcolaboradoresISNULL;
36
16 - Operadores
Operador/Elemento . :: [] ^ |/ ||/ @ * / % + IS ISNULL NOTNULL IN BETWEEN OVERLAPS LIKE ILIKE SIMILAR < > = <> >= <= NOT AND OR || != ~~ ~~* IS NULL NOT NULL Descrio/Uso separador de nome de tabela/coluna typecast estilo PostgreSQL seleo de elemento de array menos unrio exponenciao raiz quadrada raiz cbica valor absoluto multiplicao diviso resto de diviso adio subtrao IS TRUE, IS FALSE, IS UNKNOWN, IS NULL teste para nulo teste para no nulo em entre sobreposio de intervalo de tempo comparao de string case sensitive comparao de string case insensitive comparao de string por similaridade menor que maior que igualdade, atribuio diferente maior ou igual que menor ou igual que NO lgico E lgico OU lgico concatena strings
37
17 - SELECT - Consultas (Queries)

Consultar ou fazer uma query em um banco de dados, pode ser simplesmente dito como selecionar os dados. Um simples SELECT:
SELECT5+2; ?column? 7 (1registro)
Podemos melhorar um pouco:

SELECT5+2AS"Resultado"; Resultado 7 (1registro)
Uma operao matemtica que retorna o valor absoluto (no negativo), usando o operador @:
SELECT@310AS"Resultado"; Resultado 7 (1registro)
O parmetro AS usado para dar um rtulo coluna, na ausncia dele usado o nome do campo da tabela. No caso acima, o resultado exibido no foi retirado de uma tabela e sim de uma simples operao matemtica. Foram inseridas as aspas para que a palavra Resultado no aparecesse apenas com letras minsculas. Geralmente puxamos dados de tabelas e a forma mais simples pra isso a seguinte:
SELECTcampoX,campoY,...,campoNFROMnome_da_tabela;
Em nosso banco de dados de exemplo, h uma tabela chamada colaboradores. Vamos fazer uma query selecionando todos os dados da mesma:
SELECT*FROMcolaboradores;
Obtivemos ento todos os registros de todos os campos. No comando acima o asterisco * representa todos os campos da tabela. Agora vamos somente selecionar alguns campos:
SELECTnome,snomeFROMcolaboradores;
Dessa vez selecionamos apenas os campos nome e snome. Modificando um pouco o comando:
SELECTnome||''||snomeAS"Nomecompleto"FROMcolaboradores;
38
Com o uso operador de concatenao de strings, o pipe duplo ||, juntamos os valores de duas colunas em uma s intercalados com um espao (um espao entre as aspas simples). Podemos misturar concatenaes tambm:
SELECT nome||''||snomeAS"Nomecompleto", cidade||''||ufAS"CidadeUF" FROMcolaboradores;
Fazer operaes matemticas com os valores dos campos um recurso interessante tambm. Suponhamos que preciso calcular o valor do Vale Transporte (que de 6% do salrio)para cada empregado:
SELECT nome||''||snomeAS"Nomecompleto", salario*0.06AS"ValeTransporte" FROMcolaboradores;
Se for necessrio saber quais so as cidades que se encontram na tabela, de forma que no haja repetio:
SELECTDISTINCTcidadeFROMcolaboradores;
A palavra chave DISTINCT faz com que seja exibida apenas uma ocorrncia dos valores da tabela selecionada. Mas possvel fazer a distino avaliando mais de um campo:
SELECTDISTINCTcidade,ufFROMcolaboradores;
Se tiver cidades com nomes iguais, mas de diferentes estados, as duas seriam constadas, o campo uf seria o diferencial. O recurso de paginao muito til quando no queremos ver todos os resultados. Se for optado por exibir apenas os 7 primeiros registros:
SELECT*FROMcolaboradoresLIMIT7;
Ou ainda exibir por faixa de registros. Do dcimo primeiro ao dcimo stimo, por exemplo:
SELECT*fromcolaboradoresLIMIT7OFFSET10;
Uma traduo para a query acima: selecionar todos os campos da tabela colaboradores, limitando a exibio a 7 registros e dispensando os 10 primeiros. Sem limites, mas cortando os 6 primeiros:
SELECT*fromcolaboradoresOFFSET6;
17.1 - O Comando TABLE O comando TABLE tem o mesmo efeito que SELECT * FROM, ou seja:
TABLE
= SELECT*FROM
Logo, se fizermos:
TABLEcolaboradores;
a mesma coisa que:
39
17.2 - SELECT sem FROM H uma forma de se fazer consultas usando o SELECT sem o FROM da seguinte forma:
SELECTtabela.coluna;
Exemplo:
SELECTcolaboradores.*;
Erro! Para que a omisso da palavra FROM d certo necessrio habilitar. H duas formas: Por seo:
SETadd_missing_fromTOON;
De forma definitiva: Edite o arquivo postgresql.conf (/etc/postgresql/8.4/main/postgresql.conf) e mude a linha:

#add_missing_from = off
para
add_missing_from = on
E por fim mande o servio carregar as novas configuraes:

/etc/init.d/postgresqlreload
No preciso reiniciar (restart) 17.3 - Type Casts Faz uma converso de um tipo de dado para outro:
CAST(expressoAStipo)
ou
expresso::tipo Exemplos: SELECTCAST(('7'||'0')ASint2)+7AS"Resultado"; SELECT(('7'||'0')::int2)+7AS"Resultado";
Obs.: CAST est conforme o padro SQL e o operador :: prprio do PostgreSQL.
40
17.4 - Case - Um IF disfarado
O comando CASE, para quem conhece linguagens de programao, pode ser comparado ao IF. Ao invs de usarmos a sequncia IF, THEN, ELSE, usamos CASE, WHEN, THEN, ELSE e finalizamos com um END.
SELECTnome||''||snomeAS"NomeCompleto", CASEuf WHEN'SP'THEN'SoPaulo' WHEN'MG'THEN'MinasGerais' ELSE'RiodeJaneiro' END AS"Origem" FROMcolaboradores;
Caso uf for igual a SP; retorna SoPaulo, igual a MG; retorna MinasGerais, seno; retorna Riode
Janeiro.
17.5 - COALESCE Em certas situaes, quando h valores nulos em uma tabela necessrio preench-los provisoriamente com algum valor. O comando COALESCE faz essa funo. Como exemplo, a tabela colaboradores ser usada. A mesma no tem valores nulos. O comando INSERT ser explicado mais adiante, mas por hora nos ser til:
INSERTINTOcolaboradores(mat_id,nome,snome)VALUES(00020,'Z','Ningum');
Quando for feita uma query na tabela, constar apenas valores para os campos e uf.
mat_id, nome, snome
Os observadores mais atentos notaro que no INSERT no consta uma referncia ao campo uf, no entanto, se olharmos o anexo que contm todo o cdigo SQL de como foi feita a estrutura da tabela, ver que h um valor padro DEFAULT associado. Uma query na tabela colaboradores, mostra que o ltimo registro agora tem valores nulos. Para exemplo, um dos campos nulos ser usado com o comando COALESCE, o campo setor:
SELECTnome,snome,COALESCE(setor,'inexistente')assetor FROMcolaboradores;
17.6 - NULLIF uma funo que retorna um valor nulo se o primeiro valor for igual ao segundo valor, caso contrrio retornar o primeiro valor. Isso pode ser usado para fazer a operao inversa de COALESCE: Exemplos:
SELECTNULLIF(1,1);RetornaNull SELECTNULLIF(7,7);RetornaNull SELECTNULLIF(9,1);Retorna9 SELECTNULLIF(3,3);RetornaNull
41
17.7 - GREATEST e LEAST As funes GREATEST e LEAST selecionam respectivamente o maior ou menor valor de uma lista de qualquer nmero de expresses. As expresses devem ser todas compatveis com o tipo de dados comum, que ser o tipo de resultado. Valores nulos na lista sero ignorados. O resultado ser nulo apenas se todas as expresses avaliadas forem tambm nulas. Essas funes no fazem do padro SQL, mas so uma extenso comum. Alguns outros bancos de dados faro o retorno nulo se qualquer argumento for nulo, ao invs de somente quanto todos forem nulos. Exemplos:
SELECTGREATEST(2,1,9,55,20);Retorna55 SELECTLEAST(2,1,9,55,20);Retorna9 SELECTGREATEST(NULL,5);Retorna5 SELECTLEAST(NULL,3);Retorna5
17.8 - Conjuntos Relembrando a teoria de conjuntos de matemtica:
Os dois conjuntos representam, respectivamente as tabelas tab_mult2 e tab_mult3. No PostgreSQL funcionam de acordo com a correspondncia ou no de colunas selecionadas entre uma tabela e outra. Os comandos abaixo criaro as tabelas citadas e as preenchero:
CREATETEMPTABLEtab_mult2(valorint2); CREATETEMPTABLEtab_mult3(valorint2); INSERTINTOtab_mult2VALUES(2),(4),(6),(8),(10),(12); INSERTINTOtab_mult3VALUES(3),(6),(9),(12),(15);
42
17.8.1 - Fazendo operaes de conjunto Temos Unio (UNION), Interseco (INTERSECT) e Exceo (EXCEPT).
17.8.1.1 - UNION a unio de todos os valores das colunas das tabelas envolvidas. Por padro cada valor exibido somente uma vez, mas se for usado o parmetro ALL, valores que existem em mais de uma tabela exibido de acordo com a ocorrncia.
SELECTcampo1,campo2,...,campoNFROMtabelaX UNION[ALL] SELECTcampo1,campo2,...,campoNFROMtabelaY;
Exemplo:
SELECTvalorFROMtab_mult2UNIONSELECTvalorFROMtab_mult3;
17.8.1.2 - INTERSECT S retorna valores em comum entre as tabelas:

SELECTvalorFROMtab_mult2INTERSECTSELECTvalorFROMtab_mult3;
17.8.1.3 - EXCEPT a diferena da tabela da esquerda menos a da direita:

SELECTvalorFROMtab_mult2EXCEPTSELECTvalorFROMtab_mult3;
S foram retornados os valores que s existem em tab_mult2.
43
17.9 - Expresses Regulares Tem grande utilidade em operaes que requerem manipulao complexa de strings.
Operador ~ ~* !~ !~* ^ $ [] [-] ^[-] | Sintaxe ~ 'string' ~* 'string' !~ 'string' !~* 'string' ~ '^string' ~ 'string$' ~ [xyz] ~ [0-5] ~ ^[a-z] string1 |string2 Retorna a string exata Retorna a string de modo insensitive Retorna o que no for a string exata Retorna o que no for a string exata de modo insensitive Retorna o que comea com a string Retorna o que acaba com a string Retorna o que contiver algum dos caracteres declarados entre os colchetes Retorna o que contiver o intervalo de caracteres que separado pelo hfen Retorna o que comea com o algum dos caracteres do intervalo Retorna se alguma das strings coincide Descrio
Exemplos:
SELECT*fromcolaboradoreswherenome~'Chiquinho';/*ProcurapelonomeChiquinho*/; SELECT*fromcolaboradoreswherenome~*'chiquinho';/*Procurapelonomechiquinho (insensitive)*/; SELECT*fromcolaboradoreswhereuf!~'SP';/*RetornaoquenotemSPemuf*/;
17.10 - WHERE A clusula WHERE usada para fazer filtragem nas consultas. A palavra where traduzida significa onde. Na linguagem SQL ela pode ser interpretada como por exemplo selecionar as colunas de uma determinada tabela, onde atenda a esta(s) condio(es): <condio(es)>. Para determinar uma condio usamos operadores, exemplos: Selecionar todos os campos da tabela colaboradores, onde o setor Marketing e que no seja do estado de SP:
SELECT*FROMcolaboradoresWHEREsetor='Marketing'ANDuf!='SP';
Buscar dentre os registros aqueles em que o campo cargo no foi preenchido:

SELECT*FROMcolaboradoresWHEREcargoISNULL;
44
17.10.1 - OR, AND, IN, agrupamento de comparaes e BETWEEN Exemplos: Consultar os colaboradores que so do setor de Marketing ou de Publicidade:
SELECT*FROMcolaboradoresWHEREsetor='Marketing'ORsetor='Publicidade';
O comando acima no ficou de uma forma inteligente e elegante, soou repetitivo. Podemos melhorar isso com o operador IN:
SELECT*FROMcolaboradoresWHEREsetorIN('Marketing','Publicidade');
Descobrir quem tem o cargo de diretor e de SP e do setor de Marketing:

SELECT*FROMcolaboradoresWHEREcargo='Diretor'ANDuf='SP'ANDsetor='Marketing';
O comando anterior outro exemplo que podemos melhorar a sintaxe, atravs do agrupamento de comparaes, em casos que o operador o mesmo (no caso exposto, de igualdade), em que de um lado se colocam os campos e no outro seus respectivos valores:
SELECT*FROMcolaboradoresWHERE(cargo,uf,setor)=('Diretor','SP','Marketing');
Como fazer uma consulta por faixa salarial? Quem ganha entre R$ 1000,00 e R$ 2000,00?
SELECT*fromcolaboradoresWHEREsalarioBETWEEN1000AND2000;
O comando acima o equivalente a:

SELECT*fromcolaboradoresWHEREsalario>=1000ANDsalario<=2000;
No entanto, usando BETWEEN mais fcil de se entender e dependendo do caso pode economizar digitao de forma inteligente. 17.10.2 - O Operador LIKE ou ~~ Tanto faz usar a palavra LIKE ou ~~, pois o efeito ser o mesmo. A finalidade fazer consultas case sensitive tomando como base uma string ou parte dela atravs de caracteres curingas:
Smbolo % _ \\ Funo Porcentagem: simboliza um, vrios ou nenhum caractere na posio. Underline: simboliza um ncio caractere na posio. Barra invertida dupla: Escape, faz com que se interprete literalmente os caracteres % e _.
Exemplos: Buscar colaboradores que seu nome inicia com a letra A:

SELECT*FROMcolaboradoresWHEREnome~~'A%';
Selecionar colaboradores que a segunda letra de seu nome a:

SELECT*FROMcolaboradoresWHEREnomeLIKE'_a%';
45
17.10.3 - O Operador ILIKE ou ~~* Sua nica diferena com o LIKE o fato de ser case insensitive, ou seja, no considera letras maisculas ou minsculas. Portanto, como exemplos, podem ser tomados os do LIKE, s que, logicamente, trocando as palavras LIKE por ILIKE e invertendo na string da condio maisculas por minsculas e vice-versa. 17.10.4 - SIMILAR TO Recurso parecido com o operador LIKE, porm possui mais recursos de pesquisa de strings atravs do uso de caracteres curinga.
Curinga % [A-J] [XYZ] [^XYZ] | || _ Descrio So os mesmos curingas usados no LIKE. Qualquer caractere entre A e J na posio X, Y ou Z na posio Qualquer caractere, exceto X, Y ou Z Operador OR Concatenao
Exemplos: Todos os nomes de colaboradores que a segunda letra uma vogal sem acentuao:
SELECT*FROMcolaboradoresWHEREnomeSIMILARTO'_[aeiou]%';
Todos os nomes de colaboradores que a segunda letra no uma vogal sem acentuao:
SELECT*FROMcolaboradoresWHEREnomeSIMILARTO'_[^aeiou]%';
Busca de registros cuja uf no seja SP ou MG:

SELECT*FROMcolaboradoresWHEREufNOTSIMILARTO'(SP|MG)';
Nomes cuja primeira letra de S Z:

SELECT*FROMcolaboradoresWHEREnomeSIMILARTO'[SZ]%';
17.10.5 - O operador IS NULL Valores nulos significam no preenchimento. Muitas vezes, equivocadamente para fazer uma consulta em que se buscam valores nulos usa-se o sinal de igualdade =. Ao invs disso, o correto usar operador IS: Exemplos: Fazer uma consulta para achar registros onde o campo cargo no foi preenchido: Forma errada:
SELECT*FROMcolaboradoresWHEREcargo=NULL;
46
Forma correta:
17.10.6 - O operador NOT Significa negao de algo. Exemplo: Filtrando resultados sem valores nulos em cargo:
17.11 - A Clusula ORDER BY Quando feita uma consulta sem se especificar um critrio de ordenao, os registros so exibidos por ordem de insero ou atualizao. Atravs da clusula ORDERBY ordena-se consultas de acordo com os valores de uma determinada coluna. Exemplos: Consultar todos os funcionrios ordenando pela data de admisso ( campodt_admis):
SELECT*FROMcolaboradoresORDERBYdt_admis;
Por padro consultas com a clusula ORDERBYso exibidas em ordem ascendente (ASC). O comando anterior tambm poderia ser dado desta maneira, especificando que a ordem crescente:
SELECT*FROMcolaboradoresORDERBYdt_admisASC;
Ou colocar em ordem decrescente (DESC):

SELECT*FROMcolaboradoresORDERBYdt_admisDESC;
possvel tambm especificarmos mais de uma coluna e tipos de ordem diferentes pra cada, como por exemplo consultar primeiramente pela data de admisso decrescente e nome crescente:
SELECT*FROMcolaboradoresORDERBYdt_admisDESC,nomeASC;
Ao invs dos nomes das colunas podemos usar a classificao posicional. Levando-se em conta que os campos so numerados da esquerda pra direita e iniciando por 1, temos nome=2 e snome=3. Uma consulta levando-se em conta primeiro o sobrenome (snome) e depois o nome:
SELECT*FROMcolaboradoresORDERBY3,2;
Classificando por apelido de coluna:

SELECTnome||''||snomeAS"NomeCompleto",setor,cidade||''||ufAS"Localidade"FROM colaboradoresORDERBY"NomeCompleto";
47
Usando colunas invisveis... Colunas invisveis so assim chamadas aquelas que so usadas como critrio de classificao, mas no aparecem no resultado:
SELECTnome||''||snomeAS"NomeCompleto",setor,cidade||''||ufAS"Localidade"FROM colaboradoresORDERBYdt_admisDESC;
A coluna dt_admis no aparece no resultado. 17.12 - SELECT INTO Cria uma nova tabela com os resultados da consulta. Diferente de um SELECT convencional, os resultados no so mostrados e sim inseridos na nova tabela. Exemplo: Criar uma tabela idntica tabela colaboradores, apenas quando
uf=SP:
SELECT*INTOcolaboradores_spFROMcolaboradoresWHEREuf='SP';
Aps uma tabela ser criada no PostgreSQL, no se pode mais alterar a ordem das colunas com o comando ALTERTABLE. Uma soluo para isso usar o SELECTINTO para criar uma nova tabela, com os mesmos dados da antiga, apagar a tabela cuja ordem no interessante e renomear a nova tabela para o nome da antiga. Na tabela colaboradores , a coluna cargo vem antes de setor, ento vamos troc-las de lugar:
SELECTmat_id,nome,snome,setor,cargo,uf,cidade,salario,dt_admisINTOcolaboradores_tmpFROM colaboradores;Novatabelacriada,naordemdecolunasdesejada; DROPTABLEcolaboradores;Tabelaantigaapagada; ALTERTABLEcolaboradores_tmpRENAMETOcolaboradores;Tabelanovarenomeada;
17.13 - CREATE TABLE AS Assim como SELECTINTO, tambm cria uma nova tabela com os resultados da consulta. Exemplo:
CREATETABLEdiretoresASSELECT*FROMcolaboradoresWHEREcargo='Diretor'; Novatabeladiretorescriadaapartirdeumaconsultaemcolaboradores;
17.14 - Selecionando dados de diferentes tabelas 17.14.1 - Aliases de Tabelas (apelidos de tabelas) Ao usarmos mais de uma tabela em uma consulta pode haver nomes de colunas idnticos nas tabelas envolvidas. Tal inconveniente pode ser resolvido colocando o nome da tabela e um ponto antes do nome da coluna. No entanto, muitas vezes isso pode se tornar um tanto cansativo devido ao fato de se digitar coisas a mais.
48
Uma sada pra isso usar um apelido para a tabela antes do ponto e do nome da coluna e dizer a quem pertence o apelido: Pose se usar AS:
SELECTc.nome||''||c.snomeAS"Funcionriocontemplado",p.premio_idAS"Prmio" FROMcolaboradoresASc,funcionarios_premiadosASp WHEREc.mat_id=p.mat_id;
Ou omitir AS:
SELECTc.nome||''||c.snomeAS"Funcionriocontemplado",p.premio_idAS"Prmio" FROMcolaboradoresc,funcionarios_premiadosp WHEREc.mat_id=p.mat_id;
17.14.2 - Joins Em portugus, juno, uma JOIN rene registros de tabelas diferentes em uma mesma consulta. 17.14.2.1 - Tipos de Joins 17.14.2.1.1 - CROSS JOIN (Juno cruzada) Retorna uma conjunto de informaes o qual resultante de todas as combinaes possveis entre os registros das tabelas envolvidas:
SELECTc.nome||''||c.snomeAS"Funcionriocontemplado",p.premio_idAS"Prmio" FROMcolaboradoresc CROSSJOINfuncionarios_premiadosp;
17.14.2.1.2 - NATURAL JOIN (Juno natural) Faz a juno tomando como base as colunas de mesmo nome nas tabelas envolvidas:
SELECTc.nome||''||c.snomeAS"Funcionriocontemplado",p.premio_idAS"Prmio" FROMfuncionarios_premiadosp NATURALJOINcolaboradoresc;
17.14.2.1.3 - INNER JOIN (Juno interna) Retorna as informaes apenas de acordo com as linhas que obedeam as definies de relacionamento. Existe uma ligao lgica para se fazer a juno:
SELECTc.nome||''||c.snomeAS"Funcionriocontemplado",p.premio_idAS"Prmio" FROMfuncionarios_premiadosp INNERJOINcolaboradorescONc.mat_id=p.mat_id; SELECTc.nome||''||c.snomeAS"Funcionriocontemplado",p.premio_idAS"Prmio" FROMfuncionarios_premiadosp INNERJOINcolaboradorescUSING(mat_id);
49
17.14.2.1.4 - OUTER JOIN (Juno externa) Assim como na INNERJOIN, existe uma ligao lgica, mas no retorna apenas as informaes que satisfaam a regra da juno. OUTERJOINs podem ser dos tipos: LEFT OUTER JOIN: retorna todos os registros da tabela esquerda; RIGHT OUTER JOIN: retorna todos os registros da tabela direita; FULL OUTER JOIN: retorna todos os registros de ambos os lados. Obs.: de uso opcional a palavra OUTER.
/*LEFTOUTERJOIN*/; SELECTc.nome||''||c.snomeAS"Funcionriocontemplado",p.premio_idAS"Prmio" FROMfuncionarios_premiadosp LEFTOUTERJOINcolaboradorescUSING(mat_id); SELECTc.nome||''||c.snomeAS"Funcionriocontemplado",p.premio_idAS"Prmio" FROMfuncionarios_premiadosp LEFTOUTERJOINcolaboradorescONc.mat_id=p.mat_id; /*RIGHTOUTERJOIN*/; SELECTc.nome||''||c.snomeAS"Funcionriocontemplado",p.premio_idAS"Prmio" FROMfuncionarios_premiadosp RIGHTOUTERJOINcolaboradorescUSING(mat_id); SELECTc.nome||''||c.snomeAS"Funcionriocontemplado",p.premio_idAS"Prmio" FROMfuncionarios_premiadosp RIGHTOUTERJOINcolaboradorescONc.mat_id=p.mat_id; /*FULLOUTERJOIN*/; SELECTc.nome||''||c.snomeAS"Funcionriocontemplado",p.premio_idAS"Prmio" FROMfuncionarios_premiadosp FULLOUTERJOINcolaboradorescUSING(mat_id); SELECTc.nome||''||c.snomeAS"Funcionriocontemplado",p.premio_idAS"Prmio" FROMfuncionarios_premiadosp FULLOUTERJOINcolaboradorescONc.mat_id=p.mat_id;
17.14.2.1.5 - SELF JOIN Nem sempre JOINs so usadas para unir dados de duas ou mais tabelas. H um caso especial que se faz uma auto juno. Ou seja, feita uma juno de uma tabela consigo mesma. Para evitar conflitos, usa-se aliases. Exemplo: Descobrir o nome e sobrenome do chefe direto de cada funcionrio:
SELECTc2.nome||''||c2.snomeAS"Colaborador",c1.nome||''||c1.snomeAS"ChefeDireto" FROMcolaboradoresASc1 JOINcolaboradoresc2ON(c1.mat_id=c2.chefe_direto);
50
17.14.3 - Sub-consultas Tambm conhecidas como subquerries, so SELECTs embutidos dentro de outro SELECT tm por finalidade flexibilizar consultas. Esse recurso est disponvel no PostgreSQL desde a verso 6.3. 17.14.3.1 - Sub-consulta no WHERE
SELECTnome,snome,salarioFROMcolaboradores WHERE salario=(SELECTmin(salario)FROMcolaboradores);
17.14.3.2 - Sub-consulta no SELECT

SELECTnome,snome,salario(SELECTsalario*0.06)AS"salariobruto" FROMcolaboradoresWHEREsalario<3000;
17.14.3.3 - Sub-consulta no FROM Subconsultas no FROM requerem um alias:

SELECT*FROM(SELECT*FROMcolaboradoresWHEREcidade='SoPaulo')AScolab_cid_sp;
17.14.3.4 - EXISTS Se a sub-consulta que ele antecede retornar pelo menos uma linha seu resultado true: Exemplos: Forando um retorno verdadeiro:
SELECTnome,snome,salarioFROMcolaboradores WHEREEXISTS (SELECT*FROMcolaboradoresWHEREsalario<9000); Forandoumretornofalso: SELECTnome,snome,salarioFROMcolaboradores WHEREEXISTS (SELECT*FROMcolaboradoresWHEREsalario=9000);
17.14.3.5 - IN e NOT IN Retorna verdadeiro para os valores que casem com o requisito. Exemplo: Mostrar todos os colaboradores que no tm o salrio mximo:
SELECTnome,snome,salarioFROMcolaboradores WHEREsalarioNOTIN (SELECTmax(salario)FROMcolaboradores);
51
17.14.3.6 - ANY ou SOME Tanto faz escrever um ou outro, pois o efeito o mesmo. Retorna verdadeiro para qualquer valor da sub-consulta que satisfaa a condio: Exemplo: Retornar apenas os salrios que estiverem entre R$ 2000,00 e R$ 5000,00:
SELECTnome,snome,salarioFROMcolaboradores WHEREsalario=SOME (SELECTsalarioFROMcolaboradoresWHEREsalarioBETWEEN2000AND5000);
17.14.3.7 - ALL Retorna somente se todos valores casarem com a sub-consulta. Exemplo: Retornar todos os salrios que estiverem fora da faixa R$ 2000,00 a R$ 5000,00:
SELECTnome,snome,salarioFROMcolaboradores WHEREsalario!=ALL (SELECTsalarioFROMcolaboradoresWHEREsalarioBETWEEN2000AND5000);
52
18 - VIEW
View (ou viso) uma consulta armazenada, a qual acessada como uma tabela virtual. Usa-se vises para facilitar o gerenciamento e deixar o design do banco de dados com um aspecto melhor e assim encapsulando os detalhes estruturais de tabelas. Como o prprio nome diz, uma viso apenas exibe informaes, nela no so inseridos dados. Pode-se inclusive construir views baseando-se em outras views. Exemplo:
CREATEORREPLACEVIEWvw_testeAS SELECT*FROMcolaboradoresWHEREuf='SP'ANDnome~*'^c';
53
19 - Esquemas (Schemas)
uma forma de organizar objetos dentro de um banco de dados, permitindo um mesmo tipo de objeto seja criado mais de uma vez com nome igual, mas em esquemas diferentes. Por padro, quando criamos objetos, os mesmos pertencem ao esquema pblico (public). Sendo assim quando fazemos uma consulta, no precisamos especificar o esquema:
Isto tambm produz o mesmo resultado at ento:

SELECT*FROMpublic.colaboradores; esquema.nome_do_objeto
Para um melhor entendimento, criaremos um esquema chamado paralelo:

CREATESCHEMAparalelo;
Criando um objeto dentro do novo esquema:

CREATETABLEparalelo.tabela_teste( campo1int, campo2int ); CREATEtipo_de_objetonome_do_esquema.nome_do_objeto;
Acessando dados de uma tabela criada em um determinado esquema:

SELECT*FROMparalelo.tabela_teste;
Listando todas as tabelas dentro do esquema paralelo:

\dtparalelo.*
Obs.: Isso vale pra outros tipos de objetos, basta trocar o t pela letra correspondente ao tipo de objeto, conforme pode ser conferido na ajuda de comandos internos do psql (\?). Alterando o esquema de uma tabela:
ALTERTABLEcolaboradoresSETschemaparalelo;
Como a tabela colaboradores fazia parte do esquema pblico, o mesmo no precisou ser mencionado, mas para faz-la voltar para o esquema pblico ser preciso mencionar o esquema:
ALTERTABLEparalelo.colaboradoresSETschemapublic;
54
20 - Funes
O PostgreSQL fornece um grande nmero de funes nativas para vrias finalidades, as quais so executadas dentro de um comando SQL. 20.1 - Funes Matemticas So funes cujo objetivo facilitar determinados tipos de clculos.
Funo pow(b,e) log(x) mod(x,y) sqrt(x) ceil(x) floor(x) round(x) pi()
Descrio Potncia: Eleva um nmero de uma base (b) a um expoente (e) Logaritmo de x Resto de diviso: Retorna o resto da diviso de x por y Raiz quadrada de x Arredondamento para cima de x Arredondamento para baixo de x Arredondamento padro de x Pi
Exemplo SELECT pow(2,3); SELECT log(100); SELECT mod(19,5); SELECT sqrt(49); SELECT ceil(9.1); SELECT floor(9.999); SELECT round(9.5); SELECT pi();
Resultado 8 2 4 7 10 9 10 3.14159265358979
20.2 - Funes de Data e Hora

Funo current_date current_time current_timestamp to_char(dt,mask) Data atual Hora atual Data e hora Converte a data de acordo com a mscara definida Descrio Exemplo SELECT current_date + 10; /* Daqui a 10 dias*/ SELECT current_time; SELECT current_timestamp; ou SELECT now(); SELECT to_char(now(),'"Data: " DD/MM/YYYY "Hora:" HH24:MI:SS');
20.2.1 - extract(...) Tem como objetivo pegar um dado de current_timestamp tais como:
Descrio Dia Ms Ano Hora Minuto Segundos Dcada Dia da semana Dia do ano poca Exemplo SELECT extract(day from now()); SELECT extract(month from now()); SELECT extract(year from now()); SELECT extract(hour from now()); SELECT extract(minute from now()); SELECT extract(secondfrom now()); SELECT extract(decade from now()); SELECT extract(dow from now()); SELECT extract(doy from now()); SELECT extract(epoch from now());
55
20.3 - Funes de Strings

Funo trim(string,'x') ltrim(string,'x') rtrim(string,'x') lower(string) upper(string) initcap(string) length(string) substr(string,n,x) translate(string,o,n) strpos(string,c) lpad(string,x,c) Descrio Retira o caractere x da stringsel Retira o caractere x da string esquerda Retira o caractere x da string direita Exibe a string em letras minsculas Exibe a string em letras maisculas Exibe a string com a primeira letra maiscula Informa a quantidade de caracteres da string Retorna uma sub-string da posio n de comprimento x Substitui os caracteres originais (o) para os novos (n), de acordo com a posio Informa a posio da primeira ocorrncia do caractere c na string Exemplo SELECT trim('xxxxABCxxxx','x'); SELECT ltrim('xxxxABCxxxx','x'); SELECT rtrim('xxxxABCxxxx','x'); SELECT lower('tEsTe'); SELECT upper('tEsTe'); SELECT initcap('tEsTe'); SELECT length('tEsTe'); SELECT substr('tEsTe',1,3); SELECT translate('hacker','ae','43'); SELECT strpos('goiaba','a');
Define o tamanho (x) mximo que a string ser exibida e preenche com o caractere c esquerda quando for SELECT lpad('Linux rulz!!',20,'-'); menor que x. Define o tamanho (x) mximo que a string ser exibida e preenche com o caractere c direita quando for SELECT rpad('Linux rulz!!',20,'-'); menor que x. Retorna o campo n tomando como referncia o delimitador d SELECT split_part('Steve Harris',' ',2) AS "Sobrenome";
rpad(string,x,c) split_part(string,d,n)
20.3.1 - to_char() Essa funo to til, merece at tratamento especial para falar dela. Como entrada pode-se colocar nmeros ou datas e de acordo com a mscara inserida obtm-se interessantes resultados. Para ver a ajuda de sintaxe da funo digite: \df to_char Sendo que do que foi apresentado como segundo argumento tudo text, uma mscara para modelar a converso desejada. Exemplos: Converter o nmero 2009 para algarismos romanos:
SELECTto_char(2009,'RN');
Apresentar a data e hora atual no formato dd/mm/yyyy hh:mm:

SELECTto_char(now(),'dd/mm/yyyyhh:mm');
Determinar que um cdigo ser composto por 5 algarismos, preenchidos por zeros esquerda:
SELECTto_char(53,'00000');
At 9 (nove) algarismos + 2 (duas) casas decimais, preenchidos com zeros e separador de milhar:
SELECTto_char(29535.21,'000G000G000D00');
Nmero de at 9 (nove) algarismos + 2 (duas) casas decimais, no preenchidos com zeros e separador de milhar:
SELECTto_char(29535.21,'999G999G999D99');
56
20.4 - Funes de Sistema

Funo current_database() current_user version() current_schema() current_schemas(bool) current_setting(string) inet_server_addr() inet_server_port() Descrio Banco de dados em uso Informa qual o atual usurio Verso do PostgreSQL Esquema corrente Esquemas no caminho de procura incluindo, opcionalmente (true), os esquemas implcitos Informa uma configurao de sistema Retorna o endereo IP do servidor Retorna a porta do servidor PostgreSQL Exemplo SELECT current_database(); SELECT current_user; ou SELECT user; SELECT version(); SELECT current_schema(); SELECT current_schemas(true); SELECT current_setting('datestyle'); SELECT inet_server_addr(); SELECT inet_server_port();
20.5 - Funes de Agregao (ou de Grupos de Dados)

Funo avg(expresso) Descrio Mdia aritmtica dos valores de entrada
SELECTavg(salario)FROMcolaboradores; count(*) A quantidade de valores de entrada
SELECTcont(*)FROMcolaboradores; count(expresso) A quantidade de valores de entrada em que o valor da expresso no nulo
SELECTcount(setor)FROMcolaboradores; max(expresso) Retorna o valor mximo dentre todos os valores de entrada
SELECTnome,snome,salarioFROMcolaboradores WHERE salario=(SELECTmax(salario)FROMcolaboradores); min(expresso) Retorna o valor mnimo dentre todos os valores de entrada
SELECTnome,snome,salarioFROMcolaboradores WHERE salario=(SELECTmin(salario)FROMcolaboradores); sum(expresso) Informa a soma dos valores de entrada da expresso
SELECTsum(salario)FROMcolaboradores; stddev(expresso) Desvio padro
SELECTstddev(salario)FROMcolaboradores; variance(expresso) Varincia estatstica
SELECTvariance(salario)FROMcolaboradores;
57
20.6 - A Clusula GROUP BY
A clusula GROUP BY concentra em apenas uma linha todos os registros cujo valor o mesmo da coluna GROUP BY. importante lembrar que todas as colunas relacionadas no SELECT que no estejam nas funes de grupo devem estar tambm na clusula GROUP BY.
Exemplos: Selecionar quantos colaboradores h por UF:
SELECTuf,count(uf)FROMcolaboradoresGROUPBYuf;
Consultar quantos colaboradores ganham pelo menos R$ 2.500,00 agrupando pelo estado de origem:
SELECTuf,count(uf)FROMcolaboradoresWHEREsalario>=2500GROUPBYuf;
20.7 - A Clusula HAVING Tem por finalidade filtrar os dados agrupados impondo uma condio. Tal condio deve ter alguma das colunas do SELECT. Exemplo: Consultar quantos colaboradores ganham pelo menos R$ 2.500,00 agrupando pelo estado de origem, mas apenas quando o estado possuir mais que 3 (trs) colaboradores que atendam ao requisito:
SELECTuf,count(uf)FROMcolaboradoresWHEREsalario>=2500GROUPBYufHAVINGcount(uf)>3;
58
21 - INSERT insero de Dados

O comando INSERT cria novos registros em uma tabela. Sintaxe geral:
INSERTINTOtabela(coluna1,coluna2,...,colunaN)VALUES(valor1,valor2,...,valorN);
Como exemplo, vamos inserir um novo funcionrio na tabela colaboradores, mencionando todos os campos:
INSERTINTOcolaboradores (mat_id,nome,snome,cargo,setor,uf,cidade,salario,dt_admis,chefe_direto) VALUES ('00036','Teobaldo','Melo','Diretor','Financeiro',DEFAULT,'So Paulo',4523.75,'09/06/2009','00001');
Analisando o cdigo acima podemos notar que logo aps o nome da tabela abre-se parnteses e vem a sequncia de nomes de colunas separados por vrgulas e aps o nome da ltima h o fechamento de parnteses. A palavra chave VALUES anuncia os valores que sero inseridos de acordo com a posio do nome de coluna. Para valores de tipos no numricos, datas ou tempo usa-se aspas simples para delimitar. Tipos numricos, palavras chave, chamada de funes ou identificadores no so envolvidos por aspas simples. Um dos valores a ser inserido usa a palavra chave DEFAULT, cujo, na estrutura da tabela colaboradores foi declarado como valor padro igual a SP. Quando vai inserir valores para todos os campos de uma tabela, no preciso declarar os nomes das colunas, podendo assim ganhar agilidade. Ento o comando acima poderia ser feito da seguinte forma:
INSERTINTOcolaboradoresVALUES ('00036','Teobaldo','Melo','Diretor','Financeiro',DEFAULT,'So Paulo',4523.75,'09/06/2009','00001');
Adicionando apenas alguns valores:

INSERTINTOcolaboradores(mat_id,nome,snome,dt_admis) VALUES('00037','Hugo','Nbrega',now());
Foram usados apenas os campos referentes matrcula, ao nome, ao sobrenome e data de admisso do funcionrio inserido, os outros campos (os no declarados), ou ficaram nulos ou com algum valor padro. Nesse caso, para o campo dt_admis, usamos a funo now() que preencheu com a data atual. Vale lembrar que para colunas do tipo NOTNULL o preenchimento obrigatrio.
59
21.1 - Inserindo Registros com SELECT Mtodo similar ao SELECTINTO e CREATETABLEAS, uma forma de passarmos dados de uma tabela para outra. Para exemplo, vamos primeiro criar uma tabela temporria, com poucos campos. cuja finalidade ser conter apenas os funcionrios amazonenses:
CREATETEMPTABLE"colaboradores_AM"( mat_idCHAR(5)PRIMARYKEY, nomeVARCHAR(15), snomeVARCHAR(15) );
Inserindo os dados na nova tabela atravs de uma consulta filtrada em colaboradores:

INSERTINTO"colaboradores_AM" SELECTmat_id,nome,snomeFROMcolaboradoresWHEREuf='AM';
21.2 - INSERT Agrupado Se for necessrio inserir mais de um registro, no necessrio fazer vrios INSERTs. Pode ser feito separando cada conjunto de valores dos registros (delimitados por parnteses), separados por vrgulas e aps o ltimo ao invs da vrgula, ponto e vrgula:
INSERTINTOcolaboradores(mat_id,nome,snome)VALUES ('00038','teste','insert_1'), ('00039','tEsTe','insert_2'), ('00040','TEste','insert_3');
60
22 - Dollar Quoting
Dollar Quoting uma forma alternativa de inserir strings sem usar as aspas simpes e de escapar caracteres dando uma maior segurana para o banco de dados, um grande aliado contra um tipo de ataque conhecido como SQL Injection. O dollar quoting se aplica a strings, sendo que pode ser feito de duas formas:
$$suastring$$
ou
$marcador$suastring$marcador$
Sendo que marcador pode ser qualquer string que voc quiser escolher para denominar. Exemplo:
INSERTINTOtabela(campo_string)VALUES($dfghjkliu$testetesteteste$dfghjkliu$);
O valor inserido foi teste teste teste (sem as aspas), usamos como marcador $dfghjkliu$. O uso de dollar quoting nos permite inserir strings com caracteres especias, tais como a prpria aspas simples:
INSERTINTOtabela(campo_string)VALUES($outro_marcador$'teste'$outro_marcador$);
Nesse caso, o valor inserido foi:

'teste'
Sim! Com o uso de dollar quoting o insert considerou as aspas simples como um caracter comum. Obs.: Apesar dos exemplo dos exemplos terem sido com INSERT, poderiam ser de acordo com o contexto usando outros comandos DML (SELECT, UPDATE e DELETE).
61
22 - UPDATE Alterando Registros

O comando UPDATE utilizado para mudar registros de uma tabela. Sintaxe geral:
UPDATEtabelaSETcoluna=valorWHEREcondio;
Vamos usar o comando UPDATE para atualizar a tabela colaboradores, levando-se em conta que os trabalhadores que ganham menos ou igual a R$ 1.200,00 e so do estado de So Paulo receberam um aumento de 10% no salrio:
UPDATEcolaboradoresSETsalario=salario*1.1WHEREsalario<=1200ANDufILIKE'sp';
Nota-se que ao fazer uma consulta dos registros, aps a atualizao, os registros afetados saram de sua ordem original passando agora para o final de todos os registros. 23.1 - Atualizando Mais de Um Campo Podemos tambm fazer atualizao de registros referenciando mais de um campo. Vamos supor que por um motivo qualquer a unidade de Manaus teve que mudar para Belm:
UPDATEcolaboradoresSET(uf,cidade)=('PA','Belm')WHEREuf='AM';
Para voltar ao normal:

UPDATEcolaboradoresSET(uf,cidade)=('AM','Manaus')WHEREuf='PA';
Poderia ser feito tambm da seguinte forma:

UPDATEcolaboradoresSETuf='RN',cidade='Natal'WHERE(uf,cidade)=('SP','SoPaulo');
Voltando ao que era antes:

UPDATEcolaboradoresSETuf='SP',cidade='SoPaulo'WHEREuf='RN'ANDcidade='Natal';
23.2 - Atualizando com Sub-consulta Todos os trabalhadores que ganham menos ou igual a R$ 1.050,00 tero seus salrios reajustados para o valor igual mdia daqueles que ganham menos que R$ 2.000,00:
UPDATEcolaboradoresSETsalario= (SELECTavg(salario)FROMcolaboradoresWHEREsalario<2000) WHEREsalario<=1050;
62
24 - DELETE Removendo Registros

Para apagar linhas de uma tabela usa-se o comando DELETE. Sintaxe geral:
DELETEFROMtabelaWHEREcolunacondio;
Apagando todos os registros cuja coluna cargo tem valor nulo:

DELETEFROMcolaboradoresWHEREcargoISNULL;
Assim como em outros exemplos, pode-se usar sub-consultas para tambm deletar dados.
63
25 - TRUNCATE Redefinindo uma Tabela Como Vazia

O comando TRUNCATE apaga todos os registros de uma tabela, portanto deve ser usado com cautela. Apesar de apagar todos os dados de uma tabela um comando de definio e no de manipulao, ou seja: DDL. Sintaxe geral:
TRUNCATEtabela[CASCADE];
Vamos tentar fazer o comando TRUNCATE na tabela colaboradores:

TRUNCATEcolaboradores;
Erro! No foi possvel, pois a mesma referenciada por outra tabela, sendo ento necessrio fazer a remoo em cascata, usando o parmetro CASCADE:
TRUNCATEcolaboradoresCASCADE;
Todos os dados foram perdidos! Mas para continuarmos o curso simplesmente copie a parte de INSERTS do arquivo curso.sql e cole no prompt do psql.
64
26 - COPY
O comando COPY copia registros entre um arquivo e uma tabela, ou de uma tabela para STDOUT (standard output sada padro) ou de STDIN (standard input entrada padro) para uma tabela. Para se ter uma idia de como funciona o comando
COPYcolaboradoresTOstdout; COPY,
vamos primeiro copiar os dados para a tela:
Podemos observar que o resultado obtido similar a mandar selecionar todos os dados. Cada campo separado por espao, mas podemos usar outro delimitador como a vrgula, por exemplo:
COPYcolaboradoresTOstdoutDELIMITER',';
O delimitador determinado pelo caractere entre as aspas simples. Para prosseguirmos nossos exemplos com COPY vamos criar uma tabela temporria contendo apenas os funcionrios de So Paulo:
CREATETEMPTABLEcolab_spASSELECT*FROMcolaboradoresWHEREuf='SP';
Com o comando COPY enviaremos os dados dessa tabela para o arquivo /tmp/backup_sp.txt, usando como delimitador o caractere pipe (|):
COPYcolab_spTO'/tmp/backup_sp.txt'DELIMITER'|';
Usando o comando TRUNCATE para redefinir a tabela colab_sp como uma tabela vazia:
TRUNCATEcolab_sp;
E recuperamos os dados que nela estavam usando COPY:

COPYcolab_spFROM'/tmp/backup_sp.txt'DELIMITER'|';
26.1 - Formato CSV CSV significa Comma Separeted Values (Valores Separados por Vrgula), que no caso so os valores respectivos aos campos de uma tabela. Para gerarmos um CSV:
COPYcolab_spTO'/tmp/backup_sp.csv'CSV;
Com o parmetro CSV o arquivo resultante gerado automaticamente com vrgulas como delimitadores de colunas, mesmo as que esto nulas.
65
Para recuperar dados de um arquivo CSV para uma tabela:

COPYcolab_spFROM'/tmp/backup_sp.csv'CSV;
26.2 - Inserindo Dados com o COPY pelo Teclado Uma alternativa para inserir dados diferente do comando INSERT:
COPYcolab_spFROMstdinCSV;
Aparecer uma mensagem na tela: Informe os dados a serem copiados seguido pelo caractere de nova linha. Finalize com uma barra invertida e um ponto na linha.
>>
Os dois sinais maior que indicam a linha corrente para se inserir dados. No exemplo foi determinado o formato CSV e ento como exemplo digitaremos:
00032,Paula,Franco,Programador,TI,SP,Jundia,2100,20070930,00003
<ENTER>
00030,Martina,Santos,An.deSistemas,TI,SP,Jundia,4200,20070930,00003
<ENTER>
\.
66
27 - SEQUENCE Sequncia
Uma sequncia usada para determinar valores automaticamente para um campo. Vejamos como se cria uma:
CREATESEQUENCEnome_da_sequencia [INCREMENTincremento] [MINVALUEvalor_mnimo|NOMINVALUE] [MAXVALUEvalor_mximo|NOMAXVALUE] [START[WITH]incio] [CACHEcache] [[NO]CYCLE];
Os parmetros: INCREMENT: Valor de incremento. MINVALUE: Valor mnimo. NO MINVALUE: Neste caso usado os valores mnimos padres, sendo que 1 e -2 63-1 para seqncias ascendentes e descendentes, respectivamente. MAXVALUE: Valor mximo. NO MAXVALUE: Ser usado os valores mximos padres: 263-1 e -1 para seqncias ascendentes e descendentes, respectivamente. START [ WITH ]: Valor inicial. CACHE: Quantos nmeros da seqncia devem ser pr-alocados e armazenados em memria para acesso mais rpido. O valor mnimo 1 (somente um valor gerado de cada vez, ou seja, sem cache), e este tambm o valor padro. CYCLE: Faz com que a seqncia recomece quando for atingido o valor_mximo ou o valor_mnimo por uma seqncia ascendente ou descendente, respectivamente. Se o limite for atingido, o prximo nmero gerado ser o valor_mnimo ou o valor_mximo, respectivamente. NO CYCLE: Seu efeito se d a toda chamada a nextval aps a seqncia ter atingido seu valor mximo retornar um erro. Se no for especificado nem CYCLE nem NO CYCLE, NO CYCLE o padro. Exemplo: Criar uma sequencia que se d de 5 em 5, valor mnimo 15, valor mximo 500, com 20 nmeros da sequncia pr alocados na memria e sem ciclo:
CREATESEQUENCEsq_teste INCREMENT5 MINVALUE15 MAXVALUE500 CACHE20 NOCYCLE;
67
27.1 - Funes de Manipulao de Sequncia nextval('sequencia'): Prximo valor e incrementa. currval('sequencia'): Valor atual. Quando a sequncia ainda no foi usada retornar erro. setval('sequencia',valor): Determina um novo valor atual. Damos incio sequncia manualmente:
SELECTnextval('sq_teste');
(Repita trs vezes) Observamos seu valor corrente:

SELECTcurrval('sq_teste');
Mudamos seu valor para 20:

SELECTsetval('sq_teste',20);
27.2 - Usando Sequencias em Tabelas Dentre os tipos de dados que foram vistos, o serial nada mais do que uma sequncia criada na hora, de acordo com o nome da tabela. Vejamos este exemplo:
CREATETEMPTABLEtb_teste( codserial, nomeVARCHAR(15) );
exibida uma nota:

CREATETABLEcriarsequnciaimplcita"tb_teste_cod_seq"paracolunaserial"tb_teste.cod"
Vamos fazer novamente a tabela, mas com a sequncia sq_teste:

CREATETABLEtb_teste( codintDEFAULTnextval('sq_teste'), nomeVARCHAR(15) );
Testando:
INSERTINTOtb_teste(nome)VALUES('nome1'),('nome2'),('nome3'),('nome4'); SELECT*FROMtb_teste;
68
Como pde ser observado, o prximo valor de sq_teste foi tomado como um valor padro para o campo cod. Um campo auto incremental.
27.3 - Alterando uma Sequncia Usando o comando \h ALTER SEQUENCE podemos ver o que pode ser mudado na sequncia desejada, quase igual a criar uma. Como exemplo vamos voltar o valor para 15:
ALTERSEQUENCEsq_testeRESTARTWITH15;
De agora em diante os valores sero a partir do 15 em sq_teste. 27.4 - Deletando uma Sequncia Podemos apagar uma ou mais sequncias usando a seguinte sintaxe:
DROPSEQUENCE[IFEXISTS]nome[,...][CASCADE|RESTRICT]
Exemplo:
DROPSEQUENCEsq_teste;
Erro! Pois sq_teste est vinculada tabela tb_teste, ento devemos fazer a remoo em cascata:
DROPSEQUENCEsq_testeCASCADE;
O valor padro da tabela tb_teste foi removido.
69
28 - INDEX - ndice
Um ndice (INDEX) um recurso que agiliza buscas de informaes em tabelas. Indexamos campos usados como critrios de filtragem numa consulta (clusula WHERE, por exemplo) e aqueles cujos valores so mais restritivos comparados a outros valores da tabela. Seu funcionamento consiste em criar ponteiros para dados gravados em campos especficos. Quando no existe ndice num campo usado como critrio de filtragem, feita uma varredura em toda a tabela, de maneira que haver execues de entrada e sada (I/O) de disco desnecessrias, alm de tambm desperdiar processamento. Obs.: Ao se criar chaves primrias, automaticamente um ndice criado. Sintaxe geral:
CREATE[UNIQUE]INDEXnome_do_indexONtabela[USINGmtodo](campo);
O parmetro UNIQUE faz com que a coluna indexada s aceite valores nicos. No entanto, se a tabela j estiver com valores duplicados na coluna desejada no ser possvel criar o ndice como UNIQUE. Exemplo:
CREATEINDEXidx_colab_nomeONcolaboradoresUSINGBTREE(nome);
28.1 - Mtodos de Indexao O PostgreSQL fornece quatro tipos de ndices: BTREE (padro), GIST, GIN e HASH. Cada tipo de ndice usa um algoritmo diferente, cada qual se encaixa melhor em algum tipo de consulta.
28.1.1 - BTREE Podem lidar com consultas de faixas ou igualdades em dados que podem ser organizados ordenadamente. Este tipo de ndice deve ser considerado pelo query planner quando a coluna indexada envolve comparaes usando algum desses operadores: <, <=, =, => e >. 28.1.2 - GIST
No so tipos singulares de ndices, mas sim dentro de uma infra-estrutura quem muitas diferentes
70
estratgias de indexao podem ser implementadas. Assim, os operadores com os quais um ndice GIST pode ser usado varia dependendo da estratgia de indexao (a classe de operador). Por exemplo, a distribuio padro do PostgreSQL inclui classes de operadores GIST para vrios tipos de dados geomtricos bi-dimensionais, que suporta consultas usando estes operadores: <<, &<, &>, >>, <<|, &<| , | &>, |>> , @> , <@ , ~= e &&.
28.1.3 - GIN So ndices invertidos que podem lidar com valores que contenham mais de uma chave,arrays por exemplo. Como o GIST, pode suportar muitas estratgias diferentes de indexao definidas por usurios e os operadores particulares com que um ndice GIN pode ser usado dependendo da estratgia de indexao. O PostgreSQL por padro inclui classes de operadores GIN para arrays uni-dimensionais, que suportam consultas usando estes operadores: <@, @>, =, &&. 28.1.4 - HASH Lidam apenas com comparaes simples. O query planner considerar o ndice hash sempre que uma coluna indexada estiver envolvida em uma comparao com o operador =. ndices do tipo hash no suportam consultas ISNULL.
Obs.: Testes demonstram que ndices hash do PostgreSQL no tem performance melhor que ndices do tipo BTREE e o tamanho e tempo para construir bem pior. O uso do ndice hash desencorajado.
28.2 - ndices Compostos

So aqueles que contm em sua composio mais de um campo, sendo que para um melhor desempenho deve-se declarar na criao do ndice os campos com valores mais distribudos no incio. Exemplo: CREATE INDEX nome_do_index ON nome_da_tabela (campoX,campoY,campoZ);
28.3 - ndices Parciais

Quando se tem uma tabela com muitos registros, um ndice comum pode no oferecer um desempenho no to bom. Felizmente o Postgres tem um recurso que permite que sejam criados ndices de acordo com uma condio estabelecida na sua criao. Exemplo:
Criaodatabeladeteste CREATETABLEtb_teste_index( campo1INT ); Inserode2milhesderegistros INSERTINTOtb_teste_indexVALUES(generate_series(1,2000000)); Anlisesemndicesdevaloresmltiplosde19(semndice) EXPLAINANALYZESELECT*FROMtb_teste_indexWHEREcampo1%19=0; QUERYPLAN SeqScanontb_teste_index(cost=0.00..40710.00rows=10620width=4)(actualtime=0.132..1009.375 71
rows=105263loops=1) Filter:((campo1%19)=0) Totalruntime:1144.964ms (3rows) Criaodendicetotal CREATEINDEXidx_teste_index_totalONtb_teste_index(campo1); Criaodendiceparcialmltiplosde19 CREATEINDEXidx_teste_index_19ONtb_teste_index(campo1)WHEREcampo1%19=0;
Anlisesemndicesdevaloresmltiplosde19(COMNDICE) EXPLAINANALYZESELECT*FROMtb_teste_indexWHEREcampo1%19=0; QUERYPLAN IndexScanusingidx_teste_index_19ontb_teste_index(cost=0.00..1892.58rows=10000width=4) (actualtime=0.286..292.026rows=105263loops=1) Totalruntime:430.361ms (2rows)
Pelo exemplo acima pudemos constatar o que foi dito na teoria; antes da criao dos ndices a busca foi sequencial, demorando 1144.964 ms. Aps a criao dos ndices, o planejador de consultas j podia contar com eles, optando por usar o ndice com maior restrio de valores levando 430.361 ms, usufruindo de uma busca agora indexada por um ndice parcial.
28.4 - Antes de Excluir um ndice

No remova um ndice de seu banco sem antes saber o quo til ele .
SELECTindexrelname,relname,idx_scan,idx_tup_read,idx_tup_fetchFROMpg_stat_user_indexes;
Sendo que cada campo significa:
indexrelname: Nome do ndice; relname: Nome da tabela qual o ndice pertence; idx_scan: Quantas vezes o ndice foi usado; idx_tup_read: Quantas tuplas o ndice leu; idx_tup_fetch: Quantas tuplas o ndice recuperou.
Ou seja, se um ndice j existe h um certo tempo e no tem sido usado, ser necessrio replanejar o mesmo, devido sua inutilidade.
28.5 - Excluindo um ndice

Sntaxe;
DROPINDEX[IFEXISTS]nome[,...][CASCADE|RESTRICT]
Exemplo;
DROPINDEXidx_colab_nome;
72
28.6 - Reconstruo de ndices REINDEX

O comando REINDEX faz a reconstruo de um ndice utilizando os dados guardados na tabela do mesmo e substitui a cpia antiga do ndice. O comando REINDEX utilizado nas seguintes situaes: ndice corrompido e no contm mais dados vlidos. Embora teoricamente isso nunca deva acontecer, na prtica os ndices podem se corromper devido a erros de programao ou falhas de hardware. O comando REINDEX fornece um mtodo de recuperao. ndice dilatado. Contm muitas pginas vazias ou quase vazias. Tal situao pode acontecer com ndices BTree sob usos fora do comum. O comando REINDEX fornece uma maneira para diminuir o consumo de espao do ndice atravs da escrita de uma nova verso sem as pginas mortas. Sintaxe:
REINDEX{INDEX|TABLE|DATABASE|SYSTEM}nome
INDEX: ndice especfico; TABLE: Todos os ndices da tabela especfica; DATABASE: Todos os ndices do banco de dados especfico; SYSTEM: Todos os ndices em todos os catlogos no mbito do atual banco de dados; nome: Identificador de INDEX, TABLE, DATABASE ou SYSTEM Exemplo:
REINDEXTABLEcolaboradores;
O comando acima reindexou todos ndices da tabela colaboradores.
28.7 - CLUSTER
O comando CLUSTER agrupa uma tabela de acordo com um ndice de modo a aumentar a performance no banco de dados. A tabela fisicamente reordenada baseando-se na informao do ndice. O agrupamento feito somente uma vez: aps ser atualizada , as atualizaes feitas nas linhas da tabela no seguiro o agrupamento, ou seja, no ser feita nenhuma tentativa para armazenar as linhas novas ou atualizadas na ordem do ndice. Se for desejado, a tabela pode ser reagrupada periodicamente executando este comando novamente. Sintaxe:
CLUSTERnome_tabela[USINGnome_ndice]
Exemplo:
CLUSTERcolaboradoresUSINGcolaboradores_pkey;
73
29 - DOMAIN (Domno)
Domnio um tipo de dado personalizado em que se pode definir como os dados sero inseridos de acordo com restries definidas opcionalmente. Sintaxe:
CREATEDOMAINnome[AS]tipo_dado [DEFAULTexpresso] [restrio[...]]
onde restrio :
[CONSTRAINTnome_restrio] {NOTNULL|NULL|CHECK(expresso)}
Exemplo: Criao de um domnio, cuja finalidade validar CEPs no formato #####-###:

CREATEDOMAINdom_cepASchar(9) CONSTRAINTchk_cep CHECK(VALUE~E'^\\d{5}\\d{3}$');
O domnio criado de nome dom_cep do tipo char(9), pois recebe 5 (cinco) dgitos, um - (hfen) e por ltimo mais 3 (trs) dgitos. Possui uma restrio denominada chk_cep, a qual verifica se o valor inserido estar de acordo com a expresso regular se o valor inserido est no valor determinado (5 dgitos + - + 3 dgitos). Criao de uma tabela que usar o domnio criado como tipo de dado para uma coluna:
CREATETEMPTABLEendereco( cepdom_cep, logradourotext, numerovarchar(6), cidadevarchar(20), ufchar(2) );
Um exemplo de insero na tabela com o domno criado:

INSERTINTOenderecoVALUES('01001000','Pa.daS','s/n','SoPaulo','SP');
74
30 - Transaes / Controle de Simultaneidade

Transao uma forma de se executar uma sequncia de comandos indivisivelmente para fins de alteraes, inseres ou remoes de registros. 30.1 - MVCC Multi Version Concurrency Control (Controle de Concorrncia Multi Verso) De maneira diferente de outros SGBDs tradicionais,que usam bloqueios para controlar a simultaneidade, o PostgreSQL mantm a consistncia dos dados utilizando o modelo multiverso (MVCC: Multi Version Concurrency Control. Significa que,ao consultar o banco de dados, cada transao enxerga um instantneo (snapshot) dos dados (uma verso do banco de dados) como esses eram antes, sem considerar o atual estado dos dados subjacentes. Esse modelo evita que a transao enxergue dados inconsistentes, o que poderia ser originado por atualizaes feitas por transaes simultneas nos mesmos registros, fornecendo um isolamento da transao para cada sesso. A principal vantagem de utilizar o modelo MVCC em bloqueios pelo fato de os bloqueios obtidos para consultar os dados (leitura) no entram em conflito com os bloqueios de escrita, ento, a leitura nunca bloqueia a escrita e a escrita nunca bloqueia a leitura. 30.2 - Utilidade de uma transao Por padro, o PostgreSQL auto commit, que significa efetivar as alteraes nos bancos de dados de forma automtica, pois cada comando executado e logo aps a alterao feita. No entanto, h casos que exige-se uma maior segurana, como por exemplo, contas bancrias. Imagine uma transferncia de uma conta A para uma conta B de um valor qualquer. No banco de dados, a conta A ter de seu saldo subtrado o valor da transferncia e na conta B adicionado. Ou seja, na primeira operao retira-se de uma e na segunda acrescenta-se a outra. E se logo aps o valor ser subtrado de A houver algum problema? A conta B ficaria sem o valor acrescentado e A ficaria com um saldo menor sem a transferncia ter sido realmente feita... Seria um grande problema para a instituio financeira! Se no caso citado tivesse sido feito de forma a usar transao no haveria tal transtorno, pois numa transao ou todos comandos so devidamente efetivados ou no haver alterao alguma. Alteraes feitas durante uma transao s podem ser vistas por quem est fazendo, os outros usurios s podero ver depois da transao ser efetivada. Um banco de dados relacional deve ter um mecanismo eficaz para armazenar informaes que estejam de acordo com o conceito ACID. 30.3 - ACID Atomicidade: Relembrando as aulas de qumica, a palavra tomo, que vem do grego, significa
75
indivisvel. Quando se executa uma transao no h parcialidade. Ou todas alteraes so efetivadas ou nenhuma. Consistncia: Certifica que o banco de dados permanecer em um estado consistente antes do incio da transao e depois que a transao for finalizada (bem sucedida ou no). Isolamento: Cada transao desconhece outras transaes concorrentes no sistema. Durabilidade: Aps o trmino bem sucedido de uma transao no banco de dados, as mudanas persistem.
30.4 - Nveis de Isolamento O padro SQL define quatro nveis de isolamento de transao em termos de trs fenmenos que devem ser evitados entre transaes simultneas. Os fenmenos no desejados so: Dirty Read (Leitura Suja): Uma transao l dados gravados por outra transao concorrente no efetivada; Nonrepeatable Read (Leitura que no se repete): A transao rel dados que foram previamente lidos e descobre que os dados foram modificados por outra transao (efetivados desde a primeira leitura); Phantom Read (Leitura Fantasma): A transao executa novamente uma consulta e descobre que os registros mudaram devido a outra transao ter sido efetivada;
Dirty Read Possvel Impossvel Impossvel Impossvel Nonrepeatble Read Possvel Possvel Impossvel Impossvel Phantom Read Possvel Possvel Possvel Impossvel
Nvel de Isolamento Read Uncommited Read Commited (padro PostgreSQL) Repeatable Read Serializable
No PostgreSQL qualquer um dos nveis de isolamento padro pode ser escolhido, mas internamente s h dois nveis de isolamento distintos que so Read Commited e Serializable. Quando selecionado Read Uncommited na verdade obtm-se Read Commited e quando se escolhe Repeteatable Read obtm-se Serializable. Ento, o nvel de isolamento real pode ser mais estrito do que o escoolhido. O padro SQL permite que os quatro nveis de isolamento apenas definem quais fenmenos no podem acontecer, no definem quais fenmenos devem acontecer. Podemos definir o nvel de isolamento da transao usando o comando SET TRANSACTION. Sintaxe:
SETTRANSACTIONmodo_transao[,...] SETSESSIONCHARACTERISTICSASTRANSACTIONmodo_transao[,...]
onde modo_transao um dos:

ISOLATIONLEVEL{SERIALIZABLE|REPEATABLEREAD|READCOMMITTED|READUNCOMMITTED} READWRITE|READONLY
Exemplo: Definindo para a transaao:

SETTRANSACTIONISOLATIONLEVELSERIALIZABLE;
Verificando:
SHOWTRANSACTIONISOLATIONLEVEL;
76
Definindo para a sesso aberta:

SETSESSIONCHARACTERISTICSASTRANSACTIONISOLATIONLEVELREADCOMMITTED;
30.4.1 - Nvel de Isolamento Read Commited (L Efetivado) o nvel de isolamento padro do PostgreSQL. Em uma transao sob este nvel, o comando SELECT enxerga apenas os dados efetivados antes da consulta e nunca dados no efetivados ou as alteraes efetivadas por transaes simultneas durante a execuo da consulta (porm, o SELECT enxerga os efeitos das atualizaes anteriores executadas dentro da sua prpria transao, mesmo que ainda no tenham sido efetivadas). O comando SELECT, na verdade, enxerga um instantneo do banco de dados, como era no momento em que a consulta comeou. Deve ser notado que dois comandos SELECT sucessivos podem enxergar dados diferentes mesmo dentro da mesma transao, se outras transaes efetivarem alteraes durante a execuo do primeiro SELECT. 30.4.2 - Nvel de Isolamento Serializable (Serializvel) o nvel de isolamento mais rigoroso, o qual emula a execuo em srie das transaes, como se fossem executadas uma aps a outra, ao invs de ao mesmo tempo. Porm, os aplicativos que utilizemeste nvel de isolamento devem estar preparados para tentar executar as transaes novamente, devido falhas de serializao. Em uma transao contida nesse nvel de isolamento, o comando SELECT enxerga apenas os dados efetivados antes da transao comear e nunca os no efetivados ou alteraes efetivadas durante a execuo da transao por transaes simultneas (Porm, o SELECT enxerga os efeitos das atualizaes anteriores executadas dentro da sua prpria transao, mesmo que ainda no tenham sido efetivadas). Se difere do Read pelo fato de o SELECT enxergar um instantneo do momento de incio da transao, no do momento de incio do comando corrente dentro da transao. Portanto, SELECTs sucessivos dentro de uma mesma transao sempre enxergam os mesmos dados. 30.5 - Fazendo uma transao Uma transao deve comear com o comando BEGIN: Sintaxe:
BEGIN[WORK|TRANSACTION][modo_transao[,...]]
Onde modo_transao um dos:

ISOLATIONLEVEL{SERIALIZABLE|REPEATABLEREAD|READCOMMITTED|READUNCOMMITTED} READWRITE|READONLY
As palavras WORK e TRANSACTION podem ser omitidas. Aps o BEGIN, que declara que a transao foi iniciada tem se a sequencia de comandos desejada:
comando_SQL_1; comando_SQL_n;
E depois dos comandos, se por algum motivo quiser desfaz-los usa-se o comando ROLLBACK:
ROLLBACK;
77
Nos exemplos a seguir sobre transao, mesmo que os comandos sejam copiados e colados, devem ser feitos um por um de modo que se observe seu comportamento e suas relativas alteraes. tambm aconselhvel abrir uma outra sesso no mesmo banco de dados para observar o que acontece quando sesses diferentes tentam alterar o mesmo registro. Exemplo:
BEGIN;Inciodatransao; DELETEFROMcolaboradoresWHEREmat_id='00035';Remoodeumregistrodentroda transao; SELECT*FROMcolaboradores;Emumatransaopodemosverdadosaindano efetivados; ROLLBACK;Agoravamosfazerobancodedadosvoltaraoestadoanterior;
O comando ROLLBACK desfaz todas as alteraes feitas pelos comandos aps o BEGIN. Mas se as alteraes que os comandos fizeram devem ser efetivadas, usa-se o comando COMMIT:
COMMIT;
Exemplo:
BEGIN; DELETEFROMcolaboradoresWHEREmat_id='00035'; SELECT*FROMcolaboradores; COMMIT;Agoraefetivarasalteraes;
Colocando de forma geral uma transao tem o seguinte corpo:

BEGIN; comando_SQL_1; comando_SQL_n; [COMMIT|ROLLBACK]
30.6 - SAVEPOINT - Ponto de Salvamento Define um novo ponto de salvamento na transao atual. O ponto de salvamento uma marca especial dentro da transao que permite desfazer todos comandos executados aps a criao do SAVEPOINT, dessa forma voltando ao estado antes da criao do ponto de salvamento. Sintaxe:
SAVEPOINTnome_savepoint;
78
30.6.1 - ROLLBACK TO SAVEPOINT usado para desfazer at o ponto de salvamento. Sintaxe;

ROLLBACKTO[SAVEPOINT]nome_savepoint;
Obs.: A palavra SAVEPOINT pode ser omitida. Exemplo:

BEGIN; DELETEFROMcolaboradoresWHEREmat_id='00034'; SELECT*FROMcolaboradores; SAVEPOINTsp1;Criaodopontodesalvamento; UPDATEcolaboradoresSETsalario=600WHEREmat_id='00033'; SELECT*FROMcolaboradores; ROLLBACKTOsp1; SELECT*FROMcolaboradores; COMMIT;Agoraefetivarasalteraesantesdesp1quenotiveramROLLBACKTO;
30.6.2 - RELEASE SAVEPOINT Destri um ponto de salvamento definido anteriormente, mas mantm os efeitos dos comandos executados aps a criao do SAVEPOINT. Sintaxe:
RELEASE[SAVEPOINT]nome_savepoint
Exemplo:
BEGIN; DELETEFROMcolaboradoresWHEREmat_id='00034'; SELECT*FROMcolaboradores; SAVEPOINTsp1;Criaodopontodesalvamento; UPDATEcolaboradoresSETsalario=600WHEREmat_id='00033'; SELECT*FROMcolaboradores; RELEASEsp1;Opontodesalvamentofoidestrudo; SELECT*FROMcolaboradores; COMMIT;Mesmocomopontodesalvamentodestrudo,asalteraespodemserefetivadas;
Os pontos de salvamento somente podem ser estabelecidos dentro de um bloco de transao. Podem haver vrios pontos de salvamento definidos dentro de uma transao.
79
30.7 - SELECT FOR UPDATE Dentro de uma transao, atravs dos registros selecionados bloqueia os mesmos para que outras sesses no os apaguem ou alterem enquanto a transao no for finalizada. Exemplo: Sesso 1:
BEGIN; SELECT*FROMcolaboradoresWHEREcargoISNULLFORUPDATE; Todososregistrosexibidosestotravadosenquantoatransaonoacabar;
Sesso 2:
DELETEFROMcolaboradoresWHEREcargoISNULL;
Sesso 1:
COMMIT;
Aps a efetivao dos dados pela sesso 1 a sesso 2 pde finalmente fazer as alteraes que desejava.
80
31 - Cursores
Cursores so variveis que apontam para consultas, armazenam os resultados, economizam memria no retornando todos os dados de uma s vez em consultas que retornam muitas linhas. Para usurios da linguagem PL/pgSQL, normalmente no h necessidade de se preocuparem com isso, j que os laos "FOR" utilizam internamente um cursor para evitar problemas com memria. Uma interessante utilizao retornar a referncia a um cursor criado pela funo, que permite a quem executou ler as linhas. Assim proporciona uma maneira eficiente para a funo retornar grandes conjuntos de linhas. Cursores podem retornar dados no formato texto ou no formato binrio pelo comando FETCH. Por padro retornam dados no formato texto, assim como os produzidos pelo comando SELECT. Sintaxe de criao de um cursor:
DECLAREnome[BINARY][INSENSITIVE][[NO]SCROLL] CURSOR[{WITH|WITHOUT}HOLD]FORcomando [FOR{READONLY|UPDATE[OFcoluna[,...]]}]
Parmetros:
nome BINARY INSENSITIVE Nome do cursor; Retorna os dados no formato binrio ao invs do formato texto. Indica que os dados retornados pelo cursor no devem ser afetados pelas atualizaes feitas nas tabelas subjacentes ao cursor, enquanto o cursor existir. No PostgreSQL todos os cursores so INSENSITIVE. Atualmente esta palavra chave no produz efeito, estando presente por motivo de compatibilidade com o padro SQL. Especifica que o cursor pode ser utilizado para retornar linhas de uma maneira no sequencial (por exemplo, para trs). Dependendo da complexidade do plano de execuo do comando, especificar SCROLL pode impor uma penalidade de desempenho no tempo de execuo do comando. Faz com que o cursor no retorne linhas de uma maneira no seqencial. recomendado usar essa opo para fins de desempenho e economia de memria quando preciso que as linhas a serem retornadas sejam sequenciais. Especifica que o cursor pode continuar sendo utilizado aps a transao que o criou ter sido efetivada com sucesso ou at mesmo criar um cursor sem estar dentro de uma transao. Especifica que o cursor no pode ser utilizado fora da transao que o criou. Quando no especificado nem WITHOUT HOLD nem WITH HOLD, o padro WITHOUT HOLD. A consulta feita para o cursor retornar.
SCROLL (rolar)
NO SCROLL
WITH HOLD WITHOUT HOLD comando
FOR READ ONLY Indica que o cursor ser utilizado no modo somente para leitura. FOR UPDATE Indica que o cursor ser utilizado para atualizar tabelas. Uma vez que as atualizaes por cursor no so suportadas atualmente pelo PostgreSQL, especificar FOR UPDATE provoca uma mensagem de erro, e especificar FOR READ ONLY no produz efeito. As colunas a serem atualizadas pelo cursor. Uma vez que as atualizaes por cursor no so suportadas atualmente pelo PostgreSQL, a clusula FOR UPDATE provoca uma mensagem de erro.
coluna
Observaes: As palavras chave BINARY, INSENSITIVE e SCROLL podem estar em qualquer ordem. Se WITHHOLD no for especificado, o cursor criado por este comando poder ser utilizado somente dentro da transao corrente. E se a efetivao da transao que criou o cursor for bem-sucedida, o cursor poder continuar sendo acessado pelas transaes seguintes na mesma sesso (Mas se a transao que criou o cursor for interrompida, o cursor ser removido). O cursor criado com WITHHOLD fechado quando submetido um comando CLOSE explcito para o cursor, ou quando a sesso termina. Se for especificado NOSCROLL, retornar linhas para trs no ser permitido em nenhum caso. O padro SQL somente trata de cursores na linguagem SQL incorporada. O servidor PostgreSQL no implementa o comando OPEN para cursores; o cursor considerado aberto ao ser declarado. Entretanto o ECPG, o pr-processador do PostgreSQL para a linguagem SQL incorporada, suporta as convenes de cursor do padro SQL, incluindo as que envolvem os comandos DECLARE e OPEN.
81
Exemplos: Criando um cursor dentro de uma transao:

BEGIN; DECLAREcursor1CURSORFORSELECT*FROMcolaboradores; COMMIT;
Criando um cursor fora de uma transao:

DECLAREcursor2CURSORWITHHOLDFORSELECT*FROMcolaboradores;
31.1 - FETCH - Recuperando linhas de um cursor Para tal tarefa usado o comando Sintaxe:
FETCH[direo{FROM|IN}]nome_cursor FETCH,
que em ingls significa buscar, trazer, alcanar...
Onde direo pode ser vazio ou um dos:

NEXT PRIOR FIRST LAST ABSOLUTE n Prxima linha. Este o padro quando a direo omitida. Linha anterior. Primeira linha da consulta (o mesmo que ABSOLUTE 1). ltima linha da consulta (o mesmo que ABSOLUTE -1). A n-sima linha da consulta, ou a abs(n)-sima linha a partir do fim se o n for negativo. Posiciona antes da primeira linha ou aps a ltima linha se o n estiver fora do intervalo; em particular, ABSOLUTE 0 posiciona antes da primeira linha. A n-sima linha frente, ou a abs(n)-sima linha atrs se o n for negativo. RELATIVE 0 retorna novamente a linha corrente, se houver. As prximas n linhas (o mesmo que FORWARD n). uma constante inteira, possivelmente com sinal, que determina a posio ou o nmero de linhas a serem retornadas. Para os casos FORWARD e BACKWARD, especificar um n negativo equivalente a mudar o sentido de FORWARD e BACKWARD. Todas as linhas restantes (o mesmo que FORWARD ALL). Prxima linha (o mesmo que NEXT). As prximas n linhas. FORWARD 0 retorna novamente a linha corrente. Todas as linhas restantes. Linha anterior (o mesmo que PRIOR). As n linhas anteriores (varrendo para trs). BACKWARD 0 retorna novamente a linha corrente. Todas as linhas anteriores (varrendo para trs).
RELATIVE n n
ALL FORWARD FORWARD n FORWARD ALL BACKWARD BACKWARD n BACKWARD ALL
Exemplos:
FETCHcursor2;Porpadroretornaaprximalinha FETCH2INcursor2;Retornaasprximas2linhas FETCH1INcursor2;Retornaalinhaanterior FETCHFORWARDFROMcursor2;Prximalinha FETCHFORWARD7FROMcursor2;Prximas7linhas FETCHFIRSTFROMcursor2;Sempreretornaraprimeiralinha FETCHLASTFROMcursor2;Sempreretornaraltimalinha
82
31.2 - MOVE Movendo Cursores O comando MOVE faz a mudana na posio de um cursor sem retornar linhas. Sua sintaxe similar do FETCH:
MOVE[direo{FROM|IN}]nome_cursor
Exemplo:
MOVE7FROMcursor2;Volta7posiesdocursor
31.3 - CLOSE Fechando Cursores H duas maneiras de se fechar um cursor, de forma implcita; quando uma transao encerrada ou no se concretiza (COMMIT ou ROLLBACK) e de forma explcita, com o comndo CLOSE: Sintaxe:
CLOSE{nome|ALL}
Exemplos:
CLOSEcursor2;Fechaocursor2 CLOSEALL;Fechatodoscursoresabertos
83
32 - BLOB Binary Large OBject (Objeto Grande Binrio)

Sua utilidade est em armazenar arquivos dentro do banco de dados, sejam eles figuras, som, executveis ou qualquer outro. Os dados de um blob so do tipo bytea. Para se inserir ou recuperar blobs no PostgreSQL necessrio utilizar as funes; lo_import(text): Seu parmetro a localizao do arquivo a ser inserido no banco. Essa funo retorna o identificador do arquivo inserido (oid). lo_export(oid,text): Seus parmetros so respectivamente o oid do BLOB e a localizao futura do arquivo.
Aps importar um arquivo para a base de dados, o campo da tabela s ter o oid, que faz referncia para os dados do BLOB que na verdade se encontram na tabela de sistema pg_largeobject. Exemplo:
/*TabeladoBLOB*/ CREATETABLEfiguras( id_figuraoid, nome_figuravarchar(15) ); /*Importandooobjetoparadentrodobancodedados*/ INSERTINTOfigurasVALUES(lo_import('/tmp/figura1.jpg'),'Figura1'); /*Recuperandooobjetonodiretrio/tmp*/ SELECT lo_export(id_figura,'/tmp/copia_figura1.jpg') FROM figuras WHERE nome_figura = 'Figura 1';
32.1 - Removendo BLOBs Mesmo apagando uma linha de um BLOB com o comando DELETE na tabela figuras, o objeto binrio relativo no ser removido da tabela pg_largeobject. Para tal deve-se usar a funo lo_unlink(oid), cujo parmetro o oid do BLOB. Exemplo:
SELECTlo_unlink(16484);
ou tambm poderia ser:

SELECTlo_unlink(id_figura)FROMfigurasWHEREnome_figura='Figura1';
Agora no existe mais o objeto binrio, ento pode-se apagar a referncia a ele na tabela figuras:
DELETEFROMfigurasWHEREnome_figura='Figura1';
84
33 - Funes Criadas Pelo Usurio

O PostgreSQL tem quatro tipos de funo: Escritas em SQL; Internas; Escritas na linguagem C. Procedurais, escritas em PL/pgSQL ou PL/Tcl, por exemplo;
Todos os tipos de funo aceitam tipos base ou compostos ou combinados, como parmetros. E tambm podem retornar um conjunto de valores base ou composto. Sintaxe:
CREATE[ORREPLACE]FUNCTION nome([[modo][nome]tipo[,...]]) [RETURNStipo_retorno] {LANGUAGEnome_linguagem |IMMUTABLE|STABLE|VOLATILE |CALLEDONNULLINPUT|RETURNSNULLONNULLINPUT|STRICT |[EXTERNAL]SECURITYINVOKER|[EXTERNAL]SECURITYDEFINER |COSTcusto_execuo |ROWSregistros_retornados |SETparmetro_configurao{TOvalor|=valor|FROMCURRENT} |AS'definio' |AS'arquivo_objeto','link_simblico' }... [WITH(atributo[,...])]
Corpo de uma funo simplificado:

CREATE[ORREPLACE]FUNCTIONnome_funcao(parametros) RETURNStipoASdelimitador comando1; comandoN; delimitadorLANGUAGElinguagem;
Obs.: Quando se cria uma funo de uma forma geral temos: CREATEFUNCTIONnome Se quisermos sobrescrev-la usamos CREATEORREPLACEFUNCTIONnome... No entanto s vlido sobrescrever uma funo se o tipo de retorno for o mesmo da original. Caso contrrio ter que primeiro apagar a funo. 33.1 - Funes SQL Executam uma lista de declaraes SQL e retornam o resultado da ltima consulta dessa lista. 33.1.1 - Funes com Retorno Nulo
A ltima declarao de uma funo deve ser um seu retorno seja declarado do tipo void. Exemplo:
SELECT,
o qual servir como retorno, a no ser que
CREATEFUNCTIONfunc_teste() RETURNSvoidAS$$ DELETEFROMcolaboradoresWHEREcargoISNULL; $$LANGUAGESQL; SELECTfunc_teste();Usandoafuno;
85
33.1.2 - Funes com Retorno No Nulo Funes podem retornar uma linha ou um conjunto de linhas. Nos casos em que o retorno no um conjunto, a primeira linha do resultado da ltima consulta passa a ser o retorno da funo mesmo que tenham mais linhas. Se a ltima consulta no retornar nenhuma linha o retorno nulo. Exemplo:
CREATEORREPLACEFUNCTIONfunc_teste2() RETURNSvarchar(15)AS$$ SELECTnomeFROMcolaboradoresORDERBYnome; $$LANGUAGESQL;
33.1.3 - Apagando uma Funo Como se faz com a maior parte dos tipos de objetos... Exemplo:
DROPFUNCTIONfunc_teste();
33.1.4 - Funes que Retornam Conjuntos Para que uma funo retorne um conjunto deve-se especificar o retorno como sendo que algum_tipo pode ser uma tabela. E ento todas as linhas do resultado so retornadas. Exemplo:
CREATEORREPLACEFUNCTIONfunc_teste() RETURNSSETOFcolaboradoresAS$$ SELECT*FROMcolaboradoresWHEREuf='RJ'; $$LANGUAGESQL; SETOF algum_tipo,
Podemos inclusive usar funes que retornam vrias linhas de uma tabela como se a mesma fosse uma tabela. Exemplos:
SELECTnomeFROMfunc_teste(); SELECT*FROMfunc_teste();
33.1.5 - Funes com Parmetros Uma funo pode ser criada com ou sem parmetros. Os parmetros no so nomeados, devem ser colocados apenas os tipos entre os parnteses e no caso de ser mais de um devem ser separados por vrgulas. Nos comandos, cada parmetro chamado pela referncia $n, onde n a posio que corresponde ao parmetro. Exemplos:
Funodeapenasumparmetro; CREATEORREPLACEFUNCTIONfunc_teste(char(5)) RETURNStextAS$$ SELECTnome||':'||salarioFROMcolaboradoresWHEREmat_id=$1; $$LANGUAGESQL;
86
SELECTfunc_teste('00001');Oparmetrodeverinseridodeacordocomotipo; CREATEORREPLACEFUNCTIONfunc_teste(real,char(2)) RETURNSSETOFtextAS$$ SELECTnome||''||snomeFROMcolaboradoresWHEREsalario<=$1ANDuf=$2; $$LANGUAGESQL; SELECTfunc_teste(3000,'SP');Doisparmetrosinseridosnafuno CREATEORREPLACEFUNCTIONdaqui_uma_semana(date) RETURNSdateAS' SELECT$1+7; 'LANGUAGESQL; SELECTdaqui_uma_semana('01/07/2009');
33.1.6 - Sobrecarga de Funo Um recurso muito usado em linguagens de programao Orientadas a Objeto (Java, por exemplo), possibilita criar mais de uma funo com o mesmo nome, desde que os parmetros sejam diferentes. Ou seja, nomes de funes podem ser sobrecarregados. Quando se executa um comando, o servidor determina qual funo exatamente deve ser chamada tomando como base os tipos de dados e os parmetros. Pode-se utilizar sobrecarga de funes para simular funes com nmero varivel de parmetros (numero mximo finito). Exemplos:
Funocomparmetrosdotiporeal; CREATEORREPLACEFUNCTIONsoma_parametros(real,real) RETURNSrealAS$$ SELECT$1+$2; $$LANGUAGESQL; Funocomparmetrosdotipovarchar; CREATEORREPLACEFUNCTIONsoma_parametros(varchar,varchar) RETURNSvarcharAS$$ SELECT$1||''||$2; $$LANGUAGESQL; SELECTsoma_parametros(333,444);Parmetrosdotiporeal; SELECTsoma_parametros('PostgreSQL','rulz!!!');Parmetrosdotipovarchar;
Para apagar funes sobrecarregadas preciso especificar os tipos de parmetros. Portando se desejarmos apagar as duas variaes de soma_parametros() devemos fazer da seguinte maneira:
DROPFUNCTIONsoma_parametros(real,real); DROPFUNCTIONsoma_parametros(varchar,varchar);
87
33.2 - Funes internas So funes escritas na Linguagem C que foram estaticamente ligadas ao servidor PostgreSQL. Todas as funes internas que esto no servidor, normalmente, so declaradas na inicializao do cluster, porm o usurio pode criar aliases para uma funo interna atravs do comando CREATE FUNCTION. Exemplo:
CREATEFUNCTIONraizquad(doubleprecision)RETURNSdoubleprecision AS'dsqrt' LANGUAGEinternalSTRICT; SELECTraizquad(49);
Obs.: Nem todas as funes pr-definidas so internas no sentido acima. Algumas funes pr-definidas so escritas em SQL. 33.3 - Funes na linguagem C O usurio pode definir funes em C, as quais devem ser compiladas como bibliotecas e so carregadas conforme so requisitadas pelo servidor. Funes compiladas ganham maior desempenho do que funes escritas em linguagens compiladas. 33.4 - Linguagens procedurais No PostgreSQL possvel definir funes pelo usurio alm de SQL e C. Essas linguagens so genericamente chamadas de linguagens procedurais (PL: Precedural Language). So exemplos de PLs: PL/pgSQL, PL/Tcl, PL/Perl, PL/Python e outras linguagens definidas pelo usurio. H outras PLs adicionais, porm no so includas na distribuio ncleo. 33.4.1 - Instalao de linguagem procedural A linguagem procedural deve ser instalada em cada banco onde ser utilizada. Se instalar no banco de dados template1, automaticamente estar disponvel para todos os bancos dados que forem criados aps a instalao da linguagem, sendo que suas entradas em template1 so copiadas. Ento o administrador pode definir quais linguagens ficaro disponveis de acordo com o banco de dados, inclusive, se quiser tornar algumas padro. Para instalar a linguagem PL/pgSQL no banco de dados atual use a sintaxe:
CREATELANGUAGEplpgsql;
Ou se preferir, use o aplicativo createlang no shell:

createlangplpgsqlbanco_de_dados
33.4.2 - PL/pgSQL PL/pgSQL uma linguagem procedural carregvel feita para o PostgreSQL, a qual similar PL/SQL do Oracle. Enquanto declaraes SQL so executadas individualmente pelo servidor, a linguagem PL/pgSQL agrupa um bloco de processamento e comandos em srie dentro do servidor, somando o poder da linguagem procedural com a facilidade do SQL.
88
Economiza-se tempo, devido ao fato de no ser necessrio sobrecarregar a comunicao entre cliente e servidor, aumentando o desempenho. Em PL/pgSQL todos os tipos de dados, operadores e funes SQL podem ser utilizados. A utilizao de um editor de texto preferido pelo usurio aconselhvel para se desenvolver em PL/pgSQL. Em uma janela se escreve o cdigo (pelo editor) e em outra utiliza-se o psql para carregar as funes escritas. Utilizando "CREATE OR REPLACE FUNCTION" recomendvel para fins de atualizao do que j foi criado. 33.4.2.1 - Estrutura PL/pgSQL A linguagem PL/pgSQL estruturada em blocos. O texto completo da definio da funo deve ser um bloco. Um bloco definido como:
[<<rtulo>>] [DECLARE declaraes] BEGIN instrues END;
Declaraes e instrues dentro do bloco devem ser terminadas com ponto-e-vrgula. Um bloco dentro de outro bloco deve ter um ponto-e-vrgula aps o END, conforme exibido acima, entretanto, o END final que conclui o corpo da funo no requer o ponto-e-vrgula. Palavras chaves e identificadores podem ser escritos mesclando letras maisculas e minsculas. As letras dos identificadores so convertidas implicitamente em minsculas, a menos que estejam entre aspas. H dois tipos de comentrios no PL/pgSQL. O hfen duplo (--) comentrio de linha. O /* inicia um bloco de comentrio vai at a prxima ocorrncia de */. Os blocos de comentrio no podem ser aninhados, mas comentrios de linha podem estar dentro de blocos de comentrio, e os hfens duplos escondem os delimitadores de bloco de comentrio /* e */. Variveis declaradas na seo de declarao que antecede um bloco so inicializadas com seu valor padro toda vez que o bloco executado, e no somente uma vez a cada chamada da funo. Por exemplo:
CREATEFUNCTIONteste_escopo()RETURNSintegerAS$$ <<bloco_externo>> DECLARE quantidadeinteger:=30; BEGIN RAISENOTICE'Aquiaquantidade%',quantidade;Aquantidadeaqui30 quantidade:=50; Subbloco <<bloco_interno>> DECLARE quantidadeinteger:=80; BEGIN RAISENOTICE'Aquiaquantidade%',quantidade;Aquantidadeaqui80 END; Fimdosubbloco RAISENOTICE'Aquiaquantidade%',quantidade;Aquantidadeaqui50 RETURNquantidade; END; $$LANGUAGEplpgsql;
Obs.: No confundir o uso BEGIN/END de agrupamento de instrues PL/pgSQL com controle de transao. BEGIN/END de PL/pgSQL s para agrupamento, no iniciam nem terminam transaes.
89
33.4.2.2 - Declaraes Variveis utilizadas em um bloco devem ser declaradas em DECLARE (exceto a varivel de lao FOR que interage sobre um intervalo de valores inteiros, que automaticamente declarada como do tipo inteiro). As variveis PL/pgSQL podem ser de qualquer tipo SQL. A sintaxe geral para declarao de variveis :
nome[CONSTANT]tipo[NOTNULL][{DEFAULT|:=}expresso]; DEFAULT CONSTANT NOTNULL Seforinserida,especificaovalorpadrovarivelquandooblocoforprocessado. Casocontrrio,avarivelterseuvalornulo. Nopermitequesejaatribudooutrovalorvarivel,mantendoovalorconstante enquantooblocodurar. Fazcomquesejaobrigatriaumaatribuiodevalornonulovarivel
Exemplos:
num_pecaintegerCONSTANTinteger:=97; qtdnumeric(7); urlvarchar:='http://meusite.com'; minha_linhanome_da_tabela%ROWTYPE; meu_camponome_da_tabela.nome_da_coluna%TYPE; uma_linhaRECORD; quantidadeintegerDEFAULT32;
33.4.2.3 - Aliases de Parmetros de Funo Parmetros de uma funo tem como identificadores a simbologia $n, sendo que n o nmero relativo posio do parmetro. No entanto, podemos melhorar a legibilidade do cdigo atribuindo aliases, nomeando os parmetros. Exemplo:
CREATEORREPLACEFUNCTIONcalcula_imposto(precoreal,porcent_impreal)RETURNSrealAS$$ BEGIN RETURNpreco*(porcent_imp/100); END; $$LANGUAGEplpgsql;
A outra forma, que era a nica disponvel antes da verso 8.0 do PostgreSQL, declarar explicitamente um alias utilizando a sintaxe de declarao:
nomeALIASFOR$n;
O exemplo acima escrito utilizando este estilo fica da seguinte maneira:

CREATEORREPLACEFUNCTIONcalcula_imposto(real,real)RETURNSrealAS$$ DECLARE precoALIASFOR$1; porcent_impALIASFOR$2; BEGIN RETURNpreco*(porcent_imp/100); END; $$LANGUAGEplpgsql;
90
33.4.2.4 - SELECT INTO Quando uma consulta retorna vrias colunas (de apenas uma linha), o resultado pode ser atribudo a uma varivel registro (RECORD), ou do tipo linha (%ROWTYPE) ou uma lista de variveis escalares (lista de expresses). Da seguinte forma:
SELECTINTOdestinoexpressoes_de_consultaFROM...;
Sendo que "destino" pode ser uma varivel do tipo RECORD ou ROWTYPE ou uma lista separada por vrgulas de variveis simples e campos de registro/linha, "expressoes_de-consulta" e o restante do docmando so como no puro SQL. A interpretao de SELECTINTO em PL/pgSQL diferente da interpretao padro do PostgreSQL. Pois na forma padro uma nova tabela criada com o nome referente a "destino", conforme mostrado acima. Se for necessrio criar uma tabela dentro de uma funo PL/pgSQL a partir do resultado de uma consulta de ser usada a sintaxe CREATETABLE nova_tabelaASconsulta. 33.4.2.5 - Atributos A linguagem PL/pgSQL disponibiliza atributos para auxiliar a lidar tipos de dados. So eles:
%ROWTYPE. TYPE%
Atributos so usados para declarar variveis que coincidam com o tipo de dado de uma coluna (%TYPE) ou para corresponder estrutura de uma linha ( %ROWTYPE). No preciso saber o tipo de dado quando se usa atributos para declarar variveis. Se um objeto for alterado futuramente, o tipo daa varivel mudar automaticamente para o tipo de dado sem alterar o cdigo. 33.4.2.5.1 - Tipo de Varivel Copiado varivel %TYPE A expresso %TYPE fornece o tipo da varivel ou da coluna da tabela. Pode ser utilizada para declarar variveis que guardam valores do banco de dados. Por exemplo, supondo que exista uma coluna chamada id_produto na tabela produtos, para declarar uma varivel com o mesmo tipo de dado de produtos.id_produto:
id_produtoprodutos.id_produto%TYPE;
Com %TYPE no preciso saber o tipo de dado da estrutura referenciada e, ainda mais importante, se o tipo de dado do item referenciado mudar no futuro (por exemplo: o tipo de dado de id_produto for mudado de integer para real), no precisar mudar a definio na funo. 33.4.2.5.2 - Varivel Tipo Linha - %ROWTYPE Pode armazenar uma linha inteira do resultado de uma consulta se o conjunto de colunas do comando for do mesmo tipo declarado para a varivel. Cada coluna pode ser referenciado individualmente usando a notao var.coluna. Ao declarar uma varivel-linha pode ter o mesmo tipo das linhas de uma tabela com a notao tabela%ROWTYPE ou sua declarao especificando um tipo composto (tabelas possuem um tipo composto associado, sendo o prprio nome da tabela). Por questes de portabilidade convm-se escrever %ROWTYPE, mas para o PostgreSQL no faz diferena. Sintaxe:
variaveltabela%ROWTYPE; variaveltipo_composto;
91
Exemplo:
CREATEORREPLACEFUNCTIONmatricula2nome(matriculachar(5))RETURNStextAS$$ DECLARE /* Avarivelnome_colaboradoraquijrecebeaestruturada tabelacolaboradores */ nome_colaboradorcolaboradores%ROWTYPE; BEGIN Aconsultaparadaroretornodafuno SELECTINTOnome_colaborador*FROMcolaboradoresWHEREmat_id=matricula; Retornaoprimeiro RETURNnome_colaborador.nome||''||nome_colaborador.snome; END; $$LANGUAGE'plpgsql'; SELECTmatricula2nome('00007')AS"NomeInteiro";
33.4.2.6 - Tipo Registro RECORD Variveis do tipo RECORD so similares a variveis do tipo ROWTYPE, porm RECORD no tem uma estrutura pr definida. Elas assumem a estrutura da linha atual que foi atribuda por uma consulta. Essa atribuio, que o assinalamento se d pelo comando SELECTINTO ou FOR. A sub-estrutura de uma varivel RECORD pode mudar cada vez que for usada em uma atribuio. Se uma varivel do tipo RECORD for acessada antes de ser assinalada, haver um erro. RECORD na verdade no um tipo de dados real e sim um espao reservado. Sintaxe:
variavel RECORD;
Exemplo:
CREATEORREPLACEFUNCTIONmatricula2nome(matriculachar(5))RETURNStextAS$$ DECLARE nome_colaboradorRECORD; BEGIN SELECTINTOnome_colaborador*FROMcolaboradoresWHEREmat_id=matricula; Retornaoprimeiro RETURNnome_colaborador.nome||''||nome_colaborador.snome; END; $$LANGUAGE'plpgsql'; SELECTmatricula2nome('00001');
33.4.2.7 - RENAME Renomeia variveis, registros ou linhas. Tem como mair utilidade quando NEW ou OLD devem ser referenciados por outro nome dentro da funo de um trigger. OBS.: Desde a verso 7.3 do PostgreSQL, RENAME parece estar com problemas. A correo tem baixa prioridade, devido ao fato de ALIAS atender a maior parte dos usos prticos de RENAME. Sintaxe:
RENAMEantigo_nomeTOnovo_nome;
Exemplo:
RENAMEnomeTOnome_funcionario;
92
33.4.2.8 - Atribuies Para atribuir valor a uma varivel, ou a um campo de linha ou de registro, se faz da seguinte maneira:
identificador:=valor;
O valor pode ser tambm uma expresso que seja equivalente a um valor. Exemplos:
quantidade:=$1; iINT:=0;Quandoavarivelcriada,tambmpodeatribuirvaloraela; preco:=5.27; total:=quantidaade*preco;
33.4.2.9 - PERFORM - Execuo sem Resultado Em certos casos, deseja-se avaliar um comando e desprezar seu resultado (especialmente quando uma funo produz efeitos indesejados e/ou no tem um valor til de resultado. Na linguagem PL/pgSQL usado a instruo PERFORM. Sintaxe:
PERFORMcomando;
A instruo deve ser excrita da mesma forma que uma consulta SQL, porm substituindo a palavra chave SELECT por PERFORM. A varivel especial FOUND "true" se caso o comando resultar pelo menos uma linha ou "false" se no produzir nenhuma. Exemplo: Uma funo que apenas diz se uma matrcula corresponde a um colaborador sem dizer qual:
CREATEORREPLACEFUNCTIONcolab_teste(char(5))RETURNStextAS$$ BEGIN UsodoPERFORMparadesprezaroresultado PERFORM*FROMcolaboradoresWHEREmat_id=$1; SePERFORM"Achar"(FOUND)pelomenosumalinhaverdadeiro(true) IFFOUNDTHEN RETURN'Existeocolaborador!'; ELSE UmaNotacommensagempersonalizada RAISENOTICE'Colaboradordematricula%naofoiencontrado',$1; RETURN'Erro!!!'; ENDIF; END;$$LANGUAGEplpgsql;
93
33.4.2.10 - NULL - No Fazer Nada s vezes conforme a situao, ao invs de executar comandos, pode-se simplesmente ignorar e executar nada. Para isso utiliza-se a instruo NULL. Exemplo: As duas funes abaixo so equivalentes:
CREATEORREPLACEFUNCTIONteste_div(int,int)RETURNSintAS$$ BEGIN RETURN$1/$2; EXCEPTION WHENdivision_by_zeroTHEN NULL;ignoraoerrocomainstruoNULL RETURNNULL; END; $$LANGUAGEplpgsql; CREATEORREPLACEFUNCTIONteste_div(int,int)RETURNSintAS$$ BEGIN RETURN$1/$2; EXCEPTION WHENdivision_by_zeroTHENignoraoerroomitindoainstruoNULL RETURNNULL; END; $$LANGUAGEplpgsql;
Qualquer uma das formas no PostgreSQL vlida. Obs.: Na linguagem PL/SQL do Oracle instrues vazias no so permitidas, a instruo obrigatria em situaes como essas.
NULL
94
33.4.2.11 - EXECUTE - Execuo Dinmica de Comandos Em certos casos necessrio executar dinamicamente comandos dentro de PL/pgSQL que envolvam diferentes tabelas e / ou tipos de dados. A instruo EXECUTE fornecida para este fim executando uma string que contm um comando SQL. Substituies de variveis em PL/pgSQL no so feitas na string do comando. Seus valores devem ser inseridos quando a string elaborada. Resultados de SELECT so desprezados pelo EXECUTE e at o momento SELECTINTO no suportado. Portanto, no h como extrair o resultado de um SELECT criado dinamicamente com EXECUTE puro. Porm, h dois jeitos de se fazer isso: com o lao FORINEXECUTEou um cursor com OPENFOREXECUTE, que ambos sero vistos mais adiante. Exemplos:
CREATEORREPLACEFUNCTIONtestaExecute() RETURNStextAS$$ BEGIN //* OcomandoEXECUTEeastring. Devidoaofatodaprpriastringestarentreaspassimples,osvaloresstring contidosneladevemestarentredoisparesdeaspassimples(''string''), comosevabaixo: */ EXECUTE'UPDATEcolaboradoresSETsetor=''Higiene''WHEREsetor=''Limpeza'''; RETURN'Ok!'; END; $$LANGUAGEplpgsql; SELECTtestaExecute(); CREATEORREPLACEFUNCTIONtestaExecute(tabelatext,colunatext,valor_novotext,valor_antigo text) RETURNStextAS$marcador$ BEGIN EXECUTE'UPDATE'||tabela||'SET'||coluna||'='||''''||valor_novo||''''||'WHERE'|| coluna||'='||''''||valor_antigo||''''; RETURN'OK!'; END; $marcador$LANGUAGEplpgsql; SELECTtestaExecute('colaboradores','setor','Limpeza','Higiene');
33.4.2.12 - Status de um Resultado H vrias maneiras para determinar o efeito de um comando atravs de dois mtodos: 33.4.2.12.1 - 1 Mtodo - GET DIAGNOSTICS
GETDIAGNOSTICSvarivel=item;
Permite obter os indicadores de status do sistema. "item" uma palavra chave que identifica o valor de estado para a varivel especificada. Os itens de status podem ser: -ROW_COUNT: Quantidade de linhas processadas pelo ltimo comando SQL; -RESULT_OID: O OID da ltima linha inserida pelo comando SQL mais recente, ou seja; s tem utilidade aps um comando INSERT.
95
Exemplo:
CREATEORREPLACEFUNCTIONresult_status(char(2)) RETURNSintegerAS$$ DECLARE linhasinteger; BEGIN PERFORM*FROMcolaboradoresWHEREuf=$1; /*Atribuivarivel"linhas"onderegistros referidospelocomandoanterior;*/ GETDIAGNOSTICSlinhas=ROW_COUNT; RETURNlinhas; END; $$LANGUAGEplpgsql;
33.4.2.12.2 - 2 Mtodo - FOUND

FOUND uma varivel booleana que inicialmente "false" dentro de cada chamada de funo PL/pgSQL. Sua definio dada por cada um dos seguintes tipos de instruo: SELECTINTO, PERFORM, UPDATE, INSERT, DELETE, FETCH e FOR. Cada instruo pode definir "True" se retornar / interagir com pelo menos uma linha ou "False" se no retornar / interagir com nenhuma linha. FOUND uma varivel local dentro de cada funo PL/pgSQL; qualquer alterao feita na mesma afeta somente a funo corrente.
33.4.2.13 - Retorno de Uma Funo Dois comandos retornam dados de uma funo: RETURN e RETURNNEXT. 33.4.2.13.1 - RETURN O comando RETURN com uma expresso (que pode ser um valor, uma varivel ou at mesmo void), finaliza a funo e retorna o valor da expresso pra quem executa. Para que o retorno seja um valor composto (linha), deve ser declarada uma varivel registro ou linha como a expresso. Sintaxe:
RETURNexpresso;
O valor retornado pela funo no pode ser deixado indefinido. Se o controle atingir o final do bloco de nvel mais alto da funo sem atingir uma instruo RETURN, ocorrer um erro em tempo de execuo. Se a funo for declarada como retornando void, ainda assim deve ser especificada uma instruo RETURN; mas neste caso a expresso aps o comando RETURN opcional, sendo ignorada caso esteja presente. 33.4.2.13.2 - RETURN NEXT Quando o retorno de uma funo PL/pgSQL declarado como sendo " SETOFtipo", h um procedimento diferente a ser seguido. Os itens individuais a serem retornados so especificados em comandos RETURNNEXT, e um comando RETURN final, sem argumento indica que a funo chegou ao fim. Sintaxe:
RETURNNEXTexpresso;
Exemplo:
96
CREATEORREPLACEFUNCTIONfuncao_next() RETURNSSETOFvarchar(10)AS$funcao_next$ BEGIN RETURNNEXT'Saturno'; RETURNNEXT'Netuno'; RETURNNEXT'Urano'; RETURN; END; $funcao_next$LANGUAGEplpgsql;
Funes que utilizam RETURNNEXT devem ser chamadas como uma fonte de tabela na clusula
SELECT*fromfuncao_next();
FROM:
O comando RETURNNEXT salva o valor da expresso e em seguida a execuo continua na prxima instruo da funo PL/pgSQL. O conjunto de resultados (result set) construdo se executando sucessivos RETURNNEXTe o RETURN final, que no deve ter argumentos, faz o controle sair da funo. 33.4.2.14 - Condicionais Instrues IF (se) executam ou no comandos dependendo de condies. PL/pgSQL tem cinco formas de IF:
IF...THEN IF...THEN...ELSE IF...THEN...ELSEIF IF...THEN...ELSIF...THEN...ELSE IF...THEN...ELSEIF...THEN...ELSE
33.4.2.14.1 - IF-THEN Os comandos entre ignorados.

THEN
ENDIF
so executados se a condio for verdadeira, caso contrrio so
IFexpresso_booleanaTHEN comando1; comando2; comando...; comandoN; ENDIF;
33.4.2.14.2 - IF-THEN-ELSE Instrues IFTHENELSE ampliam o IFTHEN permitindo especificar alternativamente um conjunto de comandos a serem executadas se a condio for avaliada como falsa.
IFexpresso_booleanaTHEN instrues ELSE instrues ENDIF;
33.4.2.14.3 - IF-THEN-ELSE IF Aninhamentos de instrues podem ser feitos como segue o exemplo:
97
IFuf='SP'THEN pessoa_uf:='paulista'; ELSE IFuf='RJ'THEN pessoa_uf:='fluminense'; ENDIF; ENDIF;
Quando essa forma utilizada, na verdade, uma instruo IF est sendo aninhada dentro da parte ELSE da instruo IF principal. Ento, preciso uma instruo END para cada IF aninhado e tambm para o "IF-ELSE" pai. Apesar de funcionar, feito de forma esteticamente desagradvel quando existem muitas alternativas para serem avaliadas. A prxima forma seria mais conveniente: 33.4.2.14.4 - IF-THEN-ELSIF-ELSE
IFexpresso_booleanaTHEN comandos [ELSIFexpresso_booleanaTHEN comandos [ELSIFexpresso_booleanaTHEN comandos ...]] [ELSE instrues] ENDIF;
Usando o modo IFTHENELSIFELSE mais adequado para verificar muitas alternativas em uma instruo. equivalente aos comandos IFTHENELSEIFTHEN aninhados, porm s precisa de um ENDIF. Exemplo:
IFnumero=0THEN resultado:='zero'; ELSIFnumero>0THEN resultado:='positivo'; ELSIFnumero<0THEN resultado:='negativo'; ELSEAquianicaoutrapossibilidadequeonmerosejanulo resultado:='NULL'; ENDIF;
33.4.2.14.5 - IF-THEN-ELSEIF-ELSE
ELSEIF
um alias para ELSIF, ou seja, o resultado ser o mesmo usando qualquer um dos dois.
33.4.2.15 - Laos Com os comandos LOOP, EXIT, WHILE, e FOR possvel fazer uma funo repetir uma srie de comandos.
98
33.4.2.15.1 - LOOP Define um lao incondicional, repetindo indefinidamente at que seja finalizado por um EXIT ou "RETURN". No caso de laos aninhados pode ser utilizado um rtulo opcional em EXIT para definir o nvel de aninhamento que deve ser finalizado.
[<<rtulo>>] LOOP instrues ENDLOOP;
33.4.2.15.2 - EXIT Se nenhum rtulo for determinado, o lao mais interno finalizado e o comando aps ENDLOOP executado em seguida. Se o rtulo for determinado, esse deve ser o rtulo do nvel atual ou de algum nvel externo do lao ou do bloco aninhado. E ento o lao ou bloco finalizado, e o controle continua na instruo aps o END do lao ou do bloco. Quando se tem o parmetro WHEN, a sada do lao se faz apenas se a condio especificada for verdadeira, caso contrrio, o controle passa para a instruo aps o EXIT. O EXIT pode interromper prematuramente qualquer tipo de lao, no se limitando a apenas laos incondicionais. Sintaxe:
EXIT[rtulo][WHENexpresso];
Exemplos:
CREATEORREPLACEFUNCTIONteste_loop()RETURNSintAS$$ DECLARE iint; BEGIN i:=0; LOOP IFi=10THEN RAISENOTICE'Oindexadorvale10agora!!!'; EXIT;Fazsairdolao ENDIF; RAISENOTICE'Aindano...:('; i:=i+1; ENDLOOP; RETURNi; END; $$LANGUAGEplpgsql;
99
CREATEORREPLACEFUNCTIONteste_loop() RETURNSintAS$$ DECLARE iint; BEGIN i:=0; LOOP IFi<10THEN RAISENOTICE'Aindano...:('; ENDIF; i:=i+1; EXITWHENi=10;Fazsairdolao ENDLOOP; RAISENOTICE'Oindexadorvale10agora!!!'; RETURNi; END; $$LANGUAGEplpgsql;
33.4.2.15.3 - WHILE Repete uma seqncia de comandos enquanto a expresso de condio for verdadeira. A condio avaliada antes de cada entrada no corpo do lao. Sintaxe:
[<<rtulo>>] WHILEexpressoLOOP comandos ENDLOOP;
Exemplos:
CREATEORREPLACEFUNCTIONteste_loop() RETURNSintAS$$ DECLARE iint; BEGIN i:=0; WHILEi<10LOOP RAISENOTICE'Aindano...:('; i:=i+1; ENDLOOP; RAISENOTICE'Oindexadorvale10agora!!!'; RETURNi; END; $$LANGUAGEplpgsql;
100
CREATEORREPLACEFUNCTIONteste_loop() RETURNSintAS$$ DECLARE iint; BEGIN i:=0; WHILENOTi=10LOOP RAISENOTICE'Aindano...:('; i:=i+1; ENDLOOP; RAISENOTICE'Oindexadorvale10agora!!!'; RETURNi; END; $$LANGUAGEplpgsql;
33.4.2.15.4 - FOR (variao inteira) Cria um loop que interage em um intervalo de valores inteiros. A varivel "nome" definida automaticamente como sendo do tipo integer, e somente dentro do loop. As duas expresses que so os limites inferior e superior so avaliadas somente uma vez, ao entrar no lao. Por padro a razo da interao +1, quando o parmetro "REVERSE" especificado passa a ser -1. Sintaxe:
[<<rtulo>>] FORnomeIN[REVERSE]expresso..expressoLOOP comandos ENDLOOP;
Exemplos:
CREATEORREPLACEFUNCTIONteste_loop()RETURNSintAS$$ DECLARE iint:=0; BEGIN FORiIN0..9LOOP RAISENOTICE'Aindano...:('; i:=i+1; ENDLOOP; RAISENOTICE'Oindexadorvale10agora!!!'; RETURNi; END; $$LANGUAGEplpgsql;
101
CREATEORREPLACEFUNCTIONteste_loop()RETURNSintAS$$ DECLARE iint:=0; BEGIN FORiINREVERSE20..11LOOP RAISENOTICE'Aindano...:('; i:=i1; ENDLOOP; RAISENOTICE'Oindexadorvale10agora!!!'; RETURNi; END; $$LANGUAGEplpgsql;
Obs.: Se o limite inferior for maior que o limite superior (ou menor, no caso de ser executado sem gerar erro. 33.4.2.15.5 - Loop por Queries
REVERSE),
o loop no
possvel interagir atravs do resultado de uma query e manipular dados utilizando um lao FOR diferente. Cada linha resultante de comando (SELECT) atribuda, sucessivavemnte, varivel registro ou linha, e o corpo do loop executado uma vez para cada linha. Se o loop for finalizado por uma EXIT, o ltimo valor de linha atribudo ainda acessvel aps o lao. Sintaxe:
[<<rtulo>>] FORregistro_ou_linhaINcomandoLOOP comandos ENDLOOP; Exemplo: CREATEORREPLACEFUNCTIONloop_for()RETURNSintAS$$ DECLARE var1colaboradores%ROWTYPE; iint:=0; BEGIN
FORvar1INSELECT*FROMcolaboradoresWHEREuf='SP'LOOP i:=i+1; ENDLOOP; Retornaonmerodelinhasresultantesdaquery: RETURNi;
END; $$LANGUAGEplpgsql;
102
33.4.2.16 - FOR-IN-EXECUTE uma outra forma de interagir sobre linhas, semelhante anterior, porm o cdigo fonte do SELECT definido como uma expresso cadeia de caracteres, que avaliada e replanejada a cada iterao do loop. Isso permite ao programador escolher entre a velocidade de consulta pr-planejada e a flexibilidade da consulta dinmica, da mesma forma que no comando EXECUTE puro. Sintaxe:
[<<rtulo>>] FORregistro_ou_linhaINEXECUTEstring_de_comandoLOOP comandos ENDLOOP; Exemplo: CREATEORREPLACEFUNCTIONfor_in_exec(var_ufchar(2))RETURNSintAS$$ DECLARE var1colaboradores%ROWTYPE; iint:=0; BEGIN FORvar1INEXECUTE'SELECT*FROMcolaboradoresWHEREuf='||''''||var_uf||''''LOOP i:=i+1; ENDLOOP; Retornaonmerodelinhasresultantesdaquery: RETURNi; END; $$LANGUAGEplpgsql;
Obs.: Atualmente o analisador PL/pgSQL distingue os dois tipos de loops FOR (inteiro e resultado de uma query), verificando se encontra ".." fora de parnteses entre IN e LOOP. Caso no encontrar, o loop entendido como sendo de linhas. Se ".." for escrito de maneira equivocada, pode causar um erro notificando que a varivel do lao, para lao sobre linhas, deve ser uma varivel registro ou linha, ao invs de um simples erro de sintaxe.
103
33.4.2.17 - Tratamento de Erros Por padro, qualquer erro em uma funo causa sua interrupo. Pode-se capturar e se recuperar de erros utilizando a clusula EXCEPTION. Se no ocorrer erro algum todos comandos antes de EXCEPTION sero executados. Sintaxe:
[<<rtulo>>] [DECLARE declaraes] BEGIN comandos EXCEPTION WHENcondio[ORcondio...]THEN comandos_do_tratador [WHENcondio[ORcondio...]THEN comandos_do_tratador ...] END;
Exemplo:
CREATEORREPLACEFUNCTIONtrata_except(num1int,num2int)RETURNSintAS$$ DECLARE resultadoint; BEGIN resultado:=num1/num2; RETURNresultado; EXCEPTION WHENdivision_by_zeroTHEN RAISEEXCEPTION'NodividirsporZero!!!'; END; $$LANGUAGEplpgsql;
104
33.4.2.18 - Mensagens e Erros O comando RAISE gera mensagens que informam ou causam erros. Sintaxe:
RAISEnvel'formato'[,varivel[,...]];
Sendo que "nvel" pode ser DEBUG, LOG, INFO, NOTICE, WARNING, e EXCEPTION. O nvel EXCEPTION provoca um erro (que normalmente interrompe a transao atual); os outros nveis geram apenas mensagens com diferentes prioridades. Na string de formatao, o caractere "%" representa o prximo argumento (opcional). Para se obter uma mensagem com o caractere "%" literalmente deve-se fazer: %%. Exemplos:
RAISENOTICE'Totaldasemana:%',total;Exibeafrasecomocontedodavarivel "total" RAISENOTICE'%%%delucro!',50;Exibeovaloretambmocaractere"%"
105
33.4.2.19 - Cursores em Funes PL/pgSQL Cursores facilitam muito em consultas cujo retorno so muitas linhas, pois ao invs de fazer a consulta de uma vez, pode-se ler algumas linhas por vez. Declarao de Cursores Cursores so um tipo de varivel, portanto devem ser estar dentro de Exemplo:
... DECLARE cursor_vaziorefcursor; cursor_determinadoFORSELECT*colaboradores; cursor_com_arg(argumentoint)ISSELECT*FROMcolaboradoresWHEREcargoISNOT NULL; ... DECLARE.
Foram criados dois cursores, ambos so do mesmo tipo (refcursor),no entanto, "cursor_vazio" no foi determinado em sua declarao e pode ser usado para qualquer query e "cursor_determinado" j tem uma query determinada para executar. 33.4.2.19.1 - Abertura de Cursores Para se fazer uso de um cursor em uma funo preciso abr-lo. Cursores Vazios:
OPENcursor_vazioFORSELECT*FROMcolaboradores; OPENFOREXECUTE;
Cursores Vazios Para Executar Strings:

OPENcursor_vazioFOREXECUTE'SELECT*FROMcolaboradesWHEREuf='||$1||'ORDERBYnome';
Cursores Determinados:
OPENcursor_determinado;
Cursores Com Argumentos:

OPENcursor_com_arg(77);
106
33.4.2.19.2 - Utilizao e Fechamento de Cursores Como j foi visto, o comando "FETCH" serve para recuperar informaes de cursores. Com o comando "FETCH" pode recuperar a(s) linha determinada(s) jogando as informaes em variveis que podem ser do tipo ROWTYPE, RECORD ou variveis de algum desses tipos separadas por vrgulas. Exemplo:
FETCHcursor_xINTOvar1; FETCHcursor_yINTOvar1,var2,var3,var4;
A varivel FOUND pode ser utilizada pra se saber se houve alguma linha retornada. Aps a utilizao, um cursor tem que ser fechado com o comando "CLOSE" para liberar recursos. Porm o mesmo pode ser aberto novamente. ser aberto novamente. Exemplo:
CLOSEcursor_x;
107
33.4.2.19.3 - Funes PL/pgSQL que Retornam Cursores Funes PL/pgSQL podem retornar cursores, que til para retornar muitas linhas ou colunas, principalmente conjuntos de resultados muito grandes. A funo deve abrir o cursor e retornar o nome do cursor para quem fez a chamada (ou simplesmente abrir o cursor utilizando o nome do portal especificado por, ou de outra forma conhecido por quem chamou). Quem chamar poder ler as linhas usando o cursor. Quem chama pode fechar o cursor, ou o mesmo ser fechado automaticamente quando a transao for finalizada. O nome do portal para o cursor pode ser definido pelo programador ou gerado automaticamente. Para definir o nome do portal deve simplesmente atribuir uma string varivel do tipo "refcursor" antes de abr-la. A string da varivel ser utilizada pelo "OPEN" como o nome do portal subjacente. Mas se a varivel do tipo "refcursor" nula, o comando "OPEN" automaticamente gera um nome que no d conflito com nenhum outro portal que j exista e atribui esse nome varivel do tipo "refcursor". Obs.: Uma varivel cursor ativada inicializada com o valor da string relativa ao seu nome, ento, o nome do portal o mesmo da varivel cursor, a no ser que o programador mude esse nome fazendo uma atribuio antes de abrir o cursor. Mas uma varivel cursor desativada tem inicialmente seu valor nulo por padro, ento, recebe um nome nico gerado de forma automtica, se esse no for mudado. Exemplos:
CREATETEMPTABLEtb_teste(campotext); INSERTINTOtb_testeVALUES('123'); CREATEORREPLACEFUNCTIONretorna_cursor(refcursor)RETURNSrefcursorAS$$ BEGIN OPEN$1FORSELECTcampoFROMtb_teste; RETURN$1; END; $$LANGUAGEplpgsql; BEGIN;Aberturadeumatransao; SELECTretorna_cursor('teste_cursor'); FETCHALLINteste_cursor; COMMIT; /*Oprximoexemplogeraonomedocursorautomaticamente*/ CREATEORREPLACEFUNCTIONretorna_cursor()RETURNSrefcursorAS$$ DECLARE refrefcursor; BEGIN OPENrefFORSELECTcampoFROMtb_teste; RETURNref; END; $$LANGUAGEplpgsql; BEGIN; SELECTretorna_cursor(); retorna_cursor <unnamedportal1> (1row)
108
FETCHALLIN"<unnamedportal1>"; COMMIT; /*Retornandovrioscursores:*/ CREATEORREPLACEFUNCTIONretorna_cursor(refcursor,refcursor) RETURNSSETOFrefcursorAS$$ BEGIN OPEN$1FORSELECT*FROMtabela_1; RETURNNEXT$1; OPEN$2FORSELECT*FROMtabela_2; RETURNNEXT$2; RETURN; END; $$LANGUAGEplpgsql; necessrioestaremumatransaoparapoderusarcursor BEGIN; SELECT*FROMretorna_cursor('a','b'); FETCHALLFROMa; FETCHALLFROMb; COMMIT;
109
34 - Gatilhos Triggers
Triggers so objetos que determinam aes a serem executadas antes ou depois de um evento em uma tabela. Essas aes podem ser disparadas por comando SQL (statement) ou por linha (row). Para se criar um gatilho necessrio que j exista uma funo (strored procedure), a qual ser usada como ao disparada por um evento . E essa funo dever ter o retorno do tipo TRIGGER. Sintaxe:
CREATETRIGGERnome{BEFORE|AFTER}{evento[OR...]} ONtabela[FOR[EACH]{ROW|STATEMENT}] EXECUTEPROCEDUREnome_funo(argumentos);
Detalhes: nome: Nome do trigger a ser criado; BEFORE: A funo ser executada antes do evento; AFTER: A funo ser executada depois do evento; evento: INSERT, UPDATE ou DELETE; tabela: Nome da tabela que ter um gatilho vinculado; ROW: A funo ser disparada para cada linha; STATEMENT: A funo ser disparada apenas uma vez; nome_funo: Nome da funo vinculada ao gatilho.
Quando uma funo PL/pgSQL chamada como um trigger, diversas variveis especiais so criadas automaticamente no bloco de nvel superior. Elas so:
Nome NEW OLD TG_NAME TG_WHEN TG_LEVEL TG_OP TG_RELID TG_RELNAM E TG_NARGS TG_ARGV[] Tipo RECORD RECORD name text text text oid name integer text Descrio Contm a nova linha do banco de dados para operaes do tipo INSERT/UPDATE nos gatilhos no nvel de linha. O valor dessa varivel nulo (NULL) nos gatilhos no nvel de instruo (statement). Contm a antiga linha do banco de dados (antes da modificao), para operaes do tipo UPDATE/DELETE nos gatilhos no nvel de linha. O valor dessa varivel nulo (NULL) nos gatilhos no nvel de instruo (statement). Contm o nome do gatilho disparado. String que contm BEFORE ou AFTER, dependendo da definio do gatilho. String que contm ROW ou STATEMENT, conforme a definio do gatilho. String que contm INSERT, UPDATE, ou DELETE, informando para qual operao o gatilho foi disparado. O ID de objeto da tabela que disparou o gatilho. O nome da tabela que disparou o gatilho. Nmero de argumentos fornecidos ao procedimento de gatilho na instruo CREATE TRIGGER. Os argumentos da instruo CREATE TRIGGER. O contador do ndice comea por 0. ndices invlidos (menor que 0 ou maior ou igual a tg_nargs) resultam em um valor nulo.
110
Exemplo:
/*Criaodatabelaoriginal*/ CREATETEMPTABLEtb_teste( campo1int ); /*Criaodatabeladelogs*/ CREATETEMPTABLEtb_teste_log( codigoserialprimarykey, datadate, usuariovarchar(15), modificacaochar(6) );
/*Criaodafunoqueservinculadaaotrigger*/ CREATEFUNCTIONfunc_log()RETURNStriggerAS$$ BEGIN INSERTINTOtb_teste_log(data,usuario,modificacao)VALUES(now(),user, TG_OP); RETURNNEW; END; $$LANGUAGEplpgsql; /*Criaodotrigger*/ CREATETRIGGERtg_teste_logAFTERINSERTORUPDATEORDELETEONtb_teste FOREACHROWEXECUTEPROCEDUREfunc_log(); /*Inseres,RemoeseModificaesnatabelatb_teste*/ INSERTINTOtb_testeVALUES(1),(2),(3),(4); DELETEFROMtb_testeWHEREcampo1=2ORcampo1=4; UPDATEtb_testeSETcampo1=7WHEREcampo1=3; /*Verificandoatabeladelogs*/ SELECT*FROMtb_teste_log;
111
35 - Personalizando Operadores
O PostgreSQL permite aos usurios criarem operadores personalizados, ao invs de se utilizar funes com o mesmo propsito. No entanto, assim como triggers, cada operador deve ser assimilado a uma funo. Podem ser criado operadores dos tipos unrio esquerdo, unrio direito e binrio. Assim como acontece com funes tambm possvel fazer sobrecarga de operadores, cujo comportamento ir variar conforme o(s) tipo(s) de dados em que for usado. Sintaxe geral da criao de um operador:
CREATEOPERATORsimbolo_operador( PROCEDURE=funcao_atrelada, RIGHTARG=tipo_argumento_direito, LEFTARG=tipo_argumento_esquerdo
Exemplo:
/*Criaodafunoparaooperadorbinrio*/ CREATEORREPLACEFUNCTIONfc_operador_potencia(int,int)RETURNSdoubleprecisionAS$$ SELECTpower($1,$2); $$LANGUAGESQL; /*OperadorBinrio*/ CREATEOPERATOR**( LEFTARG=int, RIGHTARG=int, PROCEDURE=fc_operador_potencia ); /*Criaodafunoparaosoperadoresunrios*/ CREATEORREPLACEFUNCTIONfc_operador_potencia(int)RETURNSdoubleprecisionAS$$ SELECTpower($1,2); $$LANGUAGESQL; /*OperadoresUnrios*/ /*Esquerdo*/ CREATEOPERATOR**( LEFTARG=int, PROCEDURE=fc_operador_potencia ); /*Direito*/ CREATEOPERATOR**( RIGHTARG=int, PROCEDURE=fc_operador_potencia ); /*Utilizaodooperador*/ SELECT2**9;2elevadoa9(operadorbinrio) SELECT3**;3aoquadrado(operadorunrioesquerdo) SELECT**3;3aoquadrado(operadorunriodireito)
No exemplo acima podemos ver a sobrecarga no s da funo, como tambm do prprio operador. O operador quando usado de forma unria simplesmente eleva ao quadrado e na forma binria, o argumento esquerdo elevado ao direito (potncia).
112
36 - Regras - Rules
RULES um sistema de regras (prprio do PostgreSQL, no padro SQL), o qual permite definir uma ao alternativa a ser realizada nos eventos em tabelas ou vises. Grosso modo, uma regra faz com que sejam executados comandos adicionais quando executado um determinado comando em uma determinada tabela. Na verdade, uma regra um mecanismo que transforma comandos. Tal transformao ocorre antes de se executar o comando. Regras no disparam independentemente para cada linha fsica como pode ser definido em um gatilho. Os eventos que podem disparar uma RULE so: SELECT, INSERT, UPDATE e DELETE.
Sintaxe:
CREATE[ORREPLACE]RULEnome_ruleASONeventoTOtabela[WHEREcondio] DO[ALSO|INSTEAD]{NOTHING|comando|(comando;comando...)};
O que vem aps o um evento que pode ser:
DO,
como mostra a sintaxe acima, a ao executada pela regra disparada por
INSTEAD: Indica que o(s) comando(s) deve(m) ser executado(s) ao invs do comando original; ALSO: Indica que o(s) comando(s) deve(m) ser executado(s) em adio ao comando original. Caso ALSO ou INSTEAD no forem especificados, ALSO o padro.; NOTHING: No faz coisa alguma, ou seja, anula o comando dado ou no faz coisa alguma alm do comando; comando: O(s) comando(s) que faz a ao da regra. Os comandos vlidos so SELECT, INSERT, UPDATE, DELETE, ou NOTIFY.
Exemplo:
/*Criaodatabeladefuncionriosquejpertenceramempresa*/ CREATETABLEex_colaboradores( mat_idcharacter(5)NOTNULL, nomecharactervarying(15)NOTNULL, snomecharactervarying(30)NOTNULL, dt_demisdate, CONSTRAINTex_colaboradores_pkeyPRIMARYKEY(mat_id) ); /*CriaodaRULE*/ CREATEORREPLACERULErl_ex_colaboradoresASONDELETETOcolaboradores DOINSERTINTOex_colaboradoresVALUES(OLD.mat_id,OLD.nome,OLD.snome,now()); /*Demissodeumfuncionrio*/ DELETEFROMONLYcolaboradoresWHEREmat_id='00016'; /*Constataodopreenchimentodatabela*/ SELECT*FROMex_colaboradores;
H casos em que em dados eventos, para se proteger os dados, nada deve ser feito:
/*Criaodatabelaaserprotegida*/ CREATETEMPTABLEprotegida(campo1int); /*Inserodevalores*/ INSERTINTOprotegidaVALUES(1),(2),(3),(4),(5),(6),(7); /*Criandoregrasparaprotegercontramodificaesouremoes,respectivamente*/ CREATEORREPLACERULErl_escudo_protegida_updASONUPDATETOprotegidaDOINSTEADNOTHING; CREATEORREPLACERULErl_escudo_protegida_delASONDELETETOprotegidaDOINSTEADNOTHING; /*Tentativas(frustradas)deviolarosdadosinseridosnatabela*/ UPDATEprotegidaSETcampo1=1000WHEREcampo1=1; DELETEFROMprotegidaWHEREcampo1=2;
113
36.1 - Atualizando Views com Rules sabido que uma view nada mais do que uma consulta personalizada de uma tabela, sendo um objeto apenas para visualizao de dados, no sendo possvel inserir e nem apagar dados dela. No entanto, aplicando uma regra isso pode ser possvel desviando o evento da view para a tabela de origem:
/* Criao da view */ CREATE VIEW vw_colaboradores_sp AS SELECT * FROM colaboradores WHERE uf='SP' AND cidade = 'So Paulo'; /* Criao da rule */ CREATE RULE rl_vw_colaboradores_sp AS ON INSERT TO vw_colaboradores_sp DO INSTEAD INSERT INTO colaboradores (mat_id,nome,snome,dt_admis,chefe_direto,uf,cidade) VALUES (NEW.mat_id,NEW.nome,NEW.snome,now(),NEW.chefe_direto,'SP','So Paulo'); /* Insero de um registro na view */ INSERT INTO vw_colaboradores_sp (mat_id,nome,snome,chefe_direto) VALUES ('00051','Escendino','Matos','00001'); /* Verificando o resultado */ SELECT * from vw_colaboradores_sp ;
36.2 - Removendo Rules Para apagar uma RULE necessrio especificar tambm o nome da relao qual est atrelada: Sintaxe:
DROPRULE[IFEXISTS]nome_ruleONrelao[CASCADE|RESTRICT]
Exemplo:
DROPRULErl_vw_colaboradores_spONvw_colaboradores_sp;
36.3 - Rules X Trigggers Rules No possvel usar uma linguagem procedural; Substitui, Adiciona ou desabilita comandos; Ao contrrio de triggers, podem alterar a consulta executada; independente de funes definidas pelo usurio.
Triggers um mecanismo mais complexo, porm muito mais poderoso; Determinam funes a serem executadas antes ou depois de um tipo de evento; Antes de sua criao preciso criar uma funo cujo retorno do tipo trigger e no pode receber parmetros; Pode executar um comando que acione outro trigger;
114
37 - Herana de Tabelas
Assim como em linguagens orientadas a objetos, de maneira similar, h o conceito de herana de tabelas no PostgreSQL. A Tabela-me possui campos comuns a todas as outras que dela se originaro, e as tabelas-filhas, cada uma ter colunas prprias alm das herdadas. O vnculo de uma tabela-me com uma tabela-filha se efetiva com a palavra chave INHERITS. Exemplo:
/*Tabelame*/ CREATETEMPTABLEpessoa( nomevarchar(15), enderecovarchar(30), telefonechar(10) ); /*Tabelasfilhas*/ CREATETEMPTABLEpessoa_juridica( razao_socialvarchar(40), cnpjchar(14) )INHERITS(pessoa); CREATETEMPTABLEpessoa_fisica( sobrenomevarchar(40), cpfchar(11) )INHERITS(pessoa);
Exibio da estrutura de cada tabela:

\dpessoa Coluna nome endereco telefone Tipo character varying(15) character varying(30) character(10)
\dpessoa_juridica Coluna nome endereco telefone razao_social cnpj Tipo character varying(15) character varying(30) character(10) character varying(40) character(14)
\dpessoa_fisica Coluna nome endereco telefone sobrenome cpf Tipo character varying(15) character varying(30) character(10) character varying(40) character(11)
115
37.1 - Inserindo Dados em uma Tabela-Filha Ao se inserir dados em uma tabela-filha, automaticamente eles sero tambm vistos na tabelame, por padro, nos campos que ela possuir em comum com as outras:
INSERTINTOpessoa_fisicaVALUES('Georg','RuadaResistncia, 35','1122223333','Ohm','12345678901'); /*Verificandoosdadosnatabelafilha*/ SELECT*FROMpessoa_fisica; /*Verificandoosdadosnatabelame*/ SELECT*FROMpessoa;
Como pde ser constatado, a princpio, tudo que as filhas tiverem a me tambm ter. Se por acaso em uma coluna de valores nicos comum entre me e filhas, o mesmo valor for inserido tanto na me quanto em uma filha, se no for usado parmetro ONLY antes do nome da tabela-me exibir o mesmo valor mais de uma vez.
/*Visualizandoapenasosresultadosdatabelame*/ SELECT*FROMONLYpessoa;
Alm do SELECT, o parmetro ONLY pode ser usado tambm com DELETE e UPDATE.
116
38 - Array de Colunas
Tambm conhecido como vetor ou matriz um recurso muito til em linguagens de programao e que tambm est disponvel como recurso em colunas de tabelas do PostgreSQL. Para se definir um campo como vetor, adiciona-se colchetes ([]) logo depois do tipo de dado. Exemplo:
CREATETABLEtb_array( campo_array_aint[], campo_array_bvarchar(10)[][][]Umamatrizde3dimenses );
At o momento, se especificar um tamanho fixo para o array ([n]), no far diferena, pois a implementao atual no fora a obdecer o tamanho definido. 38.1 - Inserindo Dados em Arrays Os valores a serem inseridos em um vetor devem ser envoltos por chaves ({}) e separados por vrgulas. Em matrizes multidimensionais devem ter expresses de matriz com dimenses correspondentes. Exemplo:
INSERTINTOtb_arrayVALUES( '{1,2,3,4,5}', '{{"valorX1","valorX2","valorX3"},{"valorY1","valorY2","valorY3"}, {"valorZ1","valorZ2","valorZ3"}}' );
38.2 - Selecionando Dados de Arrays Para selecionarmos os dados de um vetor ou um array definimos sua posio com apenas um par de colchetes:
/*Selecionarasegundaposio*/ SELECTcampo_array_a[2]FROMtb_array;
possvel definir um faixa de valores de um array para consulta, usando como delimitador dois pontos (:):
/*Selecionardaterceiraquintaposio*/ SELECTcampo_array_a[3:5]FROMtb_array;
No caso de matrizes (vetor de mais de uma dimenso), so necessrios 2 pares de colchetes, sendo que o primeiro informa em qual dimenso se encontra e o segundo em que posio:
/*Dimenso3,Posio1*/ SELECTcampo_array_b[3][1]FROMtb_array;
117
39 - Particionamento de Tabelas
Particionar tabelas consiste em dividir uma tabela que recebe um grande volume de dados. Essa diviso feita entre outras tabelas que na verdade so tabelas-filhas cujos dados so recebidos por redirecionamentos conforme uma regra (geralmente faixas de valores). Cada tabela-filha chamada de partio, nesse caso. A tabela-me vazia geralmente, sua existncia serve apenas para representar os dados por inteiro. Importante: O conceito de particionamento aqui agora apresentado no tem nada a ver com o particionamento de discos. Benefcios: Performance melhorada para consultas; Quando consultas ou atualizaes acessam uma grande porcentagem de uma nica partio, o desempenho pode ser melhorado pela vantagem de varredura sequencial daquela partio ao invs de usar um ndice e acessos aleatrios que faz a leitura espalhada por toda a tabela; Grandes cargas e remoes podem ser efetudadas por adies ou remoes de parties. ALTER TABLE muito mais rpido do que em operaes de grande carga. Evita tambm sobrecarga de VACUUM causada por um grande volume de DELETE.
39.1 - Formas de Particionamento do PostgreSQL Range Partitioning
a forma mais utilizada. A tabela particionada dentro de faixas de dados definidas por uma coluna-chave ou um conjunto de colunas. List Partitioning A tabela particionada explicitamente listando que valores chaves aparecem em cada partio. 39.2 - Implementao do Particionamento 1. Crie a tabela-me, de onde as parties iro se originar. Esta tabela no deve ter dados. No defina qualquer restrio do tipo "CHECK", a no ser que pretenda aplic-la igualmente a todas as parties:
CREATETABLEtb_mae(valorint);
2. Crie as tableas-filhas (as parties) que herdaro as propriedades da tabela-me:

CREATETABLEtb_filha_1()INHERITS(tb_mae); CREATETABLEtb_filha_2()INHERITS(tb_mae); CREATETABLEtb_filha_3()INHERITS(tb_mae);
3. Adicione restries s parties para definir os valores chaves permitidos em cada partio:
ALTERTABLEtb_filha_1ADDCONSTRAINTchk_valorCHECK(valorBETWEEN0AND9999); ALTERTABLEtb_filha_2ADDCONSTRAINTchk_valorCHECK(valorBETWEEN10000AND19999); ALTERTABLEtb_filha_3ADDCONSTRAINTchk_valorCHECK(valorBETWEEN20000AND29999);
4. Para cada partio criar um ndice na coluna principal:
118
CREATEINDEXidx_tb_filha_1_valorONtb_filha_1(valor); CREATEINDEXidx_tb_filha_2_valorONtb_filha_2(valor); CREATEINDEXidx_tb_filha_3_valorONtb_filha_3(valor);
5. Verifique se o parmetro "constraint_exclusion" est desabilitado (off) no arquivo postgresql.conf. Caso estiver, as consultas no sero otimizadas como desejado. Com esse parmetro desabilitado, as consultas vasculharo cada uma das parties. 6. Crie uma RULE ou um TRIGGER para fazer o redirecionamento da tabela-me para a tabela-filha conforme o valor citado:
/*I)ComTrigger*/ CriaodaFunoparaoTrigger CREATEORREPLACEFUNCTIONfc_particionamento()RETURNSTRIGGERAS$$ BEGIN IF(NEW.valorBETWEEN0AND9999)THEN INSERTINTOtb_filha_1VALUES(NEW.*); ELSIF(NEW.valorBETWEEN10000AND19999)THEN INSERTINTOtb_filha_2VALUES(NEW.*); ELSIF(NEW.valorBETWEEN20000AND29999)THEN INSERTINTOtb_filha_3VALUES(NEW.*); ELSE RAISEEXCEPTION'ValorInvlido!!!'; ENDIF; RETURNNULL; END; $$LANGUAGEPLPGSQL; CriaodoTriger CREATETRIGGERtg_particionamentoBEFOREINSERTONtb_mae FOREACHROWEXECUTEPROCEDUREfc_particionamento(); /*========================================================*/ /*II)Inseroeverificaodevalores*/ Insero INSERTINTOtb_maeVALUES(55),(100),(20700),(14777); Verificandonatabelaprincipal: SELECT*FROMtb_mae; Verificandoemcadapartioseosvalorescorrespondemfaixa SELECT*FROMtb_filha_1; SELECT*FROMtb_filha_2; SELECT*FROMtb_filha_3; /*========================================================*/ /*III)ComRules*/ EliminaodoTriggeresuaFuno DROPFUNCTIONfc_particionamento()CASCADE; CriaodaRuleparaaPartio1 CREATEORREPLACERULErl_particionamento_1ASONINSERTTOtb_mae WHEREvalor BETWEEN0AND9999DOINSTEAD INSERTINTOtb_filha_1VALUES(NEW.*); CriaodaRuleparaaPartio2 CREATEORREPLACERULErl_particionamento_2ASONINSERTTOtb_mae WHEREvalorBETWEEN10000AND19999DOINSTEAD INSERTINTOtb_filha_2VALUES(NEW.*); 119
CriaodaRuleparaaPartio3 CREATEORREPLACERULErl_particionamento_3ASONINSERTTOtb_maeWHEREvalor 20000AND29999DOINSTEAD INSERTINTOtb_filha_3VALUES(NEW.*); BETWEEN
No exemplo apresentado tanto faz usar TRIGGER ou RULEs, o resultado ser o mesmo (que pode ser conferido conforme o passo II).
120
40 - Administrao de Usurios
40.1 - Roles O PostgreSQL gerencia permisses de acesso usando o conceito de "roles", cujo uso pode para usurios e grupos, dependendo de como um "role" definido. "Roles" podem possuir objetos do banco de dados e determinar privilgios nesses objetos para outros "roles", de modo a controlar quem dever ter acesso a esses objetos. Alm disso, possvel conceder um membro de um "role" para outro, permitindo assim usufruir dos privilgios que foram concedidos a esse outro "role". O conceito de "role" engloba o conceito de usurios e grupos. Em verses do PostgreSQL antes da 8.1, usurios e grupos eram distintos tipos de entidades, mas agora h apenas "roles". Qualquer "role" pode serr um usurio, um grupo ou ambos. "Roles" do banco de dados so conceitualmente completamente separados dos usurios do sistema operacional. Na prtica, pode ser conveniente manter uma correspond ncia, mas isso no requerido. Sintaxe:
CREATEROLEnome_role[[WITH]opes[...]]
Onde opes podem ser:
SUPERUSER | NOSUPERUSER : Determinam se o novo "role" um super usurio, que pode sobrepor todas restries de acesso. O status de super usurio perigoso e deve ser usado somente quando realmente necessrio. Voc mesmo deve ser um super usurio para criar um novo super usurio. Se no for especificado NOSUPERUSER o padro; CREATEDB | NOCREATEDB : Determinam se um "role" pode criar um banco de dados. Se CREATEDB especificado, o "role" poder criar novos bancos de dados. Especificando NOCREATED", que padro, negar ao "role" poder criar banco de dados; CREATEROLE | NOCREATEROLE : Determinam se um "role" pode criar novos "roles". Um "role" com o privilgio CREATEROLE pode tambm alterar ou apagar outros "roles". Se nada for especificado NOCREATEROLE o padro; CREATEUSER | NOCREATEUSER : Essas clusulas so obsoletas, mas ainda so aceitas, So equivalentes a CREATEROLE e NOCREATEROLE, respectivamente; INHERIT | NOINHERIT : Determinam um "role" herdando os privilgios de "roles" que membro. Um "role" com o atributo INHERIT pode automaticamente usar qualquer privilgio do banco de dados que foram concedidos diretamente ou indiretamente a um membro. Sem INHERIT, a adeso em outro "role" apenas garante definir um "role" para outro "role". Se nada for especificado INHERIT o padro"; LOGIN | NOLOGIN : Determinam se um "role" est permitido a entrar no sistema, isso , se o "role" poder ter autorizao a se conectar. Um"role" tento o atributo LOGIN pode ser pensado como um usurio. "Roles" sem esse atributo so teis para gerenciar privilgios de bancos de dados, mas no so como usurios. Se no for especificado NOLOGIN o padro", exceto quando CREATEROLE executado usando o parmetro CREATEUSER; CONNECTION LIMIT connlimit : Se o "role" puder entrar no sistema, isso especifica como muitas conexes concorrentes o "role" pode fazer. O padro "-1", que siginifica: sem limites; [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password' : Configura a senha do "role", sendo apenas para "roles" que tenham o atributo LOGIN, mas pode-se definir para um que no tenha. Se no planeja usar autenticao com senha, pode-se omitir essa opo. Se nenhuma senha for especificada, ser nula e a autenticao sempre falhar para esse usurio. Uma senha nula pode ser opcionalmente ser escrita explicitamente como PASSWORD NULL. ENCRYPTED ou UNENCRYPTED controlam se a senha armazenada encriptada no sistema de catlogos. Se nenhuma for especificada, o comportamento padro determinado pelo parmetro de configurao "password encryptation", no arquivo postgresql.conf. Se a senha apresentada j estiver no formato encriptado MD5, ento ela ser armazenada como , independentemente se ENCRYPTED ou UNENCRYPTED for especificados (desde que o sistema no possa descriptografar a string especificada senha
121
criptografada). Isso permite recarga de senhas criptografadas durante "dump" ou "restore". Clientes mais antigos podem no ter suporte para autenticao com o mecanismo MD5 que preciso para funcionar com senhas que so armazenadas encriptadas; VALID UNTIL 'timestamp' : Configura a data e hora, que aps isso a senha no ser mais aceita. Se essa clusula for omitida a senha ser vlida para sempre; IN ROLE rolename [, ...] : Lista um ou mais "roles" existentes para quais o novo "role" ser imediatamente adicionado como membro. (Note que no h opo para adicionar o novo "role" como um administrador, use o comando GRANT separadamente para isso; IN GROUP rolename [, ...] : Para verses a partir da 8.1 totalmete obsoleta, cujo uso se equivale a "INROLE"; ROLE rolename [, ...] : Lista um ou mais "roles" existentes que so automaticamente adicionado como membros do novo "role". (Faz o papel de um novo grupo); ADMIN rolename [, ...] : como ROLE, mas os nomes de "roles" adicionados ao novo "role" WITH ADMIN, dando-lhes o direito de conceder a adeso neste "role" para outros; USER rolename [, ...] : Seu uso obsoleto, equivale a ROLE; SYSID uid: uma clusula ignorada, porm aceita para compatibilidade com verses anteriores. Exemplos:
/*Criaodeumusurio*/ CREATEROLEusuario1LOGIN; /*Criaodeusuariocomumcomsenhadefinidaem"123"*/ CREATEROLEusuario2LOGINENCRYPTEDPASSWORD'123'; /*Criaodesuperusuriocomsenhadefinidaem"123"*/ CREATEROLEdbmasterLOGINSUPERUSERENCRYPTEDPASSWORD'123'; /*Criaodorolecontiner(grupo)financeiro*/ CREATEROLEfinanceiro; /*Criaodorolemarciadentrodogrupofinanceiro*/ CREATEROLEmarciaLOGINENCRYPTEDPASSWORD'123'INROLEfinanceiro; /*Criaodoroleclaradentrodogrupofinanceiro*/ CREATEROLEclaraLOGINENCRYPTEDPASSWORD'123'INROLEfinanceiro;
40.2 - Apagando Roles Como em outros tipos de objeto, o comando DROP usado para remoo. Porm, se um role possui algum objeto isso no possvel sem antes repassar os objetos de sua propriedade para outro role com o comando REASSIGNOWNEDBYrole_originalTOrole_novo ou mesmo apagando todos objetos de sua propriedade com o comando DROPOWNEDBYrole_... Exemplo:
/*Criaodeumobjetodotipotabela*/ CREATETEMPTABLEtb_1(valorint); /*Atribuiodepropriedadedatabelacriadaparausuario1*/ ALTERTABLEtb_1OWNERTOusuario1; /*Tentativa(frustrada)deremoodoroleusuario1*/ DROPROLEusuario1; /*Passandoaspropriedadesdeusuario1parausuario2@db1*/ REASSIGNOWNEDBYusuario1TOusuario2; /*Tentativa(comsucesso)deremoodoroleusuario1*/ DROPROLEusuario1; /*Removendotodososobjetosdepropriedadedeusuario2semremoverorole*/ DROPOWNEDBYusuario2;
122
40.3 - Grupos Tambm conhecidos como roles continers so como grupos de usurios. So roles que contm outros roles. Exemplos:
/*Rolescontiner*/ CREATEROLEcomercial; CREATEROLEvendas; /*Criaodecontinercom2rolesassimilados*/ CREATEROLEdiretoresWITHROLEclara,marcia; /*Inserindoumroledeloginemumrolecontinter*/ GRANTvendasTOmarcia; /*Removendoumroledelogindeumrolecontinter*/ REVOKEfinanceiroFROMclara; /*Usandoocomando"GRANT"eoparmetro"WITHADMINOPTION" permiteconcederodireitodeadministraodoroleaoutrorole*/ GRANTvendasTOclaraWITHADMINOPTION; /*Inversamentepodemosrevogartaldireito*/ REVOKEADMINOPTIONFORvendasFROMclara; /*Exibindotodososroles*/ \du /*...individualmente*/ \duclara
40.4 - Concedendo ou Revogando Acesso a Objetos Na criao de um objeto, quem cria (o role), passa a ser o dono e tem poderes sem restries. Para que outros roles tenham acesso preciso conceder esse privilgio, que pode ser dado a roles continers, em que seus membros podero usufruir dos mesmos direitos. Os comandos GRANT e REVOKE, respectivamente, concedem ou tiram privilgios, fazem parte da subdiviso DCL (Data Control Language Linguagem de Controle de Dados) que tratam dessa parte da linguagem SQL. Os tipos de privilgios que podem ser concedidos so:
SELECT: Permite selecionar qualquer coluna de uma especificada tabela, viso ou sequ ncia. Tambm permite o uso de COPYTO. Esse privilgio tambm necessrio para referenciar valores existentes em colunas em UPDATE ou DELETE. Para sequncias, este privilgio tambm permite o uso da funo currval; INSERT: Permite inserir uma nova linha em uma tabela especificada. Permite tambm COPYFROM; UPDATE: Permite atualizar qualquer coluna de uma tabela especificada. (Na prtica, qualquer comando de atualizao no trivial requirir privilgio SELECT bem como, uma vez que as colunas da tabela para determinar que linhas atualizar, e/ou calcular novos valores para colunas). SELECT... FORUPDATE e SELECT...FORSHARE tambm requerem esse privilgio, em adio ao privilgio de SELECT. Para sequncias, este privilgio permite o uso das funes nextvale setval; DELETE: Permite deltar uma linha da tabela especificada. (Na prtica, qualquer DELETE no trivial requirir o privilgio SELECT bem como, uma vez que as colunas da tabela tem de referncia para determinar quais as linhas que deseja excluir); REFERENCES: Para criar uma restrio de chave estrangeira, necessrio ter este privilgio em ambas as tabelas referenciadas; TRIGGER: Permite a criao de um gatilho na tabela especificada; CREATE: Para bancos de dados, permite novos esquemas serem criados dentro do banco de dados. Para esquemas, permite novos objetos serem criados dentro do esquema. Para renomear
123
um objeto existente, voc deve possuir o objeto e ter este privilgio para o esquema que o contm. Para "tablespaces", permite tabelas, ndices e arquivos temporrios serem criados dentro do "tablespace", e permite bancos de dados serem criados terem o "tablespace" como padro. (Note que revogando este privilgio no poder alterara localizao dos objetos existentes); CONNECT: Permite ao usurio conectar ao banco de dados especificado. Este privilgio checado no incio da conexo (em adio checa qualquer restries impostas pelo arquivo pg_hba.conf); TEMPORARY | TEMP: Permite tabelas temporrias serem criadas enquanto se usa o banco de dados especificado; EXECUTE: Permite o uso da funo especificada e o uso de quaisquer operadores que so implementados no topo da funo. Este o nico tipo de privilgio que aplicvel a funes. (Sua sintaxe funciona para funes de agregao); USAGE: Para linguagens procedurais, permite o uso de uma linguagem especificada para criao de funes nela. Este o nico privilgio que aplicvel a linguagens procedurais. Para esquemas, permite acesso a objetos contidos no esquema especificado (supondo que os requirimentos prprios de objetos tambm estejam preenchidos). Essencialmente isso permite a quem concedido "procurar" objetos dentro do esquema. Sem essa permisso, ainda possvel ver os nomes dos objetos, por exemplo, consultando as tabelas de sistema. Tambm, aps revogar esta permisso, "backends" existentes devem ter declaraes que j tenham realizado essa pesquisa, ento no uma maneira completamente segura para prevenir o acesso ao objeto. Para sequncias, este privilgio permite o uso das funes currval e nextval; ALL PRIVILEGES: Concede todos os privilgios imediatamente. A palavra-chave PRIVILEGES opcional no PostgreSQL, embora seja requirida pelo SQL padro. Os privilgios requiridos por outros comandos so listados na pgina de referncia do mesmo. Exemplos:
/*Rolecontiner*/ CREATEROLEmastersSUPERUSER; /*Usuriosimples*/ CREATEROLEnewusrNOSUPERUSERLOGINENCRYPTEDPASSWORD'123'; /*Tabelasparateste*/ CREATETEMPTABLEtb_acesso1(valorint); CREATETEMPTABLEtb_acesso2(valorint); CREATETEMPTABLEtb_acesso3(valorint); CREATETEMPTABLEtb_acesso4(valorint); CREATETEMPTABLEtb_acesso5(valorint); /*ConcederdireitodeINSERTem2tabelas*/ GRANTINSERTONtb_acesso1,tb_acesso2TOmasters; /*ConcederdireitodeSELECTparaqualquerum*/ GRANTSELECTONtb_acesso1TOPUBLIC; GRANTmastersTOnewusr; /*Revogandotodososprivilegiosdetodososusuriosem3tabelas*/ REVOKEALLONtb_acesso2,tb_acesso3,tb_acesso4FROMPUBLIC; /*Revogandoodireitodeinserirnatabelatb_acesso5paraosrolesnewusremasters*/ REVOKEINSERTONtb_acesso5FROMnewusr,masters;
124
SELECT UPDATE INSERT DELETE TRUNCATE REFERENCES TRIGGER [,...] ALL [ PRIVILEGES ] [ TABLE ] tablename [, ...] [ GROUP ] rolename
SELECT INSERT UPDATE REFERENCES
( column [, ...] ) [,...] ALL [ PRIVILEGES ] ( column [, ...] ) }
USAGE SELECT UPDATE [,...] ALL [ PRIVILEGES ] [ GROUP ] rolename CREATE GRANT CONNECT TEMPORARY | TEMP [,...] ALL [ PRIVILEGES ] ON DATABASE dbname [, ...] TO [ WITH GRANT OPTION ] SEQUENCE sequencename [, ...]
USAGE ALL [ PRIVILEGES ]
FOREIGN DATA WRAPPER fdwname [, ...]
FOREIGN SERVER servername [, ...]
EXECUTE ALL [ PRIVILEGES ]
FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...]
LANGUAGE langname [, ...]
[ GROUP ] rolename
CREATE USAGE [,...] ALL [ PRIVILEGES ] SCHEMA schemaname [, ...]
CREATE ALL [ PRIVILEGES ]
TABLESPACE tablespacename [, ...]
Role [, ...]
TO
Rolename [, ...]
SELECT UPDATE INSERT DELETE TRUNCATE REFERENCES TRIGGER [,...] ALL [ PRIVILEGES ] [ TABLE ] tablename [, ...] [ GROUP ] rolename
SELECT INSERT UPDATE REFERENCES
( column [, ...] ) [,...] ALL [ PRIVILEGES ] ( column [, ...] ) }
USAGE SELECT UPDATE [,...] ALL [ PRIVILEGES ] [ GROUP ] rolename REVOKE [GRANT OPTION FOR] CREATE CONNECT TEMPORARY | TEMP [,...] ALL [ PRIVILEGES ] ON DATABASE dbname [, ...] | RESTRICT] FROM [CASCADE SEQUENCE sequencename [, ...]
FOREIGN DATA WRAPPER fdwname [, ...]
FOREIGN SERVER servername [, ...]
EXECUTE ALL [ PRIVILEGES ]
FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...]
LANGUAGE langname [, ...]
[ GROUP ] rolename
CREATE USAGE [,...] ALL [ PRIVILEGES ] SCHEMA schemaname [, ...]
CREATE ALL [ PRIVILEGES ]
TABLESPACE tablespacename [, ...]
REVOKE [ADMIN OPTION FOR]
role [, ...]
FROM
rolename [, ...]
40.5 - Verificando os Privilgios de Objetos H duas maneiras simples e equivalentes para se consultar os privilgios que roles tm sobre um determinado objeto:
\dpobjeto
(pode ser interpretado como display privileges; exibir privilgios) ou

\zobjeto
(seu efeito exatamente igual ao anterior) Exemplo:

GRANTALLONcolaboradoresTOaluno; GRANTSELECTONcolaboradoresTOPUBLIC; \dpcolaboradores
Produziu a seguinte sada:

\dpcolaboradores Accessprivileges Schema|Name|Type|Accessprivileges|Columnaccess privileges ++++ public|colaboradores|table|postgres=arwdDxt/postgres| :aluno=arwdDxt/postgres :=r/postgres
No exemplo postgres, que por padro role de administrador tem todos os privilgios, assim como aluno, cujos privigios foram concedidos por postgres e PUBLIC tem acesso de somente leitura, ou seja, s pode fazer consultas (SELECT). Abaixo esto maiores informaes e siglas vistas no exemplo: rolename=xxxx -- privileges granted to a role =xxxx -- privileges granted to PUBLIC r -- SELECT ("read": leitura) w -- UPDATE ("write": escrita) a -- INSERT ("append": acres) d -- DELETE D -- TRUNCATE x -- REFERENCES t -- TRIGGER X -- EXECUTE U -- USAGE C -- CREATE c -- CONNECT T -- TEMPORARY arwdDxt -- ALL PRIVILEGES (Todos os privilgios para tabelas, varia para outros objetos) * -- opo de concesso de privilgio anterior /yyyy -- role que concedeu esse privilgio nome_de_role=xxxx -- Privilgios concedidos a um usurio ou grupo =xxxx -- Privilgios concedidos para PUBLIC
127
40.6 - Concedendo ou Revogando Privilgios em Colunas Antes da verso 8.4 s era possvel fazer o controle de acesso de forma mais granular a uma tabela criando uma view especificando as colunas permitidas. Agora pode dar ou tirar privilgios da seguinte forma:
GRANTSELECT(coluna1,coluna2,...)ONtabelaTOrole;
Para o exemplo prtico a criao de role:

CREATEROLElimitadoLOGINNOSUPERUSER;
S ter acesso s colunas nome e snome, sendo de somente leitura da tabela colaboradores:
GRANTSELECT(nome,snome)ONcolaboradoresTOlimitado;
Visualizao dos privilgios:

\zcolaboradores Accessprivileges Schema|Name|Type|Accessprivileges|Columnaccess privileges ++++ public|colaboradores|table|postgres=arwdDxt/postgres|nome: :aluno=arwdDxt/postgres:limitado=r/postgres :=r/postgres:snome: :limitado=r/postgres
De forma anloga ser revogado (retirado) o acesso coluna snome:

REVOKESELECT(snome)ONcolaboradoresFROMlimitado;
Visualizao dos privilgios novamente:

\zcolaboradores Accessprivileges Schema|Name|Type|Accessprivileges|Columnaccessprivileges ++++ public|colaboradores|table|postgres=arwdDxt/postgres|nome: :aluno=arwdDxt/postgres: limitado=r/postgres :=r/postgres
128
41 - Dblink Acessando um Outro Banco de Dados

DBlink um modulo que suporta conexes a outros bancos de dados PostgreSQL dentro de uma sesso de banco de dados. Sinataxe: Para consultas:
SELECT*FROMdblink('dbname=banco_de_dadoshostaddr=endereo_de_hostuser=usurio password=senhaport=n_da_porta',consulta_no_banco)AStabela(campo1tipo,campo2tipo,...);
Para execues:
SELECTdblink_exec('dbname=banco_de_dadoshostaddr=endereo_de_hostuser=usurio password=senhaport=n_da_porta',comando_sql);
Instalao: Como o aprendizado deste curso baseado na distribuio Debian, usaremos suas ferramentas. E primeiramente preciso saber qual o nome do pacote que contm o DBlink:
aptcachesearchdblink
De posse do valor retornado:

aptgetinstallpostgresqlcontrib
Agora necessrio saber onde esto os arquivos referentes ao DBlink:

dpkgLpostgresqlcontrib|grepdblink.sql /usr/share/postgresql/8.4/contrib/dblink.sql /usr/share/postgresql/8.43/contrib/uninstall_dblink.sql
Sendo que, ao ler o nome dos arquivos, fica bvio constatar que o primeiro instala e o segundo desinstala. A instalao do mdulo:
psqldtemplate1Ualunof/usr/share/postgresql/8.4/contrib/dblink.sql
Exemplo:
/*Criaodosbancosdedados*/ CREATEDATABASEbanco1; CREATEDATABASEbanco2; /*Conectandoaobanco1*/ \cbanco1 /*Criaodeumatabeladeexemploeinserodedados*/ CREATETABLEtb1(campoint); INSERTINTOtb1VALUES(1),(2),(3),(4),(5),(6),(7); /*Conectandoaobanco2*/ \cbanco2
129
/*Acessandodadosdatabelatb1queestembanco1*/ SELECT*FROMdblink('dbname=banco1hostaddr=127.0.0.1user=alunopassword=123 port=5432','SELECT*FROMtb1')AStb2(campoint); /*Inserindodadosnatabelatb1queestembanco1*/ SELECTdblink_exec('dbname=banco1hostaddr=127.0.0.1user=alunopassword=123 port=5432','INSERTINTOtb1VALUES(8),(9)');
130
42 - VACUUM Alocando Espaos sem Desperdcio

O comando VACUUM recupera as reas de armazenamento ocupadas por registros mortos. Em uma operao normal do PostgreSQL, registros que so deletados ou transformados em obsoletos por um UPDATE no so fisicamente removidos de suas tabelas, eles permanecem presentes at o comando VACUUM ser feito. Portanto, necessrio executar o VACCUM periodicamente, especialmente em tabelas frequentemente atualizadas. O comando VACCUMANALIZE alm do comportamento padro de VACUUM faz uma anlise para cada tabela selecionada. Essa uma forma de combinao acessvel para scripts de manuteno de rotina. Veja adiante sobre o comando ANALIZE para mais detalhes sobre como ele processa. VACUUM (sem o parmetro FULL) apenas recupera o espao e o torna disponvel para reutilizao. Essa forma de comando pode operar em paralelo com leituras e escritas normais da tabela, devido ao fato de no haver uma trava ("lock") exclusiva. J VACUUMFULL faz processamento mais extensivo, incluindo mover registros atravs de blocos para tentar compactar a tabela para o mnimo nmero blocos de disco. Essa forma muito lenta e requer uma trava exclusiva em cada tabela enquanto processada. Sintaxe:
VACUUM[FULL][FREEZE][VERBOSE][table] VACUUM[FULL][FREEZE][VERBOSE]ANALYZE[tabela[(coluna[,...])]]
Parmetros:
FULL FREEZE VERBOSE
Recupera mais espao, mas leva mais tempo e requer exclusividade de trava ("lock") de tabela. Provoca o congelamento dos registros. um parmetro obsoleto e ser removido num futuro lanamento. Imprime na tela a atividade de relatrio de "VACUUM" para cada tabela. Pode ser usado para ajudar a determinar configuraes para "max_fsm_pages", "max_fsm_relations" e "default_statistics_targets" que se encontram no arquivo "postgresql.conf". Atualiza estatsticas usadas pelo planejador para determinar a o modo mais eficiente para executar uma consulta.
ANALYZE
Exemplo:
VACUUMANALYZEVERBOSEcolaboradores;
131
43 - ANALYZE - Coleta de Estatsticas Sobre uma Base de Dados

O comando ANALYSE coleta estatsticas sobre o contedo de tabelas no banco de dadoes e guarda os resultados na tabela de sistema "pg_statistic". Subsequencianmente, o planejador de consultas usa essas estatsticas para ajudar a determinar o plano de execuo mais eficiente para "queries". Sem parmetros, ANALYSE examina todas tabelas no banco de dados corrente. Com um parmetro, examina apenas a tabela determinada. Com o parmetro VERBOSE, habilita a exibio de mensagens de progressso. Sintaxe:
ANALYZE[VERBOSE][tabela[(coluna[,...])]]
Exemplo:
ANALYZEVERBOSEcolaboradores;
Obs.: Na configurao padro do PostgreSQL, o daemon de "auto-vacuum" cuida da anlise automtica de tabelas quando so carregadas pela primeira vez com dados e como elas mudam toda operao regular. Quando "autovacuum" desabilitado, uma boa prtica executar ANALYZE periodicamente, ou apenas aps fazer grandes mudanas no contedo da tabela. Estatsticas ajudam o planejador a escolher o mais apropriado plano de consultas e assim melhorar sua velocidade de processamento. Uma estratgia comum rodar o ANALYZE uma vez ao dia durante um perodo de pouco uso.
132
44 - EXPLAIN
Esse comando exibe o plano de execuo que o planejador PostgreSQL gera para cada instruo. O plano de execuo exibe como a(s) tabela(s) referenciada(s) por uma instruo ser examinada por um exame sequencial plano, exame de ndices, etc. Se mltiplas tabelas forem referenciadas, quais algoritmos de juno sero usados para unir linhas necessrias de cada tabela envolvida. Sintaxe:
EXPLAIN[ANALYZE][VERBOSE]instruo
Parmetros:
ANALIZE
Executa o comando e mostra o tempo real de execuo
VERBOSE Mostra a representao interna completa da rvore de planos, ao invs de apenas um resumo. Usualmente essa opo til apenas para propsitos de "debug" especializados. instruo Qualquer instruo SELECT, INSERT, UPDATE, DELETE, VALUES, EXECUTE ou DECLARE, cujo plano de execuo deeja visualizar.
Exemplo:
EXPLAINANALYZESELECT*FROMcolaboradores;
133
45 - Backup & Restore

Como tudo que tem dados, bancos de dados PostgreSQL devem ser "backupeados" regularmente. Fundalmentalmente, h trs diferentes tipos de tcnicas para se fazer backup do PostgreSQL: - SQL dump: on line, com pg_dump ou pg_dumpall; - Em nvel de sistema de arquivos: off line, com tar, cp, gzip, bzip2, etc; - Arquivamento contnuo: online, utilizando Point In Time Recovery. Backups on line no param o servio do banco, mas reduz a performance. J os backups off line necessitam que o servio do banco esteja parado. Cada um tem seus pontos positivos e negativos, que sero discutidos. altamente recomendvel efetuar backups em perodos que a utilizao seja a menor possvel.
45.1 - Backup SQL
45.1.1 - pg_dump A idia por trs deste mtodo de "dump" gerar um arquivo de texto com comandos SQL, que quando restaurado recriar o banco de dados no mesmo estado com era quando o backup foi efetudado. O PostgreSQL disponibiliza um utilitrio, para ser usado no shell, o bpg_dump. O pg_dump uma aplicao cliente, que permite fazer backups remotamente. E seu uso bsico :
pg_dumpnome_do_banco>arquivo_de_saida
Por padro, o pg_dump faz backups em formato texto. H a possibilidade de se fazer backups em formato binrio e comprimido da seguinte forma:
pg_dumpFcfarquivo.dumpnome_do_banco
45.1.1.1 - Restaurando do pg_dump e o Utilitrio pg_restore Os arquivos de texto criados pelo pg_dump podem ser interpretados pelo utilitrio psql. A forma geral de comando para restaurar :
psqlnome_do_banco<arquivo_de_entrada
Onde "arquivo_de_entrada" o arquivo de sada usado pelo comando pg_dump. Obs.: Ele no recria o banco de dados com o comando CREATEDATABASE..., caso o banco no exista mais e for preciso restaurar atravs do arquivo gerado pelo pg_dump, antes pode recriar o banco com o utilitrio shell, cuja funo equivale ao comando SQL CREATEDATABASE:
createdbnome_do_banco
No entanto o mtodo acima vlido para dumps feitos em modo texto. Para restaurar arquivos binrios compactados pelo pg_dump utiliza-se o programa pg_restore:
pg_restoreFcarquivo.dumpdnome_do_banco
134
45.1.2 - pg_dumpall O utilitrio pg_dump faz cpia de segurana de um nico banco de dados por vez, e no guarda informaes sobre roles ou tablespaces, pois so objetos que no dependem de um banco. Para se fazer backups de todo contedo de um cluster de um cluster, o utilitrio pg_dumpall fornecido. O utilitrio pg_dumpall efetua backups de cada base de dados que um cluster contm e tambm preserva dados mais abrangentes como roles e definies de tablespaces. No faz backup de BLOBs. Seu uso bsico :
pg_dumpall>arquivo
O backup resultante pode ser restaurado pelo utilitrio psql da seguinte forma:
psqlfarquivopostgres
(Atualmente, pode ser especificado algum nome de base de dados existente para iniciar o cluster dela, mas se est recarregando as informaes em um cluster vazio, ento o nome "postgres" deve ser usado). 45.2 - Backups para Bancos de Dados Grandes H a possibilidade de um arquivo de backup ser maior do que o mximo tamanho permitido para o sistema de arquivos (file system) utilizado. Levando-se em conta que o utilitrio pg_dump pode imprimir sua sada para a tela, possvel usar ferramentas padro Unix para contornar esse possvel problema. Aplicando o uso do pipe "|" e utilitrios com compresso mais poderosa do que o formato binrio comprimido do pg_dump. Exemplo: - Comprimindo
pg_dumpcurso|bzip29>banco.bz2
No comando acima, o pg_dump enviaria sua sada para a tela, mas o pipe redireciona para o bzip2 (utilitrio de compresso), que gera o arquivo "banco.bz2". - Restaurando
bzip2dcbanco.bz2|psqlcurso
O arquivo descompactado e mandaria sua sada para a tela, mas novamente o pipe redireciona, desta vez para o psql que especifica para qual base de dados. Quando s a compresso no resolve podemos dividir o arquivo resultante em vrios pedaos com o comando split:
pg_dumpnome_do_banco|splitb10marquivo
No comando acima, o pipe repassa o que seria exibido em tela para o comando split que dividir o backup em arquivos de no mximo 10MB A restaurao feita da seguinte maneira:
catarquivo*|psqlnome_do_banco
135
45.3 - Backup em Nvel de Sistema de Arquivos Uma estratgia alternativa de backup copiar diretamente os arquivos que o PostgresSQL usa para armazenar os dados da base. Para isso deve-se saber onde esto tais arquivos. No caso da instalao via apt-get do postgresql desta verso est em "/var/lib/postgresql/8.4/main". Localizao obtida no arquivo postgresql.conf, na configurao de nome "data_directory". Exemplo usando o utilitrio tar com compresso bzip2:
tarcvjfcluster.tar.bz2/var/lib/postgresql/8.4/main/exclude=pg_xlog/*
45.4 - Arquivamento Contnuo 45.4.1 - WAL (Write-Ahead-Log: Registro Prvio de Escrita) Write-Ahead Loggin (WAL) o mtodo padro para garantir integridade de dados. O conceito central do WAL que as mudanas nos arquivos de dados (onde tabelas e ndices esto) devem ser gravadas somente depois que tenham sido escritas em log, ou seja, as alteraes sero efetivadas fisicamente (nos arquivos de dados). Os arquivos de log de transao so conhecidos como xlog e esto armazenados em em subdiretrio do diretrio de dados do postgresql (data_directory no arquivo de configurao postgresql.conf ou a varivel de ambiente PGDATA). Ento o caminho para os logs de transao em sistemas Linux/Unix PGDATA/pg_xlog ou em sistemas Windows: %PGDATA%\pg_xlog, isso se, as variveis de ambientes foram devidamente configuradas. Os nomes dos arquivos de log so nmeros hexadecimais, sendo que os oito primeiros dgitos so referentes sua cronologia. Exemplo: 000000030000000000000012. Em caso de crash (falha) de banco de dados possvel recuperar as transaes registradas, sendo que para aplicar usa-se rolling forward e para desfazer rolling back. O WAL faz com que no seja preciso sincronizar a rea de cache em memria da base de dados com os arquivos fsicos a cada efetivao de uma transao. No arquivo postgresql.conf, os parmetros de configurao do WAL so:
Parmetro Definies Ativado (on), o servidor PostgreSQL tentar checar se as atualizaes esto fisicamente escritas em disco, mediante chamadas de sistema fsync() ou vrios mtodos equivalentes (veja wal_sync_method). Isso assegura que a base de dados pode ser recuperada para um estado consistente aps um crash de hardware ou de sistema operacional. No entanto, o uso de fsync resulta em perda de performance: quando uma transao efetivada, o PostgreSQL deve esperar pelo sistema operacional para liberar o WAL para o disco. Quando o fsync est desabilitado (off), permite ao sistema operacional fazer melhores "buffering", ordenao e temporizao de escrita. Resulta em uma insignificante melhora de performance, mas se houver um crash de sistema, os resultados das poucas ltimas transaes efetivadas sero perdidas parcialmente ou completamente. Define se a efetivao da transao ir esperar por registros WAL serem escritos no disco antes do comando retornar uma indicao de sucesso para o cliente. Se for desativado (off), poder haver um atraso durante o momento de sucesso que reportado ao cliente e quando a transao realmente garantida para ser segura contra um crash de servidor. Mtodo usado para forar atualizaes do WAL para o disco. Se fsync estiver desativado esta configurao irrelevante, j que as atualizaes no sero todas foradas a sair. Os possveis valores so: open_datasync (grava arquivos WAL com a opo open() O_DSYNC); fdatasync (chama fdatasync() a cada efetivao); fsync_writethrough (chama fsync() a cada efetivao, forando write-through de qualquer cache de disco); fsync (chama fsync() a cada efetivao); Padro Descrio
fsync (boolean)
on
synchronous_commit (boolean)
on
wal_sync_method (string)
fsync
136
open_sync (grava arquivos WAL com a opo open() O_SYNC). Obs.: Nem todas as opes esto disponveis para todas as plataformas. full_page_writes (boolean) on Ativado este parmetro faz com que o servidor PostgreSQL escreva o contedo inteiro de cada pgina de disco para o WAL durante a primeira modificao de pgina depois de um checkpoint. O total de memria usada na memria compartilhada (shared memory) para dados do WAL. Aumentar esse valor pode significar maior desempenho para bases de dados que tm transaes grandes, no entanto, faz com que o PostgreSQL requeira mais memria compartilhada do que as configuraes padro do sistema operacional oferecem. Define o tempo entre ciclos de atividade para o escritor do WAL. Tempo entre a escrita de uma efetivao para o buffer do WAL e liberao do buffer para o disco em microsegundos. Configurando com um valor diferente de zero pode permitir mltiplas transaes para serem efetivadas com apenas uma chamada de sistema fsync(), se a carga de sistema alta o suficiente de modo que transaes adicionais fiquem prontas para efetivar dentro do intervalo especificado. Nmero mmino de transaes simultneas abertas antes da requisio de tempo de commit_delay. Um valor maior torna possvel pelo menos uma ou outra transao fique pronta para efetivar durante o intervalo de tempo.
wal_buffers wal_writer_delay (integer)
64kB 200ms
commit_delay (integer)
commit_siblings (integer) Checkpoints
checkpoint_segments (integer) checkpoint_timeout (integer) checkpoint_completion_target (floating point) checkpoint_warning (integer) Arquivamento
3 5min 0.5 30s
Nmero mximo de segmentos de arquivos de log entre checkpoints do WAL (cada segmento tem 16 MB). Aumentando esse parmetro pode aumentar tambm o tempo necessrio para um "crash recovery". Intervalo entre checkpoints automticos do WAL (por padrao em segundos). Varia de 30s a 1h. Especifica a durao do alvo de checkpoints, como uma frao de intervalo de verificao. Escreve uma mensagem no log do servidor se verificaes causadas pelo preenchimento de arquivos de checkpoint acontecer mais prximo do que este nmero de segundos. Zero desabilita a mensagem.
archive_mode (boolean)
off
Quando habilitado, segmentos WAL completados so copiados para locais determinados (ver archive_command). Comando utilizado para arquivar um segmento do WAL. Na string podem ser usados os seguintes artifcios: %p = O caminho do arquivo WAL a ser arquivado %f = Nome do arquivo WAL
archive_command (string)
importante que o comando retorne zero em caso de sucesso. Exemplos: archive_command = cp "%p" /caminho/do/diretorio/de/arquivamento/"%f" archive_command = copy "%p" "C:\\caminho\\do\\diretorio\\de\\arquivamento\%f" # Windows
archive_timeout (integer)
Tempo mximo em segundos entre arquivamentos do WAL, ou seja, determina o intervalo de reciclagem do arquivo de log, forando o arquivamento. Zero desabilita. Caso tiver um valor muito baixo comprometer a performance do banco de dados.
137
45.4.1.1 - Benefcios do WAL Reduo significativa do nmero de escritas em disco, pois quando uma transao efetivada s preciso descarregar o arquivo de registro ao invs de todos os arquivos de dados modificados pela transao; Consistncia das pginas de dados, pois sem o WAL no possvel garantir consistncia em caso de queda de energia. E qualquer queda durante a escrita poderia resultar em: 1. Linhas de ndice apontando para linhas que no existem; 2. Perda de linhas de ndice nas operaes de quebra de pgina (split); 3. Tabelas ou ndices corrompidos devidos a dados parcialmente gravados. Alta disponibilidade com a cpia dos arquivos de log para outra mquina: Log shipping.
45.4.1.2 - PITR - Point In Time Recovery Com o PITR possvel restaurar o estado do cluster de um tempo passado. Durante todo o tempo, o PostgreSQL mantm o WAL no subdiretrio px_log do diretrio do cluster, o qual contm cada mudana para os arquivos da base de dados. Esse log existe principalmente contra falhas de sistema, pois se isso acontecer, o banco pode ser restaurado consistentemente refazendo as entradas efetuadas desde o ltimo checkpoint. Mas, a existncia de logs torna possvel outra estratgia para se fazer backup de bancos de dados, pode-se combinar backup em nvel de sistema de arquivos com backups dos arquivos do WAL. Se houver necessidade de recuperao, restauram-se o backup, para o momento atual. Obs.: ndices que no sejam B-Tree no esto presentes no WAL, se o banco em questo tiver ndices diferentes desse tipo deve ser reindexado, utilizando o comando REINDEX, aps o trmino do PITR. 45.4.1.2.1 - Configurao do Arquivamento Primeiramente deve ser criado ou determinado o diretrio de backup para onde iro os arquivos de log. No exemplo ser usado /srv/wal_backup:
mkdirp/srv/wal_backup chgrpRpostgres/srv/wal_backup chmod -R 775 /srv/wal_backup #Criaododiretrio #Definindoogrupodeusuriosdodiretrio # Permisso a membros do grupo para escrita
No arquivo postgresql.conf fazer as seguintes modificaes:

archive_mode = on
Habilita o arquivamento;
achive_command = 'cp %p /srv/wal_backup/%f'
Aps as modificaes serem feitas preciso reiniciar o servio do PostgreSQL para reconhecer essas novas configuraes:
/etc/init.d/postgresqlrestart
Obs.: Se o diretrio px_xlog for perdido, os dados dos arquivos que no foram arquivados sero perdidos.
138
45.4.1.2.2 - Fazendo um Backup da Base O procedimento para fazer o backup base relativamente simples: 1. Verifique se o WAL archiving est abilitado; 2. Conecte a um banco de dados como super usurio e d o comando:
SELECTpg_start_backup(rotulo);
Onde "rotulo" qualquer string escolhida para identificar unicamente essa operao de backup (uma boa prtica usar o caminho inteiro que se pretende destinar o arquivo de backup). A funo pg_start_backup cria um arquivo de rtulo, cujo nome "backup_label", no diretrio do cluster como informaes sobre o backup ou ento cria um arquivo de extenso .backup no subdiretrio pg_xlog. No importa em qual banco de dados est conectado para dar esse comando. A funo pg_start_backup pode levar muito tempo para finalizar, devido ao fato de ele realizar um checkpoint, e as requisies de I/O (input/output = entrada/sada) para um checkpoint sero distribudas ao longo de um perodo de tempo significativo, por padro metade do intervalo de inter-checkpoint (ver configurao "checkpoint_completion_target"). Normalmente isso o desejado porque minimaliza o impacto no processamento de queries. Se desejar inicializar o backup assim que possvel, execute o comando CHECKPOINT (que realiza um checkpoint o mais rpido possvel) e em seguida, imediatamente execute pg_start_backup. A funo pg_start_backup ter pouco para fazer, pois no vai demorar. 3. Realize o backup em nvel de sistema de arquivo no cluster usando qualquer ferramenta conveniente como tar ou cpio. No necessrio e nem desejvel parar o banco de dados enquanto se faz isso. Por exemplo, d o comando no shell do sistema operacional:
tarcvjfcluster.tar.bz2/var/lib/postgresql/8.4/main/exclude=pg_xlog/*
Obs.: O comando acima criar o backup no diretrio corrente, para saber em qual diretrio est d o comando pwd no shell. 4. Aps o backup em nvel de sistema de arquivos conecte a um banco de dados d o comando:
SELECTpg_stop_backup();
Isso terminar o modo de backup e realizar uma mudana automtica para o prximo segmento WAL e tambm gerar um histrico de backup na rea de arquivamento. O intervalo entre pg_start_backup e pg_stop_backup pode ser demorado.
139
45.4.1.2.3 - Recuperao PITR O pior aconteceu!!! preciso recuperar o backup! Procedimento: 1. Pare o servio, se ele estiver rodando;
/etc/init.d/postgresqlstop
ou
pg_ctlstop
2. Se houver espao para fazer, copie todo o diretrio de cluster e qualquer tablespace para uma localidade temporria, pois pode precisar depois. Caso no houver espao, no mnimo copiar o contedo do diretrio pg_xlog, pois pode ter logs que no foram arquivados antes do sistema cair;
cpa$PGDATA/pg_xlog/*/srv/wal_backup
3. Apague todos arquivos e diretrios que estiverem dentro do diretrio do cluster e sob a raiz de qualquer tablespace que estiver em uso;
rmfr$PG_DATA
4. Restaure os arquivos de seu backup. importante ter assegurar as propriedades corretas de permisso para o usurio do sistema do banco de dados (geralmente o usurio postgres) e no com as permisses de root. Caso usar tablespaces, deve verificar se os links simblicos em $PGDATA/tblspc/ foram corretamente restaurados;
tarxvjfcluster.tar.bz2
5. Apague qualquer arquivo presente em $PGDATA/pg_xlog, pois se originam da restaurao do backup e provavelmente so obsoletos. Se no tiver o contedo de de pg_xlog, ele deve ser recriado, tendo cuidado ao assegurar que foi restabelecido como link simblico, se foi criado dessa forma antes.
mkdirp$PGDATA/pg_xlog/archive_status
6. Caso tenha armazenado os arquivos de segmentos WAL (ver passo 2), devem ser copiados em $PGDATA/pg_xlog. melhor copi-los e no mov-los, pois assim ainda ter os arquivos no modificados se um problema ocorrer, para evitar comear de novo.
cpa/srv/wal_backup/*$PGDATA/pg_xlog
7. Criar o arquivo recovery.conf no diretrio de cluster. Na instalao padro h um modelo em "/usr/share/postgresql/8.4/recovery.conf.sample".

cp/usr/share/postgresql/8.4/recovery.conf.sample$PGDATA/recovery.conf
Obs.: aconselhvel fazer uma modificao temporria no arquivo pg_hba.conf para prevenir que usurios comuns se conectem at tiver certeza que a restaurao funcionou.
140
45.4.1.2.4 - Configurao de Restaurao (recovery.conf) Estas configuraes s podem ser feitas no arquivo recovery.conf, e aplicveis somente durante o processo de recovery (restaurao). restore_command (string) o comando para executar a restaurao de WAL arquivados e nica configurao obrigatria.
restore_command = cp /srv/wal_backup/%f "%p"
um
recovery_end_command (string) Define um comando que ser executado assim que a restauralo acabou. Seu propsito prover mecanismo para fazer limpeza de arquivos aps uma replicao ou recuperao. recovery_target_time (timestamp) Determina at que data e hora a recuperao deve ser feita, o padro recuperar at o fim dos logs WAL.
recovery_target_xid (string) Indica at que ID de transao a recuperao ser feita
recovery_target_inclusive (boolean) Determina se vai parar logo aps o alvo de restaurao (true) ou pouco antes dele (false).
recovery_target_timeline (string)
Determina a recuperao de uma determinada cronologia. Por padro recupera ao longo da cronologia que foi a cronologia atual quando foi feito o backup da base. 45.4.2 - Cronologias Timelines A capacidade de restaurar uma base de dados a um ponto anterior no tempo cria algumas complexidades que so semelhantes a histrias de fico cientfica sobre viagem no tempo e universos paralelos. Num caso real de banco de dados, talvez voc apagou uma tabela crtica s 17:15 na tera-feira, mas no percebeu o erro at quarta-feira ao meio-dia. Tranqilamente, voc pega seu backup e o restaura para 17:14 de tera-feira. Est rodando! Nessa histria de universo de banco de dados, voc nunca apagou toda a tabela. Mas suponha que depois voc percebeu que aps tudo isso no foi uma grande idia, e gostaria de voltar para algum tempo na manh de quarta-feira na histria original. Voc no poder fazer isso se, enquanto seu banco de dados estiver rodando, ele sobrescreveu algum dos segmentos seriais do WAL que levou at o momento que voc deseja agora retornar. Ento voc realmente quer distinguir as sries de gravaes do WAL geradas aps terminar o PITR daqueles que foram gerados na histria original do banco de dados. Para lidar com esses problemas o PostgreSQL tem a noo de timelines (cronologias). Sempre que um arquivo de restaurao completado, uma nova timeline criada para identificar as sries das gravaes de WAL geradas aps essa restaurao. O nmero timeline ID parte dos nomes de arquivos de segmentos WAL, e ento uma nova timeline no vai sobrescrever dados gerados por timelines anteriores. de fato possvel armazenar muitas cronologias diferentes. Embora possa parecer um recurso intil, muitas vezes salva vidas. Considere a situao onde voc no tenha certeza que ponto no tempo recuperar, e ento tem que fazer muitos PITRs por tentativa e erro at encontrar o melhor lugar para ramificar a partir do histrico antigo. Sem cronologias esse processo logo geraria uma baguna incontrolvel. Com cronologias, voc pode recuperar para qualquer estado anterior, incluindo estados de
141
ramificaes de cronologias que voc mais tarde abandonou. Cada vez que uma nova cronologia criada, o PostgreSQL cria um arquivo de "histrico da linha do tempo" que exibe que cronologia ramifica de onde e quando. Esses arquivos de histricos so necessrios para permitir ao sistema pegar os arquivos certos de segmento WAL quando se recupera de um arquivo que contm vrias cronologias. Portanto, elas so armazenadas na rea de arquivo WAL assim como arquivos de segmentos do WAL. Os histricos so apenas pequenos arquivos de texto, ento no custa muito e adequado mantlos indefinidamente (ao contrrio dos arquivos de segmento que so muito grandes). Se quiser, voc pode adicionar comentrios ao arquivo de histrico para fazer suas prprias anotaes sobre como e porque esta cronologia foi criada. Tais comentrios sero especialmente importantes quando voc tem um emaranhado de cronologias diferentes como resultado de experimentao. O comportamento padro de uma restaurao recuperar ao longo da mesma cronologia que era quando o backup da base de dados foi tomado. Se desejar recuperar dentro da mesma cronologia filha (isto , voc quer retornar para algum estado que foi-se gerado aps uma tentativa de restaurao), voc precisa especificar o ID da cronologia alvo no arquivo recovery.conf (mais especificamente, no parmetro recovery_target_timeline). Voc no poder recuperar dentro de cronologias que ramificaram mais cedo do que o backup da base. 45.4.2.1 - Recuperao PITR (continuao) 8. Inicie o servidor. O servidor entrar no modo de recuperao e far a leitura dos arquivos WAL armazenados que precisa. Se a recuperao for terminada por causa de um erro externo, o servidor pode simplesmente ser reiniciado e vai continuar a recuperao. Aps a concluso do processo de recuperao, o servidor renomear o arquivo recovery.conf para recovery.done (para prevenir entrar no modo de recuperao acidentalmente em caso de falha posterior) e ento iniciar as operaes normais de banco de dados. 9. Examine o contedo do banco de dados para garantir que voc tenha recuperado para onde voc quer estar. Se no, volte ao passo 1. Se tudo estiver bem, permita conexo dos usurios novamente restaurando o arquivo pg_hba.conf ao normal. A parte fundamental de tudo isso configurar o arquivo de comando de recuperao que descreve como voc quer restaurar e at quando o recovery deve rodar.
142
46 - Configuraes de Memria e Ajustes de Desempenho

Muitas pessoas erroneamente pensam que o PostgreSQL um SGBD muito lento. No entanto, o que acontece que por questes de compatibilidade com configuraes de hardware modestas, os ajustes padres so tambm modestos. Para configuraes de hardware mais potentes podem ser feitas modificaes que aumentam o desempenho consideravelmente. Essas modificaes, no postgresql.conf so feitas seguindo o modelo parmetro = valor, sendo que para valores numricos no se usa separador de milhar e para valores no numricos (texto/string) o valor deve ficar entre aspas simples: parmetro='valor'. Antes de qualquer modificao, vamos criar uma tabela de exemplo e popul-la significativamente: Criao da tabela:
CREATETABLEtb_tuning_teste( codigoserialprimarykey, valor1int, valor2int, valor3int, textovarchar(50) );
No shell digite:
foriin$(seq110000); dopsqldcursoc\ "INSERTINTOtb_tuning_teste(valor1,valor2,valor3,texto)VALUES\ ($i*2,$i*3,$i*4,now()||''||'$i'),($i*2,$i*3,$i*4,now()||''||'$i'),\ ($i*2,$i*3,$i*4,now()||''||'$i'),($i*2,$i*3,$i*4,now()||''||'$i'),\ ($i*2,$i*3,$i*4,now()||''||'$i'),($i*2,$i*3,$i*4,now()||''||'$i'),\ ($i*2,$i*3,$i*4,now()||''||'$i'),($i*2,$i*3,$i*4,now()||''||'$i'),\ ($i*2,$i*3,$i*4,now()||''||'$i'),($i*2,$i*3,$i*4,now()||''||'$i');" done
O comando acima gerar 100.000 registros na tabela criada. Essa operao demorar alguns minutos: No psql digite:
EXPLAINANALYZESELECT*fromtb_tuning_testeWHERE(codigo%2=0)ORDERBYvalor3DESC;
Repita cinco vezes e veja os resultados. Sero exibidos como segue:

QUERYPLAN Sort(cost=2502.71..2503.96rows=500width=50)(actualtime=191.541..248.919rows=50000loops=1) SortKey:valor3 SortMethod:externalmergeDisk:2952kB >SeqScanontb_tuning_teste(cost=0.00..2480.30rows=500width=50)(actual time=0.023..77.225rows=50000loops=1) Filter:((codigo%2)=0) Totalruntime:281.811ms (6rows)
143
46.1 - Shared Memory Tambm conhecida como memria compartilhada, uma quantidade de memria disponibilizada pelo sistema operacional para fins de comunicao entre processos (IPC: Inter-Process Comunication). A configurao da memria compartilhada feita atravs de parmetros do kernel:
Descrio Quantidade mxima (em bytes) de um segmento de SHMMAX memria compartilhada SHMALL Parmetro Sugesto de Valor 250kB + (8.2kB * shared_buffers) + (14.2kB * max_connections)
Total de memria compartilhada disponvel (em pginas O valor equivalente ao de SHMMAX = SHMMAX / 4096 de 4KB)
46.1.1 - Visualizando os Valores de Memria Compartilhada Respectivamente, para cada um dos parmetros do kernel citados, no shell d os comandos:
cat/proc/sys/kernel/shmmax cat/proc/sys/kernel/shmall
46.1.2 - Modificando os Valores de Memria Compartilhada H trs maneiras para fazer a modificao, ambas como usurio root do sistema: 1. A forma no efetiva, ao reiniciar a mquina a configurao voltar ao que era antes:
echo322838528>/proc/sys/kernel/shmmax echo78818>/proc/sys/kernel/shmall
2. Forma definitiva por edio, abrindo o arquivo /etc/sysctl.conf e ento altere ou adicione as seguintes linhas referentes ao parmetros kernel.shmmax e kernel.shmall:
kernel.shmmax = 322838528 kernel.shmall = 78818
Salve, saia e d o comando no prompt:

sysctlp
3. A forma definitiva por comando, sem precisar editar arquivo:

sysctlwkernel.shmmax=322838528 sysctlwkernel.shmall=78818
O utilitrio sysctl usado para modificar parmetros do kernel em tempo de execuo.
144
46.2 - Configuraes de Consumo de Recursos no postgresql.conf 46.2.1 - Gerenciamento de Trava Lock Management 46.2.1.1 - deadlock_timeout (integer) Padro: 1s Vamos assumir de uma forma otimista que dead locks no so comuns em aplicaes de produo e apenas espera por uma trava enquanto antes de comear a buscar por um dead lock. Aumentando este valor reduz a quantidade de tempo gasto em checagens de dead lock sem necessidade. O valor padro de um segundo (1s) provavelmente o menor valor que vai querer na prtica. Em um servidor muito carregado voc pode querer aumentar esse valor. Idealmente o ajuste deve exceder o tempo tpico de transao, de modo a melhorar as chances que uma trava ser liberada antes da deciso do sistema para checar por dead locks. Quando o parmetro log_lock_waits est ajustado, determina tambm o tempo de espera antes que uma mensagem de log enviada sobre a espera de lock (trava). Se voc est investigando atrasos de locking voc deve querer ajustar um valor menor para log_check_waits do que um deadlock_timeout normal. 46.2.1.2 - max_locks_per_transaction (integer) Padro: 64 Controla o nmero mdio de travas alocadas para cada transao; transaes individuais podem travar mais objetos enquanto como as travas de todas transaes se ajustam na tabela de trava (lock table). Este no o nmero de linhas que devem ser travadas, esse ilimitado. O valor padro (64), tem historicamente tem se comprovado suficiente, mas voc deve precisar aumentar se tem clientes que acionam tabelas diferentes em uma nica transao. Ao aumentar esse parmetro pode fazer com que o PostgreSQL requira mais memria compartilhada do que sua configurao padro permite. 46.2.2 - Memria 46.2.2.1 - shared_buffers (integer) Padro: 24MB Um dos mais importantes parmetros que influencia diretamente no desempenho do sistema de banco de dados. Determina a quantidade de memria (buffer) da memria compartilhada que o PostgreSQL usa. Esse parmetro expresso escrevendo o valor diretamente com o sufixo de medida (KB,MB,..) ou um nmero puro que representa a quantidade de blocos de 8 KB. Exemplos equivalentes:
shared_buffers = 24MB # Valor em MB
ou
shared_buffers = 3072 # 3072 pginas de 8KB (Qtd em MB x 1024) / 8
E eis ento a questo: Com qual valor ajusto shared_buffers? A resposta mais adequada : A quantidade adequada para este parmetro o maior nmero possvel que no prejudique outras atividades no servidor. Para reajustar esse parmetro preciso tambm reajustar os valores de shmmax e shmall do
145
kernel. Primeiro se define a quantidade desejada para o parmetro shared_buffers, e ento a quantidade para a memria compartilhada (shmmax) ser determinada pela seguinte frmula: SHMMAX = 250kB + (8.2kB * shared_buffers) + (14.2kB * max_connections) Exemplo: Supondo que a mquina que o servidor Postgres esteja tenha apenas 1 GB (1024 MB), e 300 MB seja uma quantidade segura de memria para destinar ao parmetro shared buffers e o parmetro max_connections seja igual a 10: shared_buffers = (300x1024) / 8 shared_buffers = 38400 ento: SHMMAX = 250kB + (8.2kB *38400)+(14.2kB * 10) = 315272 kB Valor em bytes: SHMMAX = 315272 x 1024 = 322838528 Precisamos agora do valor para SHMALL: SHMALL = SHMMAX / 4096 = 322838528 / 4096 = 78818 Ok. Agora temos os valores para shmmax e shmall. preciso fazer com que o kernel reconhea esses valores:
sysctlwkernel.shmmax=322838528 sysctlwkernel.shmall=78818
E por fim, aps todos os cculos, no arquivo postgresql.conf, vamos definir finalmente, a quantidade de 300MB para o parmetro shared buffers:
shared_buffers = 38400
ou
shared_buffers = 300MB
46.2.2.2 - temp_buffers (integer) Padro: 8MB Configura a quantidade mxima de buffers temporrios usados em cada sesso de banco de dados. Esses so buffers de sesso local apenas usados para tabelas temporrias. A configurao pode ser alterada com sesses individuais, mas apenas at o primeiro uso de tabelas temporrias dentro de uma sesso; tentativas subsequentes para mudar o valor no ter efeito nessa sesso. Uma sesso sesso alocar buffers temporrios conforme o necessrio at o limite dado de temp_buffers. O custo de configurar um valor muito alto em sesses que realmente no precisam de muitos buffers temporrios apenas um descritor de buffer, ou cerca de 64 bytes por incremento em temp_buffers. Porm, se um buffer realmente usado um adicional de 8192 bytes (8KB) ser consumido por ele. Exemplo:
temp_buffers = 8MB
146
46.2.2.3 - max_prepared_transactions (integer) Padro: 0 Configura o mximo nmero de transaes que podem estar em um estado preparadas simultaneamente (PREPARE TRANSACTION do manual do PostgreSQL). Ajustando este parmetro para zero desabilita a caracterstica de transao preparada (prepared transaction). Se no planeja usar esse recurso, seu valor deve ser zero para prevenir criao acidental de transaes preparadas. Caso estiver usando transaes preparadas, provavelmente gostaria que max_prepared_transactions seja no mnimo o mesmo valor de max_connections, de modo que cada sesso pode ter uma transao preparada pendente. Aumentanto max_prepared_transactions custa aproximadamente 600 bytes de de memria compartilhada por slot de transao, mais lock space (veja max_locks_per_transaction). 46.2.2.4 - work_mem Padro 1MB Determina a quantidade de buffers de memria compartilhada utilizada pelo Postgres. Tem efeito em operaes de ordenao para manipulao e/ou consulta de ndices. Especifica a quantidade de memria usada por operaes internas de classificao e tabelas hash antes de alternar para arquivos temporrios em disco. Para cada consulta complexa, muitas consultas e operaes de hash devem rodar paralelamente; cada uma ser permitida para usar tanta memria quanto este valor especifica antes dele comear a inserir os dados em arquivos temporrios. Alm disso, vrias sesses em execuo poderiam fazer tais operaes simultaneamente. Ento o total de memria usado pode ser muitas vezes o valor de work_mem; necessrio para manter esse fato em mente quando escolher o valor. operaes de classificao so usadas por ORDER BY, DISTINCT e JOINs. Tabelas hash so usadas em JOINs de hash, agregao baseada em hash e hashes baseados em processamento de subconsultas IN. Vamos alterar para 30MB o valor:
work_mem = 30MB
46.2.2.5 - maintenance_work_mem (integer) Padro: 16MB Especifica a quantidade mxima de memria para ser usada em operaes de manuteno, como VACUUM, CREATE INDEX, e ALTER TABLE ADD FOREIGN KEY. Desde que apenas uma dessas operaes possa ser executada em um momento por uma sesso, e uma instalao normalmente no tenha muitas delas rodando atualmente, seguro configurar este valor significantemente maior do que work_mem. Maiores configuraes devem melhorar a performance para VACUUM e para restaurar backups. Note que enquanto o autovacuum roda, at autovacuum_max_workers essa memria deve der alocada, ento tenha cuidado para no configurar o valor padro to alto. Exemplo:
maintenance_work_mem = 100MB
46.2.2.6 - max_stack_depth (integer) Padro: 2MB Determina a profundidade mxima segura de pilhas em execuo. A configurao ideal para este parmetro o atual limite de tamanho de pilha executada pelo kernel menos uma margem segura de 1MB. A margem de segurana necessria porque a profundidade de pilha no checada em todas rotinas no servidor, mas apenas nas principais rotinas potencialmente recursivas como avaliao de expresso. A configurao padro um valor seguro contra falhas, mas muito baixo para permitir execuo de funes complexas. Apenas super usurios podem mudar essa configurao. Definir max_stack_depth com um valor mais alto do que o atual limite do kernel significa que uma funo recursiva que escapar pode quebrar um processo individual de backend. Em plataformas onde o Postgres pode determinar o limite do kernel, no vai deixar definir essa varivel para um valor no seguro. Porm, nem todas as plataformas fornecem a
147
informao, ento recomendado ter cautela ao configurar esse valor. Exemplo:

max_stack_depth = 5MB
46.2.3 - Uso de Recursos do Kernel 46.2.3.1 - max_files_per_process (integer) Padro: 1000 Configura o nmero mximo permitido de arquivos abertos simultaneamente para cada subprocesso do servidor. Se o kernel impor um limite seguro por processo, no precisa se preocupar com esta configurao. Mas em algumas plataformas (a maioria dos sistemas BSD), o kernel permitir processos individuais a abrir muito mais arquivos do que o sistema pode realmente suportar quando um grande nmero de processos de todos os que tentam abrir muitos arquivos. Se aparecer uma mensagem de falha: "Too many open files", tente reduzir esta configurao. Exemplo:
max_files_per_process = 1000
46.2.3.2 - shared_preload_libraries (string) Padro: [em branco] Esta varivel define uma ou mais bibliotecas compartilhadas que devem ser carregadas na inicializao do servidor. Se deve carregar mais de uma biblioteca, separe seus nomes com vrgulas. Por exemplo: '$libdir/mylib' abrir mylib.so (em alguns sistemas; mylib.sl) para ser carregada do diretrio de instalao padro de bibliotecas. Bibliotecas de linguagens procedurais podem ser carregadas desse jeito, tipicamente usando a sintaxe '$libdir/plXXX' onde XXX pgsql, perl, tcl ou python. Carregando a biblioteca compartilhada, seu tempo de inicializao evitado quando usada pela primeira vez. No entanto, o tempo para inicializar cada novo processo de servidor deve aumentar ligeiramente, mesmo se esses processos nunca usem essa biblioteca. Ento, alterar esse parmetro recomendado apenas para bibliotecas que sero usadas na maior parte das sesses. Se uma biblioteca especificada no for encontrada, o servidor falhar ao inicializar. Todas bibliotecas suportadas pelo PostgreSQL tem um bloco mgico (magic bloc) que checado para garantir compatibilidade. Por essa razo, bibliotecas no-PostgreSQL no podero ser carregadas desse jeito. Exemplo:
shared_preload_libraries =
46.2.4 - Custo Baseado no Tempo de VACUUM Durante a execuo de comandos VACCUM e ANALYZE, o sistema mantm um contador interno que mantm o controle de custo estimado de vrias operaes I/O (input/output entrada/sada) que so realizadas. Quando o custo acumulado alcana um limite (especificado por vacuum_cost_delay). Ento ele vai zerar o contador e continua a execuo. A inteno dessa caracterstica permitir administradores reduzir o impacto de I/O desses comandos em atividades de banco de dados simultneas. H muitas situaes em que no muito importante que comandos de manuteno como VACUUM e ANALYZE terminem rapidamente; porm, muito importante que esses comandos normalmente no interfiram significantemente na habilidade que o sistema realiza operaes de banco de dados. Custo baseado no tempo de VACUUM (Cost-based vacuum delay) fornece uma maneira para administradores alcanarem isso. Esta caracterstica desabilitada por padro para comandos VACUUM enviados. Para habilitar defina o parmetro vaccum_cost_delay para um valor diferente de zero.
148
46.2.4.1 - vacuum_cost_delay (integer) Padro: 0ms Durao do tempo em milisegundos, que o processo aguardar quando o limite de custo (cust limit) for excedido. Valores positivos habilitam esse parmetro. Em muitos sistemas, a resoluo efetiva de hibernao de 10ms; configurando vacuum_cost_delay para um valor que no um mltiplo de 10 deve ter os mesmos resultados como os configurados no prximo nmero maior que 10. Quando o custo baseado em vcuo (cost-based vacuuming) usado de forma apropriada para vacuum_cost_delay so normalmente valores muito pequenos, talvez 10 ou 20 milisegundos. Ajustando o recurso de consumo de vcuo melhor feito ajustando outros parmetros de custo de vcuo. Exemplo:
vacuum_cost_delay = 0ms
46.2.4.2 - vacuum_cost_page_hit (integer) Padro: 1 O custo estimado para vcuo de um buffer encontrado no cache de shared buffer. Ele representa o custo para travar as requisies de buffer, pesquisa a tabela de hash compartilhada e vasculha o contedo da pgina. Exemplo:
vacuum_cost_page_hit = 1
46.2.4.3 - vacuum_cost_page_miss (integer) Padro: 10 O custo estimado para vcuo de um buffer que tem que ser lido do disco. Isso representa o esforo para travar as requisies de buffer, pesquisa a tabela de hash compartilhada, l o bloco desejado do disco e vasculha seu contedo. Exemplo:
vacuum_cost_page_miss = 10
46.2.4.4 - vacuum_cost_page_dirty (integer) Padro: 20 O custo estimado cobrado quando o vcuo modifica um bloco que foi previamente limpo. Ele representa o I/O requirido para liberar o bloco limpo no disco novamente. Exemplo:
vacuum_cost_page_dirty = 20
149
46.2.4.5 - vacuum_cost_limit (integer) Padro: 200 O custo acumulado que causar a hibernao do processo de vcuo. H determinadas operaes que mantm bloqueios crticos e deve ento completar o mais rpido possvel. Custos baseados no tempo de vcuo no ocorrem durante durante tais operaes. Porm, possvel que o custo acumule muito mais do que o limite especificado. Para evitar longos atrasos nesses casos, o atual (delay) caulculado por vacuum_cost_delay * accumulated_balance / vaccum_cost_limit com um mximo de vaccum_cost_delay * 4. Exemplo:
vacuum_cost_limit = 200
46.2.5 - Background Writer H um processo separado chamado background writer, cuja funo enviar escritas de shared buffers sujos (dirty). A inteno que o processo do servidor manipule consultas de usurios para que raramente ou nunca tenha que esperar para que uma escrita ocorra, porque o background writer o far. No entanto, h um aumento significativo de carga de I/O, porque uma pgina repetidamente suja deve ser escrita apenas uma vez por intervalo de checkpoint, mas o background writer deve grav-lo muitas vezes no mesmo intervalo. Os parmetros discutidos em seguida podem ser usados para melhorar o desempenho do comportamento para necessidades locais. 46.2.5.1 - bgwriter_delay (integer) Padro: 200ms Especifica o tempo entre rodadas de atividade para o background writer. Em cada round (rodada) o writer escreve para um determinado nmero de buffers sujos (controlvel pelos parmetros seguintes). Em seguida ele espera pelo tempo do valor de bgwriter e repete. Em muitos sistemas, a resoluo efetiva de tempo de hibernao 10ms; configurando bgwriter_delay para um valor que no um mltiplo de 10 deve ter os mesmos resultados com configur-lo para o prximo mltiplo de 10. Exemplo:
bgwriter_delay = 200ms
46.2.5.2 - bgwriter_lru_maxpages (integer) Padro: 100 Em cada rodada, no mais do que isso muitos buffers sero escritos pelo background writer. Configurando isso para zero desabilita background writing (exceto para atividades de checkpoint). Exemplo:
bgwriter_lru_maxpages = 100
46.2.5.3 - bgwriter_lru_multiplier (floating point) Padro: 2.0 O nmero de buffers sujos escritos em cada rodada baseado no nmero de novos buffers que tem sido necessrios por processos do servidor durante rodadas recentes. A mdia recente necessria multiplicada pelo bgwriter_lru_multiplier para chegar a um nmero estimado de nmero de buffers que preciso durante a prxima rodada. Buffers sujos so escritos at que haja muitos limpos, bufferes disponveis reutilizveis. (porm, no mais do que buffers bgwriter_lru_maxpages sero escritos por rodada.) Assim, uma configurao de 1.0 representa uma poltica just in time de escrita exatamente o nmero de buffers previsto para ser necessrio. Valores maiores fornecem amortecimento contra picos de demanda, enquanto valores menores intencionalmente deixa escritas para serem feitas por processos do
150
servidor. Valores menores de bgwriter_lru_maxpages e bgwriter_lru_multiplier reduz a carga extra de I/O causada pelo background writer, torna mais provvel que que os processos do servidor ter que enviar escritas para si mesmos, atrasando consultas interativas. Exemplo:
bgwriter_lru_multiplier = 2.0
46.2.6 - Comportamento Assncrono 46.2.6.1 - effective_io_concurrency (integer) Padro: 1 Configura o nmero de operaes simultneas de I/O de disco que o PostgreSQL espera serem executadas. Aumentando este valor eleva o nmero de operaes de I/O que qualquer sesso individual do PostgreSQL tenta iniciar em paralelo. A faixa de valores permita de 1 a 1000, ou zero para desativar solicitaes assncronas de I/O. Um bom ponto de partida para essa configurao o nmero de drives separados, inclusive para RAID. No entanto, se o banco de dados est frequentemente ocupado com mltiplas consultas feitas em sesses concorrentes, valores mais baixos devem ser suficiente para manter o disco de array ocupado. Um valor mais alto do que o necessrio para manter os discos ocupados resultaro apenas em overhead extra de CPU. Exemplo:
effective_io_concurrency = 1
46.2.7 - Constantes de Custo do Planejador As variveis de custo descritas nessa seo so mensuradas em uma escala arbitrria. Apenas seus valores relativos importam, portanto dimensionando todos eles para cima ou para baixo pelo mesmo fator resultar em nenhuma mudana nas escolhas do planejador. Tradicionalmente, essas variveis tem sido referenciadas para buscas de pginas sequenciais como unidade de custo, isto , seq_page_cost convencionalmente ajustada para 1.0 e as outras variveis de custo so definidas com referncia a essa. Mas voc pode usar uma escala diferente se preferir, como os tempos de execuo em milisegundos em uma mquina especfica. Infelizmente. no h nenhum mtodo bem definido para determinar valores ideais para variveis de custo. Eles so melhores tratados como mdias sobre todas as consultas que uma instalao particular far. Isso significa que mudando-os com base em algumas poucas experincias arriscado 46.2.7.1 - seq_page_cost (floating point) Padro: 1.0 Ajusta a estimativa do planejador de custo de uma pgina do disco buscar qual a parte de uma srie de busca sequencial. Exemplo:
seq_page_cost = 1.0
46.2.7.2 - random_page_cost (floating point) Padro: 4.0 Configura a estimativa do planejador de custo de uma pgina de disco no-sequencialmente151
buscada. Reduzindo este valor relativo a seq_page_cost far com que o sistema prefira buscas indexadas, aumentando far as buscas indexadas parecerem mais custosas. Voc pode aumentar ou diminuir ambos os valores juntos para mudar a prioridade de custos de I/O de discos para custos de CPU, que sero descritos pelos parmetros seguintes. Dica: Apesar de o sistema permitir definir ramdom_page_cost para menos do que seq_page_cost, ele no fisicamente sensato faz-lo. Porm, ajustando-os igualmente faz sentido se o banco de dados estiver totalmente no cache da memria RAM, porque neste casono haver penalidade para manejar pginas fora de sequncia. Alm disso, em um banco de dados pesadamente em cache voc dever abaixar ambos os valores relativos para os parmetros de CPU, desde que o custo para buscar uma pgina j em RAM muito menor do que ele normalmente seria. Exemplo:
random_page_cost = 4.0
46.2.7.3 - cpu_tuple_cost (floating point) Padro: 0.01 Define a estimativa do planejador de custo de processamento de cada linha, durante uma consulta. Exemplo:
cpu_tuple_cost = 0.01
46.2.7.4 - cpu_index_tuple_cost (floating point) Padro: 0.005 Define a estimativa do planejador de custo de processamento de cada entrada de ndice durante a varredura do ndice. Exemplo:
cpu_index_tuple_cost = 0.005
46.2.7.5 - cpu_operator_cost (floating point) Padro: 0.0025 Define a estimativa do planejador de custo de processamento de cada operador ou funo executados durante uma consulta. Exemplo:
cpu_operator_cost = 0.0025
46.2.7.6 - effective_cache_size (integer) Padro: 128MB This parameter has no effect on the size of shared memory allocated by PostgreSQL, nor does it reserve kernel disk cache; it is used only for estimation purposes. Define a suposio do planejador sobre o tamanho efetivo de cache de disco que est disponvel para uma nica consulta. um dos elementos de estimativa de custo de uso de um ndice; um valor mais alto provalvelmente far com que varreduras de ndices sejam utilizadas, um valor mais baixo provavelmente far mais buscas sequenciais. Ao configurar este parmetro deve considerar os shared buffers do PostgreSQL e a poro de memria cache do disco do kernel que ser usado para arquivos de dados. Alm disso, considerar o nmero de consultas simultneas em tabelas diferentes, uma vez que ter de compartilhar o espao disponvel.
152
Este parmetro no tem efeito no tamanho da memria compartilhada alocada pelo Postgres, nem faz reserva de cache de disco do kernel, s usado para fins de estimativa. Um bom valor sugerido para este parmetro 2/3 da memria RAM. Exemplo:
effective_cache_size = 682MB
153
47 - Instalao a Partir do Cdigo Fonte Compilao

Instalar o Postgres a partir do cdigo fonte permite uma flexibilidade maior para um DBA, podendo incluir recursos. Vejamos algumas das vantagens em fazer a instalao a partir do cdigo fonte: Escolher uma verso especfica ao invs de apenas a que est no repositrio; Fazer a compilao de acordo com a arquitetura do processador; Dentre vrias coisas, pode-se definir em que porta as conexes podero escutar, diretrios, etc; O processo de compilao analisa seu hardware (principalmente processador e memria), fazendo assim com que a instalao aproveite melhor os recursos do servidor;
47.1 - Instalando 1. Instalar programas e bibliotecas necessrios para compilao do source do Postgres: Como usurio root:
aptgetinstallbuildessentiallibreadline5devzlib1gdevgettext
2. No seu browser entre no endereo: http://www.postgresql.org/ftp/source/ Ao abrir o site, sero exibidas algumas pastas que representam uma verso, clique sobre a verso desejada e ento aparecer outra tela com os arquivos disponveis para baixar. Esses arquivos esto nos formatos tar.gz e tar.bz2 e alm desses h um arquivo .md5 para cada um, para fazer o checksum. No nosso exemplo baixaremos apenas o arquivo .bz2, o qual devido sua compresso ser maior um arquivo de menor tamanho e consequentemente mais rpido de baixar. Escolha um mirror (clique), para baixar o arquivo .bz2. De preferncia baixe o arquivo no diretrio /tmp. 3. Com o arquivo baixado precisamos descompact-lo:
tarxvjfpostgresqlXXXX.tar.bz2
Onde XXXX a verso do software baixado. 4. Vamos criar um usurio de sistema que ser o usurio do cluster, seu grupo e tambm definir o local do cluster, que tambm ser o diretrio home do usurio: Grupo: dbgroup Usurio: dbmaster Diretrio do Cluster: /db_data Diretrio de instalao do PostgreSQL: /usr/local/postgresql Execute os dois comandos como root:
groupadddbgroup useraddgdbgroupd/db_datas/bin/bashc"PostgresUser"dbmasterm
Sendo que: -g grupo do usurio, -d diretrio home do usurio, -c comentrio e -m fora a criao do diretrio home.
passwddbmaster#defineasenhadousuriodbmaster
5. D a propriedade do cdigo para o usurio dbmaster e para seu grupo:

chownRdbmaster:dbgrouppostgresqlXXXX/ 154
6. Entre no diretrio do source:

cdpostgresqlXXXX/
7. Antes de comearmos a compilar para que o cdigo seja compilado de uma maneira a oferecer um maior desempenho vamos definir algumas variveis de ambiente: Para dividir a compilao em 2 trabalhos (jobs), para CPUs de 2 ncleos:
exportMAKEOPTS="j2"
Memria cache para compilao de 500M:

exportCCACHE_SIZE="500M"
Especficas para sistemas de 32 ou 64 bits: Para sistemas de 32 Bits:

exportCHOST="i686pclinuxgnu" exportCFLAGS="march=nativeO3m32pipe" exportCXXFLAGS="${CFLAGS}"
Para sistemas de 64 Bits:

exportCHOST="x86_64pclinuxgnu" exportCFLAGS="march=nativeO3m64pipe" exportCXXFLAGS="${CFLAGS}"
Obs.: No caso das variveis especficas (32 ou 64 bits) so de acordo com a verso do seu sistema instalado, ressaltando que sistemas de 64 bits tm desempenho muito superior aos de 32. O suporte a um sistema de 64 bits exige um processador de 64 bits. Explicao das flags usadas nas variveis: -march=cpu-type Gera instrues para o tipo de processador da mquina. -native Produz o cdigo otimizado para a mquina local, auto detectando o modelo de CPU. -m32 -m64 Gera cdigo, respectivamente, para ambientes de 32 bits ou de 64 bits. -pipe Acelera o processo de compilao, mas no h ganhos no tempo de execuo. -O[n] (letra "O" maiscula) Otimizao. Faz com que compilao leve mais tempo e muito mais memria. Com esta opo o compilador tenta reduzir o tamanho do cdigo e o tempo de execuo. H trs nveis de otimizao: "-O1", "-O2" e "-O3", sendo que "-O3" o nvel mais alto. 8. Agora vamos compilao em si. D os comandos:
sudbmasterc"./configureprefix=/usr/local/postgresqlbindir=/usr/local/bin\ sbindir=/usr/local/sbin" 155
make&&makeinstall
Como nossas otimizaes exigem muito do processador, hora de tomar um caf! 9. Para iniciarmos o postgresql precisamos do script de inicializao:
cp/tmp/postgresqlXXXX/contrib/startscripts/linux/etc/init.d/postgresql chmod755/etc/init.d/postgresql updaterc.dpostgresqlstart70S.stop2106.
Obs.: Na pasta contrib do cdigo fonte do PostgreSQL h tambm funes que por padro no esto no PostgreSQL, como a funo dblink. preciso editar o arquivo /etc/init.d/postgresql e mude os seguintes valores:
prefix: /usr/local/pgsql prefix=/usr/local/postgresql PGDATA: /usr/local/pgsql/data /db_data/base PGUSER: postgres dbmaster DAEMON="$prefix/bin/postmaster" "/usr/local/bin/postmaster" PGCTL="$prefix/bin/pg_ctl" "/usr/local/bin/pg_ctl"
10. Crie o cluster:

sudbmasterc"/usr/local/bin/initdbEutf8D/db_data/base"
11. Como root inicialize o processo do postgres com o script:

/etc/init.d/postgresqlstart
12. Opcionalmente pode ser adicionada a seguinte linha, relativa varivel de diretrio do cluster:
export PGDATA="/db_data/base"
As variveis s passaro a valer pro usurio na prxima vez que ele logar, a no ser que ele d o comando:
source~/.bashrc
156
48 - Instalao do PostgreSQL no Windows

O uso do PostgreSQL na plataforma Windows no encorajado, principalmente pela performance muito inferior a sistemas operacionais Unix like (Linux, Solaris, BSDs). Mas para quem no tem nenhum desses sistemas operacionais instalados em seu computador, abaixo esto os procedimentos para a atual verso (8.4.2): 1. Baixe o instalador do seguinte endereo: www.postgresql.org/download/windows 2. V at o diretrio onde baixou o instalador e d um duplo clique sobre ele e siga os passos: Setup - PostgreSQL Next Installation Directory C:\Arquivos de programas\PostgreSQL\8.4 Next Data Directory C:\Arquivos de programas\PostgreSQL\8.4\data Next Password -> Digite e redigite uma senha para o super usurio do Postgres -> Next Port 5432 Advanced Options Locale=[Default locale], [ ] Install pl/pgsql in template1 database? Next Read to Install Next Completing the PostgreSQL Setup Wizard [ ] Launch Stack Builder at exit? Finish Iniciar -> Configuraes -> Painel de Controle -> Sistema Guia "Avanado" -> Variveis de Ambiente Variveis do sistema -> Path -> Editar -> Adicione no final da linha: ";C:\Arquivos de programas\PostgreSQL\8.4\bin" (sem as aspas) -> OK -> OK -> OK Iniciar -> Executar... -> Arir: cmd -> OK psql -U postgres
157
Extras
Cdigo do Banco de Dados Usado como Exemplo (Copie, cole e salve)
CRIAODOBANCODEDADOS"curso" CREATEDATABASEcursoENCODING='utf8'LC_COLLATE='pt_BR.UTF8'LC_CTYPE='pt_BR.UTF8' TEMPLATE=template0; CREATEDATABASEcurso; \ccurso TABELACOLABORADORES CREATETABLEcolaboradores ( mat_idcharacter(5)NOTNULL, nomecharactervarying(15)NOTNULL, snomecharactervarying(30)NOTNULL, cargocharactervarying(15), setorcharactervarying(15), ufcharacter(2)DEFAULT'SP'::bpchar, cidadecharactervarying(15), salarioreal, dt_admisdate, chefe_diretocharacter(5), CONSTRAINTcolaboradores_pkeyPRIMARYKEY(mat_id), CONSTRAINTchefe_direto_fkFOREIGNKEY(chefe_direto)REFERENCEScolaboradores(mat_id) ) WITH(OIDS=FALSE); ALTERTABLEcolaboradoresOWNERTOpostgres; COMMENTONTABLEcolaboradoresIS'Funcionriosdaempresa'; TABELAPRODUTOS CREATETABLEprodutos ( prod_idcharactervarying(5)NOTNULL, nomecharactervarying(15)NOTNULL, precoreal, qtdinteger, descrtext, CONSTRAINTprodutos_pkeyPRIMARYKEY(prod_id) ) WITH(OIDS=FALSE); ALTERTABLEprodutosOWNERTOpostgres; TABELAPEDIDO CREATETABLEpedido ( ped_idcharactervarying(5)NOTNULL, prodcharactervarying(5), vendedorcharactervarying(5), CONSTRAINTpedido_pkeyPRIMARYKEY(ped_id), CONSTRAINTprod_fkFOREIGNKEY(prod) REFERENCESprodutos(prod_id)MATCHSIMPLE ONUPDATENOACTIONONDELETENOACTION, CONSTRAINTvend_fkFOREIGNKEY(vendedor) REFERENCEScolaboradores(mat_id)MATCHSIMPLE ONUPDATENOACTIONONDELETENOACTION ) WITH(OIDS=FALSE); ALTERTABLEpedidoOWNERTOpostgres; TABELAFUNCIONARIOSPREMIADOS CREATETABLEfuncionarios_premiados( premio_idvarchar(5)primarykey, mat_idvarchar(15), CONSTRAINTpk_funcionarioFOREIGNKEY(mat_id)REFERENCEScolaboradores(mat_id) ); 158
INSERTS Inserodedadosnatabelacolaboradores
INSERTINTOcolaboradoresVALUES ('00001','Chiquinho','daSilva','Diretor','Marketing',DEFAULT,'SoPaulo',8500.51,'199910 20','00001'), ('00002','Almerinda','Santos','Recepcionista','Recepo',DEFAULT,'SoPaulo',850.63,'1997 0404','00003'), ('00003','Estrovzio','daSilva','Diretor','Publicidade',DEFAULT,'SoPaulo',8500.51,'1997 0530','00001'), ('00004','Wolfrmio','Leite','Qumico','Laboratrio1',DEFAULT,'SoPaulo',5400.32,'199903 13','00003'), ('00005','Estrncio','Junqueira','Faxineiro','Limpeza',DEFAULT,'SoPaulo',600.00,'200010 25','00003'), ('00006','Azevino','Xavier','Porteiro','Portaria','MG','BeloHorizonte',1020.34,'199703 15','00007'), ('00007','Sandra','Lima','Diretor','Logstica','MG','BeloHorizonte',3200.51,'200304 04','00001'), ('00008','Vagner','daSilva','OfficeBoy','Expedio','SP','Jundia',550.48,'200206 23','00012'), ('00009','Elisa','Macedo','Vendedor','Marketing','MG','BeloHorizonte',2300.00,'200509 19','00007'), ('00010','Teobaldo','Moura','WebDesigner','Publicidade','RJ','Niteri',3210.00,'200008 27','00031'), ('00011','Radnio','Xavier','Qumico','Laboratrio13','RJ','Niteri',5400.32,'200311 11','00031'), ('00012','Cludia','Carvalho','Contador','Financeiro',DEFAULT,'Jundia',2500.51,'200212 12','00001'), ('00013','Natlia','Costa','Diretor','TI',DEFAULT,'SoPaulo',4559.72,'20020202','00001'), ('00014','Agevsio','Ramalho','Contador','Financeiro',DEFAULT,'SoPaulo',3002.00,'200505 04','00003'), ('00015','Gertrudes','Trevizan','Auxiliar','Expedio',DEFAULT,'SoPaulo',850.00,'200006 06','00003'), ('00016','Estrncio','daSilva','Engenheiro','Laboratrio3','SP','SoPaulo',7100.00,'1997 0707','00003'), ('00017','Agatemor','Santos','Programador','TI',DEFAULT,'SoPaulo',2500.00,'199811 03','00003'), ('00018','Alzevina','dosSantos','Estagirio','Marketing',DEFAULT,'SoPaulo',822.20,'2008 0109','00003'), ('00019','Deoclcio','Diniz','Motorista','Logstica',DEFAULT,'SoPaulo',1500.00,'200505 04','00003'), ('00020','Anglica','Santos','Estagirio','Publicidade','AM','Manaus',1000.00,'200904 04','00021'), ('00021','Sandra','Vilares','Gerente','Financeiro','AM','Manaus',5000.00,'200705 04','00001'), ('00022','Joana','Ferraz','Programador','TI','AM','Manaus',2500.00,'20090104','00021'), ('00023','Elmeraldo','daSilva','Motorista','Logstica','SP','SoPaulo',1000.00,'200902 04','00003'), ('00024','Beatriz','Xavier','Qumico','Laboratrio13','RJ','Niteri',3500.00,'200811 20','00031'), ('00025','Fernanda','Toledo','Vendedor','Marketing','MG','BeloHorizonte',1400.00,'200612 13','00007'), ('00026','Tnia','Costa','An.deSistemas','TI','AM','Manaus',4500.00,'20081210','00021'), ('00027','Zeoclcio','Teodoro','Motorista','Logstica','AM','Manaus',1200.00,'200801 31','00021'), ('00028','Mariana','Xavier','Vendedor','Logstica','SP','Jundia',1200.00,'200907 22','00003'), ('00029','Lucrcia','Ramalho','Programador','TI',DEFAULT,'Jundia',2200.00,'200707 07','00003'), ('00030','Martina','Santos','An.deSistemas','TI','SP','Jundia',4200.00,'200709 30','00003'), ('00031','Alice','Santos','Engenheiro','Laboratrio13','RJ','Niteri',7200.00,'200801 12','00031'), ('00032','Paula','Franco','Programador','TI','SP','Jundia',2100.00,'20070930','00003'); INSERTINTOcolaboradores(mat_id,nome,snome)VALUES ('00033','Z','Ningum'), ('00034','%///teste','teste'), ('00035','_///teste','teste'); Inserodedadosnatabelafuncionariospremiados
159
INSERTINTOfuncionarios_premiadosVALUES ('00001','00007'), ('00002','00013'), ('00003','00004'), ('00004','00021'), ('00005','00010'); INSERTINTOfuncionarios_premiados(premio_id)VALUES('00006'),('00007'),('00008'), ('00009'),('00010'),('00011'),('00012');
160
Dicas
Em que endereo est escutando?

SHOWlisten_addresses; listen_addresses localhost,192.168.10.7 (1row)
Criando banco de dados latin1 em um cluster utf-8
CREATEDATABASEsistema_outrosENCODING='latin1'LC_COLLATE='pt_BR.ISO88591' LC_CTYPE='pt_BR.ISO88591'TEMPLATE=template0;
Nmero de conexes abertas no servidor

SELECTcount(procpid)FROMpg_stat_activity;
Nmero de conexes abertas em um determinado banco

SELECTcount(procpid)FROMpg_stat_activityWHEREdatname='nome_do_banco';
Nmero de conexes abertas em cada banco do servidor

SELECTdatname,numbackendsFROMpg_stat_database;
Quais ROLES esto conectados ao servidor, sendo que oid = 10 o usuario que inicializa o cluster
SELECToid,rolnameFROMpg_authid;
161
Bibilografia
Documentao oficial do PostgreSQL http://www.postgresql.org/ Documentao oficial do GNU Compiler Collection (GCC) http://gcc.gnu.org/
162

Postgresql

Enviado por

Direitos autorais:

Formatos disponíveis

Postgresql

Enviado por

Dados do documento

Título original

Direitos autorais

Formatos disponíveis

Compartilhar este documento

Compartilhar ou incorporar documento

Opções de compartilhamento

Você considera este documento útil?

Este conteúdo é inapropriado?

Direitos autorais:

Formatos disponíveis

Postgresql

Enviado por

Direitos autorais:

Formatos disponíveis

Tomando as Rdeas do Grande Elefante dos Dados

Tomando as Rdeas do Grande Elefante dos Dados - PostgreSQL

Tomando as Rdeas do Grande Elefante dos Dados - PostgreSQL

Tomando as Rdeas do Grande Elefante dos Dados - PostgreSQL

Tomando as Rdeas do Grande Elefante dos Dados - PostgreSQL

Tomando as Rdeas do Grande Elefante dos Dados - PostgreSQL

Tomando as Rdeas do Grande Elefante dos Dados - PostgreSQL

Tomando as Rdeas do Grande Elefante dos Dados - PostgreSQL

Tomando as Rdeas do Grande Elefante dos Dados - PostgreSQL

Tomando as Rdeas do Grande Elefante dos Dados - PostgreSQL

1.2 - Limites do PostgreSQL

*Depende como o sistema foi configurado Mande instalar o PostgreSQL:

Tomando as Rdeas do Grande Elefante dos Dados - PostgreSQL

Tomando as Rdeas do Grande Elefante dos Dados - PostgreSQL

Tomando as Rdeas do Grande Elefante dos Dados - PostgreSQL

Parmetros [MODELO] [MODELO] [MODELO] [NOME]

[MODELO] [MODELO] [MODELO]

lista privilgios de acesso de tabelas, vises e sequncias (mesmo que \dp)

realiza comando SQL COPY dos dados para mquina cliente

operaes com objetos grandes

Tomando as Rdeas do Grande Elefante dos Dados - PostgreSQL

Acessando o contedo de uma determinada varivel:

1.7.2.1 - Criando uma nova varivel

Digitando o comando \set verificamos que ela deixou de existir.

Tomando as Rdeas do Grande Elefante dos Dados - PostgreSQL

2 - SQL (Structured Query Language Linguagem Estruturada de Consulta)

CONNECT, DELETE, INSERT, RULE, TEMPORARY, UPDATE,

Tomando as Rdeas do Grande Elefante dos Dados - PostgreSQL

2.4 - Criao de Usurio

<aperte a tecla tab 2X>

Caso receber a seguinte mensagem:

O prximo tpico trata do assunto.

Tomando as Rdeas do Grande Elefante dos Dados - PostgreSQL

3 - Autenticao de cliente e configurao geral do PostgreSQL (pg_hba.conf e postgresql.conf)

Efetive as alteraes, aps salvar dando o comando no prompt do sistema:

Entre no psql e nele crie uma senha para aluno:

Com o usurio aluno, digite no prompt:

Tomando as Rdeas do Grande Elefante dos Dados - PostgreSQL

4 - Importando um banco de dados j pronto de um arquivo

Agora, pra entrar no banco de dados usaremos no shell:

Tomando as Rdeas do Grande Elefante dos Dados - PostgreSQL

Se por acaso fosse criado um banco usando letras maisculas:

Liste os bancos de dados com:

Tomando as Rdeas do Grande Elefante dos Dados - PostgreSQL

6 - Modelos de Banco de Dados - Templates

Tomando as Rdeas do Grande Elefante dos Dados - PostgreSQL

Tomando as Rdeas do Grande Elefante dos Dados - PostgreSQL

* numeric [ (p,s) ] real

nmero exato customizvel

double precision float8 serial bigserial serial4 serial8

de 8 bytes 4 bytes 8 bytes

, (vrgula) separador de grupo (milhares)

Notas: a. RN roman numerals.

Tomando as Rdeas do Grande Elefante dos Dados - PostgreSQL

Tomando as Rdeas do Grande Elefante dos Dados - PostgreSQL

7.5.1 - Mscaras de Data e Hora

Tomando as Rdeas do Grande Elefante dos Dados - PostgreSQL

8 - Obtendo ajuda de comandos SQL dentro do psql

So mostradas todas as formas do comando SQL CREATE.