Manual de Referncia do PostgreSQL 7.2

The PostgreSQL Global Development Group

Manual de Referncia do PostgreSQL 7.2 by The PostgreSQL Global Development Group Copyright 1996-2001 by The PostgreSQL Global Development Group Legal NoticePostgreSQL is Copyright 1996-2001 by the PostgreSQL Global Development Group and is distributed under the terms of the license of the University of California below. Postgres95 is Copyright 1994-5 by the Regents of the University of California. Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies. IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN AS-IS BASIS, AND THE UNIVERSITY OF CALIFORNIA HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.

Table of ContentsPrefcio ........................................................................................................................................................i I. Comandos SQL.......................................................................................................................................1 ABORT ..............................................................................................................................................1 ALTER GROUP.................................................................................................................................1 ALTER TABLE..................................................................................................................................1 ALTER USER ....................................................................................................................................1 ANALYZE .........................................................................................................................................1 BEGIN ...............................................................................................................................................1 CHECKPOINT ..................................................................................................................................1 CLOSE ...............................................................................................................................................1 CLUSTER ..........................................................................................................................................1 COMMENT .......................................................................................................................................1 COMMIT ...........................................................................................................................................1 COPY .................................................................................................................................................1 CREATE AGGREGATE....................................................................................................................1 CREATE CONSTRAINT TRIGGER ................................................................................................1 CREATE DATABASE .......................................................................................................................1 CREATE FUNCTION .......................................................................................................................1 CREATE GROUP ..............................................................................................................................1 CREATE INDEX ...............................................................................................................................1 CREATE LANGUAGE......................................................................................................................1 CREATE OPERATOR .......................................................................................................................1 CREATE RULE .................................................................................................................................1 CREATE SEQUENCE.......................................................................................................................1 CREATE TABLE ...............................................................................................................................1 CREATE TABLE AS .........................................................................................................................1 CREATE TRIGGER ..........................................................................................................................1 CREATE TYPE..................................................................................................................................1 CREATE USER .................................................................................................................................1 CREATE VIEW .................................................................................................................................1 DECLARE .........................................................................................................................................1 DELETE.............................................................................................................................................1 DROP AGGREGATE ........................................................................................................................1 DROP DATABASE ............................................................................................................................1 DROP FUNCTION ............................................................................................................................1 DROP GROUP...................................................................................................................................1 DROP INDEX....................................................................................................................................1 DROP LANGUAGE ..........................................................................................................................1 DROP OPERATOR............................................................................................................................1 DROP RULE......................................................................................................................................1 DROP SEQUENCE ...........................................................................................................................1 DROP TABLE....................................................................................................................................1 DROP TRIGGER...............................................................................................................................1 DROP TYPE ......................................................................................................................................1 DROP USER ......................................................................................................................................1

iii

DROP VIEW......................................................................................................................................1 END ...................................................................................................................................................1 EXPLAIN...........................................................................................................................................1 FETCH ...............................................................................................................................................1 GRANT ..............................................................................................................................................1 INSERT ..............................................................................................................................................1 LISTEN ..............................................................................................................................................1 LOAD.................................................................................................................................................1 LOCK.................................................................................................................................................1 MOVE ................................................................................................................................................1 NOTIFY .............................................................................................................................................1 REINDEX ..........................................................................................................................................1 RESET ...............................................................................................................................................1 REVOKE............................................................................................................................................1 ROLLBACK.......................................................................................................................................1 SELECT .............................................................................................................................................1 SELECT INTO...................................................................................................................................1 SET.....................................................................................................................................................1 SET CONSTRAINTS ........................................................................................................................1 SET SESSION AUTHORIZATION ..................................................................................................1 SET TRANSACTION........................................................................................................................1 SHOW ................................................................................................................................................1 TRUNCATE .......................................................................................................................................1 UNLISTEN ........................................................................................................................................1 UPDATE.............................................................................................................................................1 VACUUM...........................................................................................................................................1 II. Aplicativos para a estao cliente do PostgreSQL.............................................................................4 createdb ..............................................................................................................................................5 createlang ...........................................................................................................................................8 createuser .........................................................................................................................................10 dropdb ..............................................................................................................................................13 droplang ...........................................................................................................................................15 dropuser............................................................................................................................................17 ecpg ..................................................................................................................................................19 pgaccess............................................................................................................................................24 pg_cong..........................................................................................................................................27 pg_dump...........................................................................................................................................29 pg_dumpall.......................................................................................................................................36 pg_restore.........................................................................................................................................38 psql ...................................................................................................................................................45 pgtclsh ..............................................................................................................................................68 pgtksh ...............................................................................................................................................69 vacuumdb .........................................................................................................................................70

iv

III. Aplicativos para o servidor do PostgreSQL....................................................................................73 initdb ................................................................................................................................................74 initlocation .......................................................................................................................................77 ipcclean ............................................................................................................................................78 pg_ctl................................................................................................................................................79 pg_passwd ........................................................................................................................................83 postgres ............................................................................................................................................85 postmaster ........................................................................................................................................89

v

PrefcioAs entradas deste Manual de Referncia se propem a fornecer um resumo completo e formal dos respectivos assuntos. Mais informaes sobre a utilizao do PostgreSQL, sob forma narrativa, de tutorial, ou de exemplos, podem ser encontradas em outras partes do conjunto de documentos do PostgreSQL. Veja as referncias cruzadas listadas em cada pgina de referncia. As informaes contidas no Manual de Referncia tambm esto disponveis no formato man pages do Unix. Projeto PostgreSQL Br1 - O centro de informaes para os usurios brasileiros.

Traduzido por Halley Pacheco de Oliveira2 - Cmara Municipal do Rio de Janeiro. Revisado por Diogo de Oliveira Biazus3 - Ikono

1. 2. 3.

http://pgsqlbr.querencialivre.rs.gov.br mailto:halleypo@yahoo.com.br mailto:diogo@ikono.com.br

i

I. Comandos SQLEsta parte contm informaes de referncia para os comandos SQL suportados pelo PostgreSQL. Por SQL entenda-se a linguagem SQL de modo geral; informaes sobre a conformidade e a compatibilidade de cada comando com relao ao padro podem ser encontradas nas respectivas pginas de referncia.

ABORTNameABORT aborta a transao corrente

SynopsisABORT [ WORK | TRANSACTION ]

Entradas Nenhuma.

SadasROLLBACK

Mensagem retornada se o comando for executado com sucesso.NOTICE: ROLLBACK: no transaction in progress

Se no houver nenhuma transao sendo executada.

DescrioO comando ABORT desfaz a transao corrente, fazendo com que todas as modicaes realizadas pela transao sejam rejeitadas. Este comando possui um comportamento idntico ao do comando ROLLBACK do SQL92, estando presente apenas por razes histricas. Notas Use o comando COMMIT para terminar uma transao com sucesso.

UtilizaoPara abortar todas as modicaes:ABORT WORK;

1

ABORT

CompatibilidadeSQL92 Este comando uma extenso do PostgreSQL presente apenas por razes histricas. O comando equivalente do SQL92 o ROLLBACK.

2

ALTER GROUPNameALTER GROUP inclui ou exclui usurios em um grupo

SynopsisALTER GROUP nome ADD USER nome_do_usurio [, ... ] ALTER GROUP nome DROP USER nome_do_usurio [, ... ]

Entradas nome O nome do grupo a ser modicado. nome_do_usurio Os usurios a serem includos ou excludos no grupo. Os nomes dos usurios devem existir.

SadasALTER GROUP

Mensagem retornada se a alterao for realizada com sucesso.

DescrioO comando ALTER GROUP utilizado para incluir ou excluir usurios em um grupo. Somente os superusurios do banco de dados podem utilizar este comando. Incluir um usurio em um grupo no cria o usurio. Analogamente, excluir um usurio de um grupo no exclui o usurio do banco de dados. Use o CREATE GROUP para criar um novo grupo, e o DROP GROUP para remover um grupo.

UtilizaoIncluir usurios em um grupo:ALTER GROUP arquitetura ADD USER joana, alberto;

1

ALTER GROUP Excluir um usurio de um grupo:ALTER GROUP engenharia DROP USER margarida;

CompatibilidadeSQL92 No existe o comando ALTER GROUP no SQL92. O conceito de roles similar ao de grupos.

2

ALTER TABLENameALTER TABLE altera a denio da tabela

SynopsisALTER TABLE [ ONLY ] tabela [ * ] ADD [ COLUMN ] coluna tipo [ restrio_de_coluna [ ... ] ] ALTER TABLE [ ONLY ] tabela [ * ] ALTER [ COLUMN ] coluna { SET DEFAULT valor | DROP DEFAULT } ALTER TABLE [ ONLY ] tabela [ * ] ALTER [ COLUMN ] coluna SET STATISTICS inteiro ALTER TABLE [ ONLY ] tabela [ * ] RENAME [ COLUMN ] coluna TO novo_nome_da_coluna ALTER TABLE tabela RENAME TO novo_nome_da_tabela ALTER TABLE tabela ADD definio_de_restrio_de_tabela ALTER TABLE [ ONLY ] tabela DROP CONSTRAINT restrio { RESTRICT | CASCADE } ALTER TABLE tabela OWNER TO novo_dono

Entradas tabela O nome da tabela existente a ser alterada. coluna O nome de uma coluna nova ou existente. tipo O tipo da nova coluna. novo_nome_da_coluna O novo nome para a coluna existente. novo_nome_da_tabela O novo nome para a tabela. definio_de_restrio_de_tabela A nova restrio de tabela (table constraint) para a tabela.

1

ALTER TABLE novo_dono O nome de usurio do novo dono da tabela.

SadasALTER

Mensagem retornada se o nome da coluna ou da tabela for alterado com sucesso.ERROR

Mensagem retornada se a tabela ou a coluna no existir.

DescrioO comando ALTER TABLE altera a denio de uma tabela existente. A forma ADD COLUMN adiciona uma nova coluna na tabela utilizando a mesma sintaxe de CREATE TABLE. A forma ALTER COLUMN SET/DROP DEFAULT permite denir ou remover o valor padro para a coluna. Note que o valor padro somente se aplica aos prximos comandos INSERT; as linhas existentes na tabela no so modicadas. A forma ALTER COLUMN SET STATISTICS permite controlar a coleta de estatsticas para as operaes ANALYZE posteriores. A clusula RENAME faz com que o nome da tabela, coluna, ndice ou seqncia seja mudado sem que os dados sejam modicados. Os dados permanecem do mesmo tipo e tamanho aps o comando ser executado. A clusula ADD definio_de_restrio_de_tabela adiciona uma nova restrio de tabela utilizando a mesma sintaxe de CREATE TABLE. A clusula DROP CONSTRAINT restrio elimina todas as restries da tabela (e de suas descendentes) que correspondam restrio. A clusula OWNER muda o dono da tabela para o usurio novo_dono. Somente o dono da tabela pode modicar seu esquema. Notas A palavra chave COLUMN informativa podendo ser omitida. Na atual implementao de ADD COLUMN, as clusulas valor padro e NOT NULL no so suportadas para a nova coluna. Pode ser usada a forma SET DEFAULT do comando ALTER TABLE para denir o valor padro mais tarde. (Os valores atuais das linhas existentes podero ser atualizados, posteriormente, para o novo valor padro usando o comando UPDATE.) Em DROP CONSTRAINT a palavra chave RESTRICT requerida, embora as dependncias ainda no sejam vericadas. A opo CASCADE no suportada. Atualmente DROP CONSTRAINT remove somente as restries CHECK. Para remover as restries PRIMARY ou UNIQUE deve ser removido o ndice correspondente utilizando o comando DROP INDEX. Para remover uma FOREIGN KEY necessrio recriar e recarregar a tabela usando outros parmetros no comando CREATE TABLE.

2

ALTER TABLE Por exemplo, para remover todas as restries da tabela distribuidores:CREATE TABLE temp AS SELECT * FROM distribuidores; DROP TABLE distribuidores; CREATE TABLE distribuidores AS SELECT * FROM temp; DROP TABLE temp;

Somente o dono da tabela pode alter-la. Mudar qualquer parte do esquema do catlogo do sistema no permitido. O manual Guia do Usurio do PostgreSQL possui mais informaes sobre herana. Consulte o comando CREATE TABLE para obter uma descrio mais detalhada dos argumentos vlidos.

UtilizaoPara adicionar uma coluna do tipo VARCHAR tabela:ALTER TABLE distribuidores ADD COLUMN endereco VARCHAR(30);

Para mudar o nome de uma coluna existente:ALTER TABLE distribuidores RENAME COLUMN endereco TO cidade;

Para mudar o nome de uma tabela existente:ALTER TABLE distribuidores RENAME TO fornecedores;

Para adicionar uma restrio de vericao (CHECK) a uma tabela:ALTER TABLE distribuidores ADD CONSTRAINT cep_chk CHECK (char_length(cod_cep) = 8);

Para remover uma restrio de vericao de uma tabela e de todas as suas lhas:ALTER TABLE distribuidores DROP CONSTRAINT cepchk RESTRICT;

Para adicionar uma chave estrangeira a uma tabela:

ALTER TABLE distribuidores ADD CONSTRAINT fk_dist FOREIGN KEY (endereco) REFERENCES e

3

ALTER TABLE

Para adicionar uma restrio de unicidade (multi-coluna) tabela:ALTER TABLE distribuidores ADD CONSTRAINT dist_id_cep_key UNIQUE (dist_id, cep);

Para adicionar uma restrio de chave primria a uma tabela com o nome gerado automaticamente, observando-se que a tabela somente pode possuir uma nica chave primria:ALTER TABLE distribuidores ADD PRIMARY KEY (dist_id);

CompatibilidadeSQL92 A forma ADD COLUMN est em conformidade, a no ser por no suportar valor padro e NOT NULL, conforme foi explicado anteriormente. A forma ALTER COLUMN est em conformidade total. O SQL92 especica algumas funcionalidades adicionais para o comando ALTER TABLE que ainda no so diretamente suportadas pelo PostgreSQL:ALTER TABLE tabela DROP [ COLUMN ] coluna { RESTRICT | CASCADE }

Remove a coluna da tabela. Na implementao atual, para remover uma coluna existente a tabela deve ser recriada e recarregada:CREATE TABLE temp AS SELECT did, cidade FROM distribuidores; DROP TABLE distribuidores; CREATE TABLE distribuidores ( did DECIMAL(3) DEFAULT 1, cidade VARCHAR(40) NOT NULL ); INSERT INTO distribuidores SELECT * FROM temp; DROP TABLE temp;

As clusulas para mudar o nome das tabelas, colunas, ndices e seqncias so extenses do PostgreSQL ao SQL92.

4

ALTER USERNameALTER USER altera a conta de um usurio do banco de dados

SynopsisALTER USER nome_do_usurio [ [ WITH ] opo [ ... ] ] onde opo pode ser: [ ENCRYPTED | UNENCRYPTED ] PASSWORD senha | CREATEDB | NOCREATEDB | CREATEUSER | NOCREATEUSER | VALID UNTIL data_hora

Entradas nome_do_usurio O nome do usurio cuja conta est sendo alterada. senha A nova senha a ser utilizada para esta conta. ENCRYPTED UNENCRYPTED Estas palavras chave controlam se a senha armazenada criptografada, ou no, em pg_shadow (Consulte o comando CREATE USER para obter mais informaes sobre esta opo). CREATEDB NOCREATEDB Estas clusulas denem a permisso para o usurio criar bancos de dados. Se CREATEDB for especicado, o usurio sendo alterado ter permisso para criar seus prprios bancos de dados. Especicando-se NOCREATEDB, a permisso para criar bancos de dados negada ao usurio. CREATEUSER NOCREATEUSER Estas clusulas denem a permisso para o usurio criar novos usurios. Esta opo tambm torna o usurio um superusurio, que pode mudar todas as restries de acesso. data_hora A data (e, opcionalmente, a hora) de expirao da senha do usurio.

1

ALTER USER SadasALTER USER

Mensagem retornada se a alterao for realizada com sucesso.ERROR: ALTER USER: user "nome_do_usurio" does not exist

Mensagem retornada quando o usurio especicado no existir no banco de dados.

DescrioO comando ALTER USER utilizado para mudar os atributos da conta de um usurio do PostgreSQL. Os atributos no mencionados no comando permanecem com os seus valores inalterados. Somente um superusurio do banco de dados pode alterar os privilgios e a expirao da senha com este comando. Os usurios comuns somente podem alterar as suas prprias senhas. O comando ALTER USER no pode mudar a participao do usurio nos grupos. Use o ALTER GROUP para realizar esta operao. Use o CREATE USER para criar um novo usurio, e o DROP USER para remover um usurio.

UtilizaoMudar a senha do usurio:ALTER USER marcos WITH PASSWORD hu8jmn3;

Mudar a data de expirao da senha do usurio:ALTER USER manuel VALID UNTIL Jan 31 2030;

Mudar a data de expirao da senha do usurio, especicando que sua autorizao expira ao meio dia de 4 de maio de 1998, usando uma zona horria uma hora adiante da UTC:ALTER USER cristina VALID UNTIL May 4 12:00:00 1998 +1;

Dar ao usurio poderes para criar outros usurios e novos bancos de dados:ALTER USER marcela CREATEUSER CREATEDB;

2

ALTER USER

CompatibilidadeSQL92 No existe o comando ALTER USER no SQL92. O padro deixa a denio dos usurios para a implementao.

3

ANALYZENameANALYZE coleta estatsticas sobre um banco de dados

SynopsisANALYZE [ VERBOSE ] [ tabela [ (coluna [, ...] ) ] ]

Entradas VERBOSE Ativa a exibio de mensagens de progresso. tabela O nome de uma tabela especca a ser analisada. Por padro todas as tabelas. coluna O nome de uma coluna especca a ser analisada. Por padro todas as colunas.

SadasANALYZE

O comando est concludo.

DescrioO comando ANALYZE coleta estatsticas sobre o contedo das tabelas do PostgreSQL, e armazena os resultados na tabela do sistema pg_statistic. Posteriormente, o otimizador de consultas utiliza estas estatsticas para auxiliar na determinao do plano de execuo mais eciente para as consultas. Sem nenhum parmetro, o comando ANALYZE analisa todas as tabelas do banco de dados em uso. Com parmetro, o comando ANALYZE analisa somente uma tabela. possvel ainda fornecer uma relao de nomes de colunas e, neste caso, somente as estatsticas para estas colunas so atualizadas.

1

ANALYZE Notas Aconselha-se executar o comando ANALYZE periodicamente, ou logo aps realizar uma alterao signicativa no contedo de uma tabela. Estatsticas precisas auxiliam o otimizador na escolha do plano de consulta mais apropriado e, portanto, a melhorar o tempo do processamento da consulta. Uma estratgia habitual executar VACUUM e ANALYZE uma vez por dia em perodos de pouca carga. Ao contrrio do comando VACUUM FULL, o comando ANALYZE requer somente um bloqueio de leitura na tabela podendo, portanto, ser executado em conjunto com outras atividades na tabela. Para tabelas grandes, o comando ANALYZE pega amostras aleatrias do contedo da tabela, em vez de examinar todas as linhas. Esta estratgia permite que mesmo tabelas muito grandes sejam analisadas em curto espao de tempo. Observe, entretanto, que as estatsticas so apenas aproximadas e vo ser um pouco diferentes cada vez que o comando ANALYZE for executado, mesmo que o contedo da tabela no se altere, podendo ocasionar pequenas mudanas no custo estimado pelo otimizador mostrado no comando EXPLAIN. As estatsticas coletadas geralmente incluem a relao dos valores com maior incidncia de cada coluna e um histograma mostrando a distribuio aproximada dos dados de cada coluna. Um, ou ambos, podem ser omitidos se o comando ANALYZE consider-los irrelevantes (por exemplo, em uma coluna com chave nica no existem valores repetidos) ou se o tipo de dado da coluna no suportar os operadores apropriados. Existem mais informaes sobre as estatsticas no Guia do Usurio. A extenso da anlise pode ser controlada ajustando-se as estatsticas por coluna atravs do comando ALTER TABLE ALTER COLUMN SET STATISTICS (consulte o comando ALTER TABLE). O valor especicado dene o nmero mximo de entradas da lista de valores com maior incidncia e o nmero mximo de barras no histograma. O valor padro 10, mas pode ser ajustado para mais, ou para menos, para balancear a preciso das estimativas do otimizador contra o tempo dispendido para a execuo do comando ANALYZE e a quantidade de espao ocupado pela tabela pg_statistic. Em particular, especicando-se o valor zero desativa a coleta de estatsticas para a coluna, podendo ser til para colunas que nunca so usadas como parte das clusulas WHERE, GROUP BY ou ORDER BY das consultas, porque as estatsticas destas colunas no tm utilidade para o otimizador. O maior nmero de estatsticas, entre as colunas sendo analisadas, determina o nmero de linhas amostradas para preparar as estatsticas. Aumentando o nmero de estatsticas por coluna causa um aumento proporcional no tempo e espao necessrio para executar o comando ANALYZE.

CompatibilidadeSQL92 No existe o comando ANALYZE no SQL92.

2

BEGINNameBEGIN inicia um bloco de transao

SynopsisBEGIN [ WORK | TRANSACTION ]

Entradas WORK TRANSACTION Palavras chave opcionais. No produzem nenhum efeito.

SadasBEGIN

Signica que uma nova transao comeou.NOTICE: BEGIN: already a transaction in progress

Indica que uma transao est sendo executada. A transao corrente no afetada.

DescrioPor padro, o PostgreSQL executa as transaes em modo no encadeado (tambm conhecido por autocommit [auto-efetivao] em outros sistemas de banco de dados). Em outras palavras, cada comando executado em sua prpria transao e uma efetivao implicitamente realizada ao nal do comando (se a execuo terminar com sucesso, seno um rollback realizado). O comando BEGIN inicia uma transao no modo encadeado, ou seja, todas as declaraes aps o comando BEGIN so executadas como sendo uma nica transao, at que seja encontrado um comando explcito COMMIT ou ROLLBACK, ou a execuo ser abortada. Os comandos so executados mais rpido no modo encadeado, porque cada incio/efetivao de transao requer uma atividade signicativa de CPU e de disco. A execuo de vrios comandos em uma nica transao tambm requerida por motivo de consistncia, quando vrias tabelas relacionadas so modicadas.

1

BEGIN O nvel de isolamento padro da transao no PostgreSQL READ COMMITTED, onde os comandos dentro de uma transao enxergam apenas as mudanas efetivadas antes da execuo do comando. Por esse motivo deve-se utilizar SET TRANSACTION ISOLATION LEVEL SERIALIZABLE logo aps o comando BEGIN se for necessrio um nvel de isolamento mais rigoroso para a transao. No modo SERIALIZABLE os comandos enxergam apenas as modicaes efetivadas antes do incio de toda a transao (na verdade, antes da execuo do primeiro comando DML em uma transao serializvel). Se a transao for efetivada, o PostgreSQL garante que todas as atualizaes so realizadas ou, ento, que nenhuma delas realizada. As transaes possuem a propriedade ACID (atomicidade, consistncia, isolamento e durabilidade) padro. Notas Consulte o comando LOCK para obter mais informaes sobre o bloqueio de tabelas dentro de uma transao. Utilize COMMIT ou ROLLBACK para terminar a transao.

UtilizaoPara iniciar uma transao:BEGIN WORK;

CompatibilidadeSQL92 O comando BEGIN uma extenso do PostgreSQL linguagem.. No existe o comando BEGIN explcito no SQL92; o incio de uma transao sempre implcito, e termina pelo comando COMMIT ou pelo comando ROLLBACK.Note: Muitos sistemas de banco de dados relacionais oferecem a funcionalidade de auto-efetivao (autocommit) por convenincia.

Lembre-se que a palavra chave BEGIN utilizada para uma nalidade diferente no SQL embutido. Deve-se ter muito cuidado com relao semntica da transao ao se portar aplicativos de banco de dados. O SQL92 requer que SERIALIZABLE seja o nvel de isolamento padro das transaes.

2

CHECKPOINTNameCHECKPOINT fora um ponto de controle no log de transao

SynopsisCHECKPOINT

DescrioA gravao prvia do registro no log (Write-Ahead Logging/WAL) coloca periodicamente um ponto de controle (checkpoint) no log de transao (Para ajustar o intervalo automtico do ponto de controle, consulte as opes de congurao em tempo de execuo CHECKPOINT_SEGMENTS e CHECKPOINT_TIMEOUT). O comando CHECKPOINT fora um ponto de controle imediato ao ser executado, sem aguardar o ponto de controle pr-denido. Um ponto de controle um ponto na seqncia do log de transao no qual todos os arquivos de dados foram atualizados para reetir a informao no log. Todos os dados so escritos no disco. Consulte o Guia do Administrador do PostgreSQL para obter mais informaes sobre o WAL. Somente os superusurios podem executar o comando CHECKPOINT. A utilizao deste comando durante uma operao normal no esperada .

Consulte tambmGuia do Administrador do PostgreSQL

CompatibilidadeO comando CHECKPOINT uma extenso do PostgreSQL linguagem.

1

CLOSENameCLOSE fecha o cursor

SynopsisCLOSE cursor

Entradas cursor O nome do cursor aberto a ser fechado.

SadasCLOSE

Mensagem retornada se o cursor for fechado com sucesso.NOTICE PerformPortalClose: portal "cursor" not found

Esta advertncia exibida quando o cursor no est declarado ou se j tiver sido fechado.

DescrioO comando CLOSE libera os recursos associados a um cursor aberto. Aps o cursor ser fechado, no permitida nenhuma operao posterior sobre o mesmo. O cursor deve ser fechado quando no for mais necessrio. Um fechamento implcito executado para todos os cursores abertos quando a transao terminada pelo comando COMMIT ou pelo comando ROLLBACK. Notas O PostgreSQL no possui um comando explcito OPEN cursor; o cursor considerado aberto ao ser declarado. Use o comando DECLARE para declarar um cursor.

1

CLOSE

UtilizaoFechar o cursor cur_emp:CLOSE cur_emp;

CompatibilidadeSQL92 O comando CLOSE totalmente compatvel com o SQL92.

2

CLUSTERNameCLUSTER agrupa uma tabela de acordo com um ndice

SynopsisCLUSTER nome_do_ndice ON nome_da_tabela

Entradas nome_do_ndice O nome de um ndice. nome_da_tabela O nome de uma tabela.

SadasCLUSTER

O agrupamento foi realizado com sucesso.

ERROR: relation

numero_tabelarelao

inherits "nome_da_tabela"

ERROR: Relation nome_da_tabela does not exist!

DescrioO comando CLUSTER instrui o PostgreSQL para agrupar a tabela especicada por nome_da_tabela baseado no ndice especicado por nome_do_ndice. necessrio que o ndice tenha sido criado anteriormente na tabela nome_da_tabela. Quando a tabela agrupada, ela sicamente reordenada baseado na informao do ndice. O agrupamento esttico. Em outras palavras, assim que a tabela for atualizada as modicaes no sero agrupadas. Nenhuma tentativa feita para manter as novas instncias ou as tuplas atualizadas agrupadas. Se for desejado, a tabela pode ser reagrupada manualmente executando-se o comando novamente.

1

CLUSTER Notas Na realidade a tabela copiada para uma tabela temporria ordenada pelo ndice e, em seguida, renomeada para o seu nome original. Por esta razo, todas as permisses de acesso concedidas e os outros ndices so perdidos quando o agrupamento realizado. No caso de se estar acessando uma nica linha da tabela aleatoriamente, a ordem fsica dos dados da tabela no importante. Entretanto, havendo uma tendncia para acessar alguns dados mais do que outros, se existir um ndice que agrupa estes dados haver benefcio se o comando CLUSTER for utilizado. Outra situao em que o comando CLUSTER til so os casos em que se usa o ndice para acessar vrias linhas da tabela. Se for solicitada uma faixa de valores indexados de uma tabela, ou um nico valor indexado possuindo muitas linhas que correspondam a este valor, o comando CLUSTER ajuda porque quando o ndice identica a pgina da primeira linha todas as outras linhas estaro provavelmente nesta mesma pgina, reduzindo o acesso ao disco e acelerando a consulta. Existem duas maneiras de se agrupar os dados. A primeira com o comando CLUSTER, que reordena a tabela original na ordem do ndice especicado. Este procedimento pode ser lento para tabelas grandes porque as linhas so lidas da tabela na ordem do ndice e, se a tabela no estiver ordenada, as linhas estaro em pginas aleatrias, fazendo uma pgina do disco ser lida para cada linha movida. O PostgreSQL possui um cache, mas a maioria das tabelas grandes no cabem no cache. Outra maneira de agrupar a tabela usarSELECT lista_de_colunas INTO TABLE nova_tabela FROM nome_da_tabela ORDER BY lista_de_colunas

que usa o cdigo de ordenao do PostgreSQL da clusula ORDER BY para fazer o papel do ndice, o que muito mais rpido para dados no ordenados. Em seguida a tabela original deve ser removida, o comando ALTER TABLE...RENAME deve ser utilizado para mudar o nome da nova_tabela para o nome da tabela original, e os ndices da tabela devem ser recriados. O nico problema que o OID no preservado. Deste momento em diante o comando CLUSTER dever ser rpido, porque a maior parte dos dados da tabela estar ordenada, e o ndice existente usado.

UtilizaoAgrupar a relao empregados baseado no atributo salrio:CLUSTER emp_ind ON emp;

CompatibilidadeSQL92 No existe o comando CLUSTER no SQL92.

2

COMMENTNameCOMMENT cria ou altera o comentrio de um objeto

SynopsisCOMMENT ON [ [ DATABASE | INDEX | RULE | SEQUENCE | TABLE | TYPE | VIEW ] nome_do_objeto | COLUMN nome_da_tabela.nome_da_coluna | AGGREGATE nome_da_agregao (tipo_da_agregao) | FUNCTION nome_da_funo (arg1, arg2, ...) | OPERATOR op (leftoperand_type rightoperand_type) | TRIGGER nome_do_gatilho ON nome_da_tabela ] IS texto

Entradas nome_do_objeto, nome_da_tabela, nome_da_coluna, nome_da_agregao, nome_da_funo, op e nome_do_gatilho O nome do objeto ao qual o comentrio se refere. texto O comentrio a ser adicionado.

SadasCOMMENT

Mensagem retornada se o comando for executado com sucesso.

DescrioO comando COMMENT armazena um comentrio sobre um objeto do banco de dados. Os comentrios podem ser facilmente acessados atravs dos comandos \dd, \d+ e \l+ do psql. Outras interfaces de

1

COMMENT usurio podem acessar os comentrios utilizando as mesmas funes nativas usadas pelo psql, que so:obj_description() e col_description().

Para modicar um comentrio basta executar novamente o comando COMMENT para o mesmo objeto. Somente um nico comentrio armazenado para cada objeto. Para excluir um comentrio escreva NULL no lugar do texto. O comentrio automaticamente excludo quando o objeto excludo. Deve ser observado que no existe atualmente nenhum mecanismo de segurana para os comentrios: qualquer usurio conectado ao banco de dados pode ver todos os comentrios dos objetos do banco de dados (embora somente um superusurio possa modicar comentrios de objetos que no lhe pertencem). Portanto, no coloque informaes condenciais nos comentrios.

UtilizaoAdicionar um comentrio tabela minha_tabela:COMMENT ON minha_tabela IS Esta tabela minha.;

Alguns outros exemplos:COMMENT COMMENT COMMENT COMMENT COMMENT COMMENT COMMENT COMMENT COMMENT COMMENT COMMENT COMMENT ON ON ON ON ON ON ON ON ON ON ON ON

DATABASE bd_desenv IS Banco de dados de desenvolvimento; INDEX idx_func_id IS Garante a unicidade do identificador do funcionrio RULE upd_func IS Registra as atualizaes dos registros dos funcionrios SEQUENCE seq_func IS Gera a chave primria dos funcionrios; TABLE tbl_func IS Cadastro dos funcionrios; TYPE tipo_cn IS Suporte a nmeros complexos; VIEW vis_dep_custo IS Viso dos custos dos departamentos; COLUMN tbl_func.id_func IS Identificador do funcionrio; AGGREGATE agreg_var (double precision) IS Calcula a varincia da amostra FUNCTION func_romano (int) IS Retorna o nmero em algarismos romanos; OPERATOR ^ (text, text) IS Realiza a interseo de dois textos; TRIGGER gat_func ON tbl_func IS Utilizado para integridade referencial;

CompatibilidadeSQL92 No existe o comando COMMENT no SQL92.

2

COMMITNameCOMMIT efetiva a transao corrente

SynopsisCOMMIT [ WORK | TRANSACTION ]

Entradas WORK TRANSACTION Palavras chave opcionais. No produzem nenhum efeito.

SadasCOMMIT

Mensagem retornada se a transao for efetivada com sucesso.NOTICE: COMMIT: no transaction in progress

Se no houver nenhuma transao sendo executada.

DescrioO comando COMMIT efetiva a transao sendo executada. Todas as modicaes efetuadas pela transao se tornam visveis para os outros, e existe a garantia de permanecerem se uma falha ocorrer. Notas As palavras chave WORK e TRANSACTION so informativas podendo ser omitidas. Use o ROLLBACK para desfazer a transao.

1

COMMIT

UtilizaoPara tornar todas as modicaes permanentes:COMMIT WORK;

CompatibilidadeSQL92 O SQL92 somente especica as duas formas COMMIT e COMMIT WORK. Fora isso a compatibilidade total.

2

COPYNameCOPY copia dados entre arquivos e tabelas

SynopsisCOPY [ BINARY ] tabela [ WITH OIDS ] FROM { nome_do_arquivo | stdin } [ [USING] DELIMITERS delimitador ] [ WITH NULL AS cadeia de caracteres nula ] COPY [ BINARY ] tabela [ WITH OIDS ] TO { nome_do_arquivo | stdout } [ [USING] DELIMITERS delimitador ] [ WITH NULL AS cadeia de caracteres nula ]

Entradas BINARY Altera o comportamento da formatao dos campos, fazendo todos os dados serem escritos ou lidos no formato binrio em vez de texto. As opes DELIMITERS e WITH NULL no so pertinentes para o formato binrio. tabela O nome de uma tabela existente. WITH OIDS Especica a cpia do identicador interno do objeto (OID) para cada linha. nome_do_arquivo O nome do arquivo de entrada ou de sada no Unix, junto com o caminho absoluto.stdin

Especica que a entrada vem do aplicativo cliente.stdout

Especica que a sada vai para o aplicativo cliente. delimitador O caractere que separa os campos dentro de cada linha do arquivo. cadeia de caracteres nula A cadeia de caracteres que representa o valor nulo. O padro \N (contrabarra-N). Pode-se preferir uma cadeia de caracteres vazia, por exemplo.

1

COPYNote: Durante a leitura, qualquer item de dado que corresponda a esta cadeia de caracteres armazenado com o valor nulo, portanto deve-se ter certeza de estar usando para ler a mesma cadeia de caracteres que foi usada para escrever.

SadasCOPY

A cpia terminou com sucesso.ERROR: razo

A cpia falhou pela razo mostrada na mensagem de erro.

DescrioO comando COPY copia dados entre tabelas do PostgreSQL e arquivos do sistema operacional. O comando COPY TO copia todo o contedo de uma tabela para um arquivo, enquanto o COPY FROM copia os dados de um arquivo para uma tabela (adicionando os dados aos existentes na tabela). O comando COPY com um nome de arquivo instrui o servidor PostgreSQL para ler ou escrever diretamente de um arquivo. O arquivo deve ser acessvel ao servidor e o nome deve ser especicado a partir do ponto de vista do servidor. Quando stdin ou stdout for especicado, os dados uiro entre o cliente e o servidor.Tip: No confunda o comando COPY com a instruo \copy do psql. O \copy executa COPY FROM stdin ou COPY TO stdout e, ento, l/grava os dados em um arquivo acessvel ao cliente psql. Portanto, a acessibilidade e os direitos de acesso dependem do cliente e no do servidor, quando o\copy utilizado.

Notas O comando COPY s pode ser utilizado em tabelas, no pode ser utilizado em vises. A palavra chave BINARY fora todos os dados serem escritos/lidos no formato binrio em vez de texto. um pouco mais rpido do que a cpia normal, mas o arquivo binrio produzido no portvel entre mquinas com arquiteturas diferentes. Por padro, a cpia no formato texto usa o caractere de tabulao ("\t") como delimitador de campos. O delimitador de campo pode ser mudado para qualquer outro caractere nico usando a clusula USING

2

COPY DELIMITERS. Os caracteres nos campos de dados que forem idnticos ao caractere delimitador sero precedidos por uma contrabarra. necessrio possuir permisso de leitura em todas as tabelas cujos valores so lidos pelo COPY, e permisso para inserir ou atualizar em uma tabela onde valores so inseridos pelo COPY. O servidor tambm necessita de permisses apropriadas no Unix para todos os arquivos lidos ou escritos pelo COPY. O comando COPY TO no invoca as regras nem atua no padro da coluna. Invoca os gatilhos e as restries de vericao. O COPY pra de executar no primeiro erro, o que no deve acarretar problemas para o COPY FROM, mas a tabela de destino j vai ter recebido linhas no caso do COPY TO. Estas linhas no so visveis nem acessveis, mas ocupam espao em disco, podendo ocasionar o desperdcio de uma grande quantidade de espao em disco se o erro ocorrer durante a cpia de uma grande quantidade de dados. Deve-se executar o comando VACUUM para recuperar o espao desperdiado. Os arquivos declarados no comando COPY so lidos ou escritos diretamente pelo servidor, e no pelo aplicativo cliente. Portanto, devem residir ou serem acessveis pela mquina servidora de banco de dados, e no pela estao cliente. Os arquivos devem ser acessveis e poder ser lidos ou escritos pelo usurio do PostgreSQL (o ID do usurio sob o qual o servidor processa), e no pelo cliente. O COPY com nome de arquivo s permitido aos superusurios do banco de dados, porque permite ler e escrever em qualquer arquivo que o servidor possua privilgio de acesso.Tip: A instruo \copy do psql l e escreve arquivos na estao cliente usando as permisses do cliente, portanto no restrita aos superusurios.

Recomenda-se que os nomes dos arquivos usados no comando COPY sejam sempre especicados como um caminho absoluto, sendo exigido pelo servidor no caso do COPY TO, mas para COPY FROM existe a opo de ler um arquivo especicado pelo caminho relativo. O caminho interpretado como sendo relativo ao diretrio de trabalho do servidor (algum lugar abaixo de $PGDATA), e no relativo ao diretrio de trabalho do cliente.

Formatos dos ArquivosFormato Texto Quando o comando COPY utilizado sem a opo BINARY, o arquivo lido ou escrito um arquivo de texto com uma linha para cada linha da tabela. As colunas (atributos) so separadas na linha pelo caractere delimitador. Os valores dos atributos so cadeias de caracteres geradas pela funo de sada, ou aceitveis pela funo de entrada, para cada tipo de dado de atributo. A cadeia de caracteres nula especicada utilizada no lugar dos atributos que so nulos. Se WITH OIDS for especicado, o OID lido ou escrito como a primeira coluna, precedendo as colunas de dado do usurio (Vai acontecer um erro se WITH OIDS for especicado para uma tabela que no possua OIDs). O m dos dados pode ser representado por uma nica linha contendo apenas contrabarra-ponto (\.). Um marcador de m de dados no necessrio ao se ler de um arquivo Unix, porque o m de arquivo serve

3

COPY perfeitamente bem; mas um marcador de m dos dados deve ser fornecido para copiar os dados de ou para um aplicativo cliente. O caractere contrabarra (\) pode ser usado nos dados do comando COPY para evitar que caracteres dos dados sejam interpretados como delimitadores de linha ou de coluna. Em particular, os seguintes caracteres devem ser precedidos por uma contrabarra se aparecerem como parte do valor de um atributo: a prpria contrabarra, a nova-linha e o caractere delimitador corrente. As seguintes seqncias especiais de contrabarra so reconhecidas pelo comando COPY FROM: Seqncia\b \f \n \r \t \v \dgitos

Representa Backspace (ASCII 8) Avano de pgina (ASCII 12) Nova-linha (ASCII 10) Retorno do carro (ASCII 13) Tabulao (ASCII 9) Tabulao Vertical (ASCII 11) A contrabarra seguida por um a trs dgitos octais especica o caractere com este cdigo numrico

Atualmente o COPY TO nunca gera uma seqncia contrabarra-dgitos-octais, mas as outras seqncias de contrabarra listadas acima so utilizadas para estes caracteres de controle. Nunca coloque uma contrabarra antes dos caracteres de dado N ou ponto (.), seno os dois sero interpretados, erroneamente, como a cadeia de caracteres nula padro e o marcador de m dos dados, respectivamente. Qualquer outro caractere no mencionado na tabela acima interpretado como representando a si prprio. fortemente recomendado que os aplicativos que geram dados para o COPY convertam os caracteres de nova-linha e de retorno-de-carro presentes nos dados em seqncias \n e \r respectivamente. No presente momento (PostgreSQL 7.2 e verses mais antigas) possvel se representar um retorno-de-carro nos dados sem nenhuma seqncia especial, e representar a nova-linha nos dados por uma contrabarra seguida por um caractere de nova-linha. Entretanto, estas representaes no sero aceitas por padro nas verses futuras. Observe que o m de cada linha marcado por um caractere de nova-linha no estilo Unix (\n). Atualmente, o COPY FROM no se comporta de forma adequada quando o arquivo especicado contm o m de linha no estilo MS-DOS ou Mac. Espera-se que isto mude em verses futuras.

Formato Binrio O formato do arquivo usado pelo COPY BINARY mudou no PostgreSQL v7.1. O novo formato do arquivo consiste em uma cabealho, zero ou mais tuplas e um rodap. Cabealho do Arquivo O cabealho do arquivo consiste de 24 bytes para campos xos, seguidos por uma rea de extenso do

4

COPY cabealho de tamanho varivel. Os campos xos so: Assinatura A seqncia de 12 bytes PGBCOPY\n\377\r\n\0 --- observe que o nulo uma parte requerida da assinatura (A assinatura foi projetada para permitir a fcil identicao de arquivos corrompidos por uma transferncia no apropriada. Esta assinatura modicada por ltros de traduo de nova-linha, nulos suprimidos, bits altos suprimidos, ou mudanas de paridade). Campo de disposio de inteiro Constante int32 0x01020304 na ordem dos bytes de origem. Se uma ordem errada dos bytes for detectada aqui, o leitor poder se envolver em uma mudana na ordem dos bytes dos campos posteriores. Campo de sinalizadores Mscara de bits int32 usada para caracterizar aspectos importantes do formato do arquivo. Os bits so numerados de 0 (LSB) at 31 (MSB) --- observe que este campo armazenado na ordem dos bytes da plataforma de origem, assim como todos os campos inteiro seguintes. Os bits 16-31 so reservados para caracterizar questes crticas do formato do arquivo; o leitor deve abortar se for encontrado um bit no esperado neste intervalo. Os bits 0-15 so reservados para sinalizar questes de formato precedente-compatveis; o leitor deve simplesmente ignorar qualquer bit no esperado neste intervalo. Atualmente somente um bit sinalizador est denido, os outros devem ser zero: Bit 16 Se for igual a 1 os OIDs esto includos no arquivo; 0 caso contrrio.

Comprimento da rea de extenso do cabealho Valor int32 do comprimento em bytes do restante do cabealho, no se incluindo. Na verso inicial ser zero, com a primeira tupla vindo a seguir. Mudanas futuras no formato podem permitir a presena de dados adicionais no cabealho. O leitor deve simplesmente desprezar qualquer dado da rea de extenso do cabealho que no souber o que fazer com a mesma.

A rea de extenso do cabealho imaginada como contendo uma seqncia de blocos auto-identicantes. O campo de sinalizadores no tem por nalidade informar os leitores o que existe na rea de extenso. O projeto especco do contedo da rea de extenso do cabealho foi deixado para uma verso futura. Este projeto permite tanto adies de cabealho precedente-compatveis (adicionar blocos de extenso de cabealho, ou sinalizar com bits de baixa-ordem) e mudanas no precedente-compatveis (usar bits de alta-ordem para sinalizar estas mudanas, e adicionar dados de apoio para a rea de extenso se for necessrio).

Tuplas Cada tupla comea com um contador (int16) do nmero de campos na tupla (atualmente todas as tuplas da tabela possuem o mesmo contador, mas isto pode no ser verdade para sempre). Ento, repetido para

5

COPY cada campo da tupla, existe uma palavra tipo-comprimento (typlen, int16) possivelmente seguida por um campo de dados. O campo tipo-comprimento interpretado como: Zero O campo nulo. Nenhum dado segue. 0

O campo possui um tipo de dado de comprimento xo. Exatamente N bytes de dados seguem a palavra tipo-comprimento (typlen). -1 O campo possui um tipo de dado de comprimento varivel (varlena). Os prximos quatro bytes so o cabealho, que contm o valor do comprimento total incluindo a si prprio. -1

Reservado para uso futuro.

Para campos no nulos, o leitor pode vericar se o tipo-comprimento (typlen) corresponde ao tipocomprimento esperado para a coluna de destino, fornecendo uma maneira simples, mas muito til, de vericar se o dado est como o esperado. No existe nenhum preenchimento de alinhamento ou qualquer dado extra entre os campos. Tambm observe que o formato no distingue se um tipo de dado passado por referncia ou passado por valor. Estas duas disposies so deliberadas: elas ajudam a melhorar a portabilidade dos arquivos (embora problemas relacionados com a ordem dos bytes ou o formato do ponto utuante podem impedir a troca de arquivos binrios entre computadores). Se os OIDs so includos no arquivo, o campo OID segue imediatamente a palavra contador-de-campos. um campo normal, exceto que no est includo no contador-de-campos. Em particular possui um tipocomprimento --- isto permite tratar OIDs de 4 bytes versus 8 bytes sem muita diculdade, e permite os OIDs serem mostrados como NULL se por acaso for desejado.

Rodap do Arquivo O rodap do arquivo consiste em uma palavra int16 contendo -1, sendo facilmente distinguvel da palavra contador-de-campos da tupla. O leitor deve relatar um erro se a palavra contador-de-campos no for -1 nem o nmero esperado de colunas, fornecendo uma vericao extra com relao a perda de sincronizao com os dados.

UtilizaoO exemplo a seguir copia uma tabela para a sada padro, usando a barra vertical (|) como delimitador de campo:COPY paises TO stdout USING DELIMITERS |;

6

COPY

Para copiar dados de um arquivo Unix para a tabela paises:COPY paises FROM /usr1/proj/bray/sql/paises.txt;

Abaixo est um exemplo de dados apropriados para se copiar para a tabela a partir de stdin (por isso possui a seqncia de terminao na ltima linha):AF AL DZ ZM ZW \. AFGHANISTAN ALBANIA ALGERIA ZAMBIA ZIMBABWE

Observe que os espaos em branco em cada linha so, na verdade, o caractere de tabulao. Abaixo so os mesmos dados, escritos no formato binrio em uma mquina Linux/i586. Os dados mostrados foram ltrados atravs do utilitrio do Unix od -c. A tabela possui trs campos: o primeiro char(2); o segundo text; o terceiro integer. Todas as linhas possuem um valor nulo no terceiro campo.0000000 P G B C O P 0000020 \0 \0 \0 \0 \0 \0 0000040 A F 377 377 017 \0 0000060 T A N \0 \0 003 0000100 377 \v \0 \0 \0 A 0000120 377 377 006 \0 \0 \0 0000140 G E R I A \0 0000160 M 377 377 \n \0 \0 0000200 \0 377 377 006 \0 \0 0000220 I M B A B W Y \n 377 \r \n \0 004 003 002 001 \0 \0 003 \0 377 377 006 \0 \0 \0 \0 \0 A F G H A N I S \0 377 377 006 \0 \0 \0 A L 377 L B A N I A \0 \0 003 \0 D Z 377 377 \v \0 \0 \0 A L \0 003 \0 377 377 006 \0 \0 \0 Z \0 Z A M B I A \0 \0 003 \0 Z W 377 377 \f \0 \0 \0 Z E \0 \0 377 377

CompatibilidadeSQL92 No existe o comando COPY no SQL92.

7

CREATE AGGREGATENameCREATE AGGREGATE dene uma nova funo de agregao

SynopsisCREATE AGGREGATE nome ( BASETYPE = tipo_dado_entrada, SFUNC = func_trans_estado, STYPE = tipo_dado_estado [ , FINALFUNC = func_final ] [ , INITCOND = cond_inicial ] )

Entradas nome O nome da funo de agregao a ser criada. tipo_dado_entrada O tipo do dado de entrada sobre o qual esta funo de agregao opera. Pode ser especicado como "ANY" para uma funo de agregao que no examina seus valores de entrada (um exemplo a funo count(*)). func_trans_estado O nome da funo de transio de estado a ser chamada para cada valor dos dados da entrada. Normalmente esta uma funo com dois argumentos, o primeiro sendo do tipo tipo_dado_estado e o segundo do tipo tipo_dado_entrada. Outra possibilidade, para uma funo de agregao que no examina seus valores de entrada, a funo possuir apenas um argumento do tipo tipo_dado_estado. Em qualquer um dos casos a funo deve retornar um valor do tipo tipo_dado_estado. Esta funo recebe o valor atual do estado e o item atual de dado da entrada, e retorna o valor do prximo estado. tipo_dado_estado O tipo de dado do valor do estado da agregao. func_final O nome da funo nal chamada para calcular o resultado da agregao aps todos os dados da entrada terem sido percorridos. A funo deve possuir um nico argumento do tipo tipo_dado_estado. O tipo de dado do valor da agregao denido pelo tipo do valor retornado por esta funo. Se func_final no for especicado, ento o valor do estado nal utilizado como sendo o resultado da agregao, e o tipo da sada ca sendo o tipo_dado_estado. cond_inicial A atribuio inicial para o valor do estado. Deve ser uma constante literal na forma aceita pelo tipo de dado tipo_dado_estado. Se no for especicado, o valor do estado comea com NULL.

1

CREATE AGGREGATE

SadasCREATE

Mensagem retornada se o comando for executado com sucesso.

DescrioO comando CREATE AGGREGATE permite ao usurio ou ao programador estender as funcionalidades do PostgreSQL denindo novas funes de agregao. Algumas funes de agregao para tipos base, como min(integer) e avg(double precision) esto presentes na distribuio base. Se forem denidos tipos novos, ou se houver a necessidade de uma funo de agregao que no esteja presente, ento o comando CREATE AGGREGATE pode ser utilizado para criar as funcionalidades desejadas. Uma funo de agregao identicada pelo seu nome e pelo tipo de dado da entrada. Duas funes de agregao podem possuir o mesmo nome no caso de operarem sobre dados de entrada de tipos diferentes. Para evitar confuso, no crie uma funo comum com o mesmo nome e tipo de dado de entrada de uma funo de agregao. Uma funo de agregao constituda de uma ou duas funes comuns: uma funo de transio de estado func_trans_estado, e outra funo, opcional, para a realizao dos clculos nais func_final. Estas funes so utilizadas da seguinte maneira:func_trans_estado( estado-interno, prximo-item-dado ) ---> prximo-estado-interno func_final( estado-interno ) ---> valor-da-agregao

O PostgreSQL cria uma varivel temporria do tipo tipo_dado_estado para armazenar o estado interno atual da agregao. Para cada item de dado da entrada, a funo de transio de estado chamada para calcular o novo valor do estado interno. Aps todos os dados terem sido processados, a funo nal chamada uma vez para calcular o valor de sada da agregao. Se no houver nenhuma funo nal, ento o valor do estado nal retornado. A funo de agregao pode possuir uma condio inicial, ou seja, um valor inicial para o valor de estado interno. Este valor especicado e armazenado no banco de dados em um campo do tipo text, mas deve possuir uma representao externa vlida de uma constante do tipo de dado do estado. Se no for especicado, ento o valor do estado comea com NULL. Se a funo de transio de estado for declarada como strict, ento no poder ser chamada com valores da entrada nulos. Para este tipo de funo de transio, a execuo da agregao realizada da seguinte maneira. Valores da entrada nulos so ignorados (a funo no chamada e o valor do estado anterior permanece). Se o valor do estado inicial for nulo, ento o primeiro valor de entrada que no for nulo

2

CREATE AGGREGATE substitui o valor do estado, e a funo de transio chamada a partir do segundo valor de entrada que no for nulo. Este procedimento til para implementar funes de agregao como max. Observe que este comportamento somente est disponvel quando o tipo_dado_estado for do mesmo tipo do tipo_dado_entrada. Quando estes tipos de dado forem diferentes, dever ser fornecido um valor no nulo para a condio inicial, ou utilizar uma funo de transio que no seja estrita. Se a funo de transio de estado no for estrita ento ser chamada, incondicionalmente, para cada valor da entrada, devendo ser capaz de lidar com valores nulos da entrada e valores nulos de transio. Esta opo permite ao autor da funo de agregao ter pleno controle sobre os valores nulos. Se a funo nal for declarada strict, esto no ser chamada quando o valor do estado nal for nulo; em vez disso, um resultado nulo ser produzido automaticamente ( claro que este apenas o comportamento normal de funes estritas). De qualquer forma, a funo nal tem sempre a opo de retornar nulo. Por exemplo, a funo nal para avg retorna nulo quando no h nenhum valor de entrada. Notas Use o comando DROP AGGREGATE para excluir funes de agregao. Os parmetros do comando CREATE AGGREGATE podem ser escritos em qualquer ordem, e no apenas na ordem mostrada acima.

UtilizaoConsulte o captulo sobre funes de agregao no Guia do Programador do PostgreSQL para ver exemplos completos sobre a sua utilizao.

CompatibilidadeSQL92 O comando CREATE AGGREGATE uma extenso do PostgreSQL linguagem. No existe o comando CREATE AGGREGATE no SQL92.

3

CREATE CONSTRAINT TRIGGERNameCREATE CONSTRAINT TRIGGER dene um novo gatilho de restrio

SynopsisCREATE CONSTRAINT TRIGGER nome AFTER eventos ON relao restrio atributos FOR EACH ROW EXECUTE PROCEDURE funo ( args )

Entradas nome O nome do gatilho de restrio. eventos As categorias dos eventos para as quais este gatilho deve ser disparado. relao O nome da tabela que dispara o gatilho. restrio Especicao da restrio. atributos Atributos da restrio. funo(args) Funo a ser chamada como parte do processamento do gatilho.

SadasCREATE CONSTRAINT

Mensagem retornada se o comando for executado com sucesso.

1

CREATE CONSTRAINT TRIGGER

DescrioO comando CREATE CONSTRAINT TRIGGER utilizado de dentro do comando CREATE/ALTER TABLE e pelo pg_dump para criar gatilhos especiais para a integridade referencial. No h a inteno de ser para uso geral.

2

CREATE DATABASENameCREATE DATABASE cria um banco de dados novo

SynopsisCREATE DATABASE nome [ WITH [ LOCATION = caminho ] [ TEMPLATE = gabarito ] [ ENCODING = codificao ] ]

Entradas nome O nome do banco de dados a ser criado. caminho Um local alternativo no sistema de arquivos onde ser armazenado o banco de dados, especicado como uma cadeia de caracteres; ou DEFAULT para utilizar o local padro. gabarito Nome do banco de dados a ser usado como gabarito para a criao do novo banco de dados, ou DEFAULT para utilizar o banco de dados de gabarito padro (template1). codificao Mtodo de codicao multibyte a ser utilizado no novo banco de dados. Especique o nome como uma cadeia de caracteres (por exemplo, SQL_ASCII), ou o nmero inteiro da codicao, ou DEFAULT para utilizar a codicao padro.

SadasCREATE DATABASE

Mensagem retornada se o comando for executado com sucesso.ERROR: user nome_do_usurio is not allowed to create/drop databases

necessrio possuir o privilgio especial CREATEDB para criar bancos de dados. Consulte o comando CREATE USER.ERROR: createdb: database "nome" already exists

Esta mensagem ocorre se o banco de dados com o nome especicado j existir.

1

CREATE DATABASEERROR: database path may not contain single quotes

O local do banco de dados especicado em caminho no pode conter apstrofos (). Isto necessrio para que o interpretador de comandos, que vai criar o diretrio do banco de dados, possa executar com segurana.ERROR: CREATE DATABASE: may not be called in a transaction block

Havendo um bloco de transao explcito sendo executado no possvel utilizar o comando CREATEDATABASE. Primeiro a transao deve terminar. ERROR: Unable to create database directory caminho. ERROR: Could not initialize database directory.

Estas mensagens esto relacionadas com a falta de permisso no diretrio de dados, o disco estar cheio, ou outro problema no sistema de arquivos. O usurio, sob o qual o servidor de banco de dados est processando, deve ter permisso para o local.

DescrioO comando CREATE DATABASE cria um banco de dados novo no PostgreSQL. O criador se torna o dono do novo banco de dados. Um local alternativo pode ser especicado com a nalidade de, por exemplo, armazenar o banco de dados em um disco diferente. O caminho deve ter sido preparado anteriormente com o comando initlocation. Se o nome do caminho no possuir uma barra (/) interpretado como sendo o nome de uma varivel de ambiente, a qual deve ser conhecida pelo servidor. Desta maneira, o administrador do banco de dados pode exercer controle sobre os locais onde os bancos de dados podem ser criados (Uma escolha usual , por exemplo, PGDATA2). Se o servidor for compilado com ALLOW_ABSOLUTE_DBPATHS (o que no feito por padro), nomes de caminhos absolutos, identicados por uma barra inicial (por exemplo, /usr/local/pgsql/data), tambm so permitidos. Por padro, o novo banco de dados ser criado clonando o banco de dados padro do sistema template1. Um gabarito diferente pode ser especicado escrevendo-se TEMPLATE = nome. Em particular, escrevendo-se TEMPLATE = template0, pode ser criado um banco de dados bsico contendo apenas os objetos padro predenidos pela verso do PostgreSQL sendo utilizada. Esta forma til quando se deseja evitar a cpia de qualquer objeto da instalao local que possa ter sido adicionado ao template1. O parmetro opcional de codicao permite a escolha de uma codicao para o banco de dados, se o servidor em uso foi compilado com suporte codicao multibyte. Quando este parmetro no especicado, o padro utilizar a mesma codicao do banco de dados usado como gabarito. Os parmetros opcionais podem ser escritos em qualquer ordem, e no apenas na ordem mostrada acima. Notas O comando CREATE DATABASE uma extenso da linguagem do PostgreSQL. Use o comando DROP DATABASE para excluir um banco de dados.

2

CREATE DATABASE O aplicativo createdb um script envoltrio criado em torno deste comando, fornecido por convenincia. Existem questes associadas segurana e integridade dos dados envolvidas na utilizao de locais alternativos para os bancos de dados especicados por caminhos absolutos. Por isso, pelo padro, somente uma varivel de ambiente conhecida pelo gerenciador de banco de dados pode ser especicada para um local alternativo. Consulte o Guia do Administrador do PostgreSQL para obter mais informaes. Embora seja possvel copiar outros bancos de dados alm do template1 especicando-se seu nome como gabarito, no se pretende (pelo menos ainda) que seja uma funcionalidade para COPY DATABASE de uso geral. recomendado que os bancos de dados utilizados como gabarito sejam tratados como se fossem apenas de leitura. Consulte o Guia do Administrador do PostgreSQL para obter mais informaes.

UtilizaoPara criar um banco de dados novo:silva=> create database lusiadas;

Para criar um banco de dados novo na rea alternativa ~/bd_privado:$ mkdir bd_privado $ initlocation ~/bd_privado The location will be initialized with username "silva". This user will own all the files and must also own the server process. Creating directory /home/silva/bd_privado Creating directory /home/silva/bd_privado/base initlocation is complete.

$ psql silva Welcome to psql, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit

silva=>

CREATE DATABASE outrolugar WITH LOCATION = /home/silva/bd_privado;

CREATE DATABASE

3

CREATE DATABASE

CompatibilidadeSQL92 No existe o comando CREATE DATABASE no SQL92. Os bancos de dados so equivalentes aos catlogos cuja criao denida pela implementao.

4

CREATE FUNCTIONNameCREATE FUNCTION dene uma nova funo

SynopsisCREATE [ OR REPLACE ] FUNCTION nome ( [ tipo_do_argumento [, ...] ] ) RETURNS tipo_retornado AS definio LANGUAGE nome_ling [ WITH ( atributo [, ...] ) ] CREATE [ OR REPLACE ] FUNCTION nome ( [ tipo_do_argumento [, ...] ] ) RETURNS tipo_retornado AS arq_objeto, simbolo_de_ligao LANGUAGE nome_ling [ WITH ( atributo [, ...] ) ]

DescrioO comando CREATE FUNCTION dene uma nova funo. O comando CREATE OR REPLACE FUNCTION tanto cria uma nova funo, quanto substitui uma funo existente.

Parmetrosnome O nome da funo a ser criada. No necessrio que o nome seja nico, porque as funes podem ser sobrecarregadas (overloaded), mas as funes que possuem o mesmo nome devem ter argumentos de tipos diferentes. tipo_do_argumento Os tipos de dado dos argumentos da funo, caso existam. Os tipos de dado da entrada podem ser o tipo base, complexo, opaque, ou o mesmo tipo de uma coluna existente. Opaque indica que a funo aceita argumentos de tipo no SQL, como o char *. O tipo de dado da coluna referenciado escrevendo-se nome_da_tabela.nome_da_coluna%TYPE; utilizando-se esta notao ajuda a funo se tornar independente das mudanas ocorridas na estrutura da tabela. tipo_retornado O tipo do dado retornado. O tipo de dado retornado pode ser especicado como um tipo base, complexo, setof, opaque, ou o mesmo tipo de uma coluna existente. O modicador setof indica que a funo retorna um conjunto de itens, em vez de um nico item. As funes em que se declara opaque para o valor do tipo retornado no retornam valor, e no podem ser chamadas diretamente; as funes dos gatilhos fazem uso desta funcionalidade.

1

CREATE FUNCTION definio Uma cadeia de caracteres contendo a denio da funo; o signicado depende da linguagem. Pode ser o nome de uma funo interna, o caminho para um arquivo objeto, uma consulta SQL, ou um texto escrito em uma linguagem procedural. arq_objeto, simbolo_de_ligao Esta forma da clusula AS usada para funes escritas na linguagem C, ligadas dinamicamente, quando o nome da funo no cdigo fonte da linguagem C no o mesmo nome da funo SQL. A cadeia de caracteres arq_objeto o nome do arquivo contendo o objeto carregado dinamicamente, e simbolo_de_ligao o smbolo de ligao do objeto, ou seja, o nome da funo no cdigo fonte escrito na linguagem C. nome_ling Pode ser SQL, C, internal, ou nome_ling_proc, onde nome_ling_proc o nome de uma linguagem procedural criada. Consulte o comando CREATE LANGUAGE para obter mais detalhes. Para manter a compatibilidade com as verses anteriores, o nome pode estar entre apstrofos (). atributo Uma informao opcional sobre a funo, utilizada para otimizao. Veja abaixo para obter mais detalhes.

O usurio que cria a funo torna-se o dono da funo. Os seguintes atributos podem estar presentes na clusula WITH: iscachable O atributo Iscachable indica que a funo sempre retorna o mesmo resultado quando recebe os mesmos valores para os argumentos (ou seja, no faz consultas ao banco de dados e tambm no utiliza informaes que no estejam diretamente presentes na sua lista de parmetros). O otimizador utiliza o atributo iscachable para saber se seguro pr-executar a chamada da funo. isstrict O atributo isstrict indica que a funo sempre retorna NULL quando qualquer um de seus argumentos for nulo. Se este atributo for especicado, a funo no ser executada quando houver argumento nulo, ou seja, um resultado nulo ser assumido automaticamente. Quando o atributo isstrict no for especicado, a funo ser chamada mesmo com argumentos de entrada nulos. Fica sendo responsabilidade do autor da funo vericar valores nulos, se for necessrio, e responder de forma apropriada.

NotasConsulte o captulo do Guia do Programador do PostgreSQL relativo extenso do PostgreSQL atravs de funes para obter mais informaes sobre como escrever funes externas.

2

CREATE FUNCTION A sintaxe completa do SQL permitida para os argumentos de entrada e o valor retornado. Entretanto, alguns detalhes da especicao do tipo (por exemplo, a preciso para tipos numeric) so responsabilidade da implementao da funo subjacente sendo silenciosamente aceitos pelo comando CREATE FUNCTION (ou seja, no so reconhecidos ou exigidos). O PostgreSQL permite a sobrecarga de funo, ou seja, o mesmo nome pode ser usado por vrias funes diferentes desde que possuam argumentos com tipos diferentes. Entretanto, esta funcionalidade deve ser usada com cuidado para as funes internas e para as funes escritas na linguagem C. Duas funes internal no podem possuir o mesmo nome no cdigo C sem causar erros durante a fase de ligao. Para contornar este problema deve-se dar nomes diferentes no cdigo C (por exemplo, usar os tipos dos argumentos como parte do nome no cdigo C), ento especicar este nome na clusula AS do comando CREATE FUNCTION. Se a clusula AS for omitida, ento CREATE FUNCTION assume que o nome da funo no cdigo C tem o mesmo nome do SQL. Analogamente, ao se sobrecarregar o nome das funes SQL atravs de vrias funes escritas na linguagem C, deve ser dado a cada instncia da funo na linguagem C um nome distinto e, ento, usar a forma alternativa da clusula AS na sintaxe do CREATE FUNCTION para selecionar a implementao apropriada na linguagem C de cada funo SQL sobrecarregada. Quando chamadas repetidas ao comando CREATE FUNCTION fazem referncia ao mesmo arquivo objeto, o arquivo s carregado uma vez. Para descarregar e carregar o arquivo (talvez durante a fase de desenvolvimento), use o comando LOAD. Use o comando DROP FUNCTION para remover as funes denidas pelo usurio. Para atualizar a denio de uma funo existente use o comando CREATE OR REPLACE FUNCTION. Observe que no possvel mudar o nome ou os tipos dos argumentos da funo desta forma (se for tentado, ser criada uma nova funo distinta). O comando CREATE OR REPLACE FUNCTION tambm no permite que se mude o tipo do valor retornado de uma funo existente. Para fazer isto, a funo deve ser removida e recriada. Se a funo for removida e recriada, a nova funo no mais a mesma entidade que era antes; caro invlidas as regras, vises, gatilhos, etc... existentes que faziam referncia antiga funo. Use o comando CREATE OR REPLACE FUNCTION para mudar a denio de uma funo, sem invalidar os objetos que fazem referncia funo.

ExemplosPara criar uma funo SQL simples:CREATE FUNCTION um() RETURNS integer AS SELECT 1 AS RESULTADO; LANGUAGE SQL; SELECT um() AS resposta;resposta ---------1

3

CREATE FUNCTION Este exemplo cria uma funo C chamando a rotina de uma biblioteca compartilhada criada pelo usurio chamada funcs.so (a extenso pode variar entre plataformas). O arquivo contendo a biblioteca compartilhada procurado no caminho de procura de biblioteca compartilhada do servidor. Esta rotina em particular calcula o dgito vericador e retorna TRUE se o dgito vericador dos parmetros da funo est correto. A inteno utilizar esta funo numa restrio de vericao (CHECK).CREATE FUNCTION ean_checkdigit(char, char) RETURNS boolean AS funcs LANGUAGE C; CREATE TABLE product ( id char(8) PRIMARY KEY, eanprefix char(8) CHECK (eanprefix ~ [0-9]{2}-[0-9]{5}) REFERENCES brandname(ean_prefix), eancode char(6) CHECK (eancode ~ [0-9]{6}), CONSTRAINT ean CHECK (ean_checkdigit(eanprefix, eancode)) );

No prximo exemplo criada uma funo que faz a converso de tipo, do tipo complexo denido pelo usurio e o tipo nativo point. A funo implementada por um objeto carregado dinamicamente que foi compilado a partir de um fonte C (est ilustrada a alternativa obsoleta de se especicar o caminho absoluto para o arquivo contendo o objeto compartilhado). Para o PostgreSQL encontrar a funo de converso de tipo automaticamente, a funo SQL deve ter o mesmo nome do tipo retornado, e por isso a sobrecarga inevitvel. O nome da funo sobrecarregado utilizando-se a segunda forma da clusula AS na denio SQL:CREATE FUNCTION point(complex) RETURNS point AS /home/bernie/pgsql/lib/complex.so, complex_to_point LANGUAGE C;

A declarao em C da funo poderia ser:Point * complex_to_point (Complex *z) { Point *p; p = (Point *) palloc(sizeof(Point)); p->x = z->x; p->y = z->y; return p; }

CompatibilidadeO comando CREATE FUNCTION denido no SQL99. A verso do PostgreSQL similar mas no totalmente compatvel. Os atributos no so portveis, nem as diferentes linguagens disponveis o so.

4

CREATE FUNCTION

Consulte tambmDROP FUNCTION , LOAD, Guia do Programador do PostgreSQL

5

CREATE GROUPNameCREATE GROUP dene um novo grupo de usurios

SynopsisCREATE GROUP nome [ [ WITH ] opo [ ... ] ] onde opo pode ser: SYSID gid | USER nome_usurio [, ...]

Entradas nome O nome do grupo. gid A clusula SYSID pode ser utilizada para escolher o identicador do novo grupo no PostgreSQL. Entretanto, no h necessidade de ser utilizada. Se esta clusula no for especicada, o valor do identicador mais alto atribudo a um grupo, acrescido de um, comeando por 1, ser utilizado por padro. nome_usurio A relao dos usurios a serem includos no grupo. Os usurios devem existir.

SadasCREATE GROUP

Mensagem retornada se o comando for executado com sucesso.

1

CREATE GROUP

DescrioO comando CREATE GROUP cria um novo grupo na instalao de banco de dados. Consulte o Guia do Administrador do PostgreSQL para obter mais informaes sobre a utilizao de grupos para autenticao. necessrio ser um superusurio do banco de dados para executar este comando. Use o ALTER GROUP para incluir ou excluir usurios no grupo, e o DROP GROUP para excluir um grupo.

UtilizaoCriar um grupo vazio:CREATE GROUP engenharia;

Criar um grupo com membros:CREATE GROUP vendas WITH USER jonas, marcela;

CompatibilidadeSQL92 No existe o comando CREATE GROUP no SQL92. O conceito de roles similar ao de grupos.

2

CREATE INDEXNameCREATE INDEX dene um ndice novo

SynopsisCREATE [ UNIQUE ] INDEX nome_do_ndice ON tabela [ USING mtodo_de_acesso ] ( coluna [ nome_do_operador ] [, ...] ) [ WHERE predicado ] CREATE [ UNIQUE ] INDEX nome_do_ndice ON tabela [ USING mtodo_de_acesso ] ( nome_da_funo( coluna [, ... ]) [ nome_do_operador [ WHERE predicado ]

Entradas UNIQUE Faz com que o sistema procure por valores duplicados na tabela quando o ndice criado, se existirem dados na tabela, e sempre que novos dados forem adicionados. A tentativa de inserir ou de atualizar dados, que produza um valor duplicado, gera um erro. nome_do_ndice O nome do ndice a ser criado. tabela O nome da tabela a ser indexada. mtodo_de_acesso O nome do mtodo de acesso a ser utilizado pelo o ndice. O mtodo de acesso padro o BTREE. O PostgreSQL implementa quatro mtodos de acesso para os ndices: BTREE uma implementao das B-trees de alta concorrncia de Lehman-Yao. RTREE implementa R-trees padro, utilizando o algoritmo de partio quadrtica de Guttman. HASH uma implementao das disperses lineares de Litwin. GIST Generalized Index Search Trees (rvores de Procura de ndice Generalizadas).

1

CREATE INDEX coluna O nome de uma coluna da tabela. nome_do_operador Uma classe de operador associada. Veja abaixo para obter mais detalhes. nome_da_funo Uma funo que retorna um valor que pode ser indexado. predicado Dene a expresso da restrio (constraint) para o ndice parcial.

SadasCREATE

Mensagem retornada se o ndice for criado com sucesso.ERROR: Cannot create index: nome_do_ndice already exists.

Este erro ocorre se for impossvel criar o ndice.

DescrioO comando CREATE INDEX constri o ndice nome_do_ndice na tabela especicada.Tip: Os ndices so utilizados, principalmente, para melhorar o desempenho do banco de dados, mas a utilizao no apropriada causa uma degradao do desempenho.

Na primeira sintaxe exibida acima, os campos chave para o ndice so especicados como nomes de coluna. Vrios campos podem ser especicados, se o mtodo de acesso do ndice suportar ndices com mltiplas colunas. Na segunda sintaxe exibida acima, o ndice denido sobre o resultado da funo denida pelo usurio nome_da_funo aplicada sobre uma ou mais colunas de uma nica tabela. Estes ndices funcionais podem ser utilizados para obter acesso rpido aos dados baseado em operadores que normalmente iriam requerer alguma transformao para aplic-los aos dados base. O PostgreSQL implementa os mtodos de acesso B-tree, R-tree, hash e GiST para os ndices. O mtodo de acesso B-tree uma implementao das B-trees de alta concorrncia de Lehman-Yao. O mtodo de acesso R-tree implementa R-trees padro utilizando o algoritmo de partio quadrtica de Guttman. O

2

CREATE INDEX mtodo de acesso hash uma implementao das disperses lineares de Litwin. Os algoritmos utilizados so mencionados apenas para informar que todos estes mtodos de acesso so inteiramente dinmicos, no necessitando de otimizao peridica (como no caso de, por exemplo, mtodos de acesso hash estticos). Quando a clusula WHERE est presente, um ndice parcial criado. Um ndice parcial um ndice que contm entradas apenas para uma parte da tabela, geralmente uma parte mais interessante do que o resto da tabela. Por exemplo, havendo uma tabela contendo tanto pedidos faturados quanto no faturados, onde os pedidos no faturados ocupam uma pequena frao da tabela, mas a parte mais consultada, o desempenho pode ser melhorado criando-se um ndice apenas para esta poro da tabela. Uma outra aplicao possvel a utilizao da clusula WHERE juntamente com UNIQUE para exigir a unicidade de um subconjunto dos dados da tabela. A expresso utilizada na clusula WHERE pode referenciar apenas as colunas da tabela subjacente (mas pode referenciar qualquer coluna, e no apenas as que esto sendo indexadas). Na forma atual, subconsultas e expresses de agregao no so permitidas na clusula WHERE. Todas as funes e operadores utilizados na denio de um ndice devem ser possveis de serem armazenados na memria intermediria (cachable), ou seja, seus resultados devem depender apenas de seus argumentos de entrada e nunca de uma inuncia externa (como o contedo de outra tabela ou a hora atual). Esta restrio garante que o comportamento do ndice bem denido. Para utilizar uma funo denida pelo usurio em um ndice deve ser utilizado o atributo Iscachable na clusula WITH. Use o DROP INDEX para excluir um ndice. Notas O otimizador de consultas do PostgreSQL vai considerar o uso de um ndice B-tree sempre que um atributo indexado estiver envolvido em uma comparao utilizando um dos seguintes operadores: , =, =, =, O otimizador de consultas do PostgreSQL vai considerar o uso de um ndice R-tree sempre que um atributo indexado estiver envolvido em uma comparao utilizando um dos seguintes operadores: ,& ,& , , @, ~=, && O otimizador de consultas do PostgreSQL vai considerar o uso de um ndice hash sempre que um atributo indexado estiver envolvido em uma comparao utilizando o operador =. Atualmente somente os mtodos de acesso B-tree e Gist suportam ndices com mais de uma coluna. Por padro, at 16 chaves podem ser especicadas (este limite pode ser alterado na gerao do PostgreSQL). Na implementao atual, somente o B-tree suporta ndices nicos. Uma classe de operador pode ser especicada para cada coluna de um ndice. A classe de operador identica os operadores a serem utilizados pelo ndice desta coluna. Por exemplo, um ndice B-tree sobre inteiros de quatro bytes vai utilizar a classe de operadores int4_ops; esta classe de operadores inclui funes de comparao para inteiros de quatro bytes. Na prtica, a classe de operadores padro para o tipo de dado do campo normalmente suciente. O ponto principal em haver classes de operadores que, para alguns tipos de dado, pode haver mais de uma ordenao que faa sentido. Por exemplo, pode se desejar ordenar o tipo de dado do nmero complexo tanto pelo valor absoluto, quanto pela parte real, o que pode ser feito denindo-se duas classes de operadores para o tipo de dado e, ento, selecionando-se a classe apropriada para a construo do ndice. Tambm existem algumas classes de operadores com nalidades especiais:

3

CREATE INDEX

As duas classes de operadores box_ops e bigbox_ops suportam ndices R-tree para o tipo de dado box. A diferena entre as duas que bigbox_ops ajusta as coordenadas da caixa para baixo, evitando excees de ponto utuante ao executar multiplicao, adio e subtrao de coordenadas com nmeros de ponto utuante muito grande (Nota: isto era verdade h algum tempo atrs, mas atualmente as duas classes de operadores utilizam ponto utuante e so efetivamente idnticas).

A seguinte consulta exibe todas as classes de operadores:SELECT am.amname AS metodo_de_acesso, opc.opcname AS nome_do_operador, opr.oprname AS op_comparao FROM pg_am am, pg_opclass opc, pg_amop amop, pg_operator opr WHERE opc.opcamid = am.oid AND amop.amopclaid = opc.oid AND amop.amopopr = opr.oid ORDER BY mtodo_de_acesso, nome_do_operador, op_comparao;

UtilizaoPara criar um ndice B-tree para a coluna titulo na tabela filmes:CREATE UNIQUE INDEX unq_titulo ON filmes (titulo);

CompatibilidadeSQL92 O comando CREATE INDEX uma extenso do PostgreSQL linguagem. No existe o comando CREATE INDEX no SQL92.

4

CREATE LANGUAGENameCREATE LANGUAGE dene uma nova linguagem procedural

SynopsisCREATE [ TRUSTED ] [ PROCEDURAL ] LANGUAGE nome_da_linguagem HANDLER tratador_de_chamadas

DescrioAtravs do comando CREATE LANGUAGE, um usurio do PostgreSQL pode registrar uma nova linguagem procedural em um banco de dados do PostgreSQL. Depois, podem ser denidos funes e procedimentos de gatilhos nesta nova linguagem. O usurio deve possuir o privilgio de superusurio do PostgreSQL para poder registrar uma nova linguagem. O comando CREATE LANGUAGE associa o nome da linguagem com o tratador de chamadas (call handler) que responsvel por executar as funes escritas nesta linguagem. Consulte o Guia do Programador para obter mais informaes sobre os tratadores de chamadas das linguagens. Observe que as linguagens procedurais so locais a cada bancos de dados. Para tornar uma linguagem disponvel a todos os bancos de dados por padro, esta linguagem deve ser instalada no banco de dados template1.

ParmetrosTRUSTED TRUSTED especica que o tratador de chamadas para a linguagem seguro, ou seja, no oferece a

um usurio sem privilgios qualquer funcionalidade para contornar as restries de acesso. Se esta palavra chave for omitida ao registrar a linguagem, somente usurios do PostgreSQL com privilgio de superusurio vo poder usar esta linguagem para criar novas funes.PROCEDURAL

Apenas informativo. nome_da_linguagem O nome da nova linguagem procedural. No existe distino entre letras minsculas e maisculas. Uma linguagem procedural no pode substituir uma das linguagens nativas do PostgreSQL. Por compatibilidade com as verses anteriores, o nome pode ser escrito entre apstrofos ().HANDLER tratador_de_chamadas

O tratador_de_chamadas o nome de uma funo, previamente registrada, que vai ser chamada para executar as funes escritas nesta linguagem procedural. O tratador de chamadas para a linguagem procedural deve ser escrito em uma linguagem compilada (como C), com a conveno

1

CREATE LANGUAGE de chamadas verso 1, registrada no PostgreSQL como uma funo que no recebe nenhum argumento e com retorno do tipo opaque, que um substituto para tipos no especicados ou no denidos.

DiagnsticosCREATE

Mensagem retornada se a linguagem for criada com sucesso.

ERROR:

PL handler function nome_da_funo() doesnt exist

Este erro ocorre quando a funo nome_da_funo() no for encontrada.

NotasNormalmente, este comando no deve ser executado diretamente pelos usurios. Para as linguagens procedurais fornecidas juntamente com a distribuio do PostgreSQL o aplicativo createlang deve ser utilizado, porque este aplicativo tambm instala o tratador de chamadas correto (O aplicativo createlang chama o CREATE LANGUAGE internamente). Use o comando CREATE FUNCTION para criar uma nova funo. Use o comando DROP LANGUAGE, ou melhor ainda, o aplicativo droplang, para excluir linguagens procedurais. O catlogo do sistema pg_language registra informaes sobre as linguagens procedurais atualmente instaladas.Table "pg_language" Attribute | Type | Modifier ---------------+---------+---------lanname | name | lanispl | boolean | lanpltrusted | boolean | lanplcallfoid | oid | lancompiler | text | lanname | lanispl | lanpltrusted | lanplcallfoid | lancompiler -------------+---------+--------------+---------------+-------------

2

CREATE LANGUAGEinternal C sql | f | f | f | f | f | f | | | 0 | n/a 0 | /bin/cc 0 | postgres

Atualmente, a denio de uma linguagem procedural no pode ser mudada aps ter sido criada.