Criação de Índices com Procedures no PostgreSQL...

Vamos ver se consigo ajudar. Esta é uma dúvida do Juliano Fischer:

"Ola, estou com um problema em uma implementação e creio que pode me ajudar.
Preciso criar um subprograma armazenado que crie um indice,se já existir, informar o usuario, cado contratio, crie um indice.

criaIndice(campo,tabela);"

Não é tão difícil! A solução está abaixo, mas cuidado pois o meu código pode e deve ser melhorado. Nomes de parâmetros, métodos e variáveis e bons comentários podem ser adicionados.

Além disso, o exemplo só verifica um campo e exige que se tenha cuidado na nomenclatura dos campos, pois utiliza a cláusula LIKE.

1 - Recuperar informações dos índices.

A dica é utilizar a pg_indexes, visão de sistema mais amigável para saber se o índice existe:

select * from pg_indexes;

2 - Criar uma função parametrizada que indica se os índices existem. Observem que utilizei a linguagem SQL, e não Pl/ PgSql. Poderia utilizar qualquer outra liguagem, mas a lógica seria similar.

-- Verifica se existe o índice. Retorna ZERO se o índice não for encontrado
CREATE OR REPLACE FUNCTION retindex(in tabela varchar, in campo varchar) RETURNS bigint AS $$
select count(*) from pg_indexes where tablename = $1 and indexdef like '%' || $2 || '%'
$$
LANGUAGE SQL;


3 - Criar função parametrizada que cria o índice caso o mesmo não exista, e que utilize a função anterior. Linguagem Pl/ PgSQL.

-- Verifica e cria novo índice se for o caso
CREATE OR REPLACE FUNCTION criaindex (tabela varchar, campo varchar) RETURNS VARCHAR AS $$
DECLARE
func_cmd VARCHAR;
BEGIN
if retindex($1,$2) > 0 then
RETURN 'OK';
else
func_cmd := 'CREATE INDEX ' || $1 || '_IDX ON ' || $1 || ' (' || $2 || ')';
EXECUTE func_cmd;
RETURN func_cmd;
end if;
END;
$$ LANGUAGE plpgsql;

4 - Testando tudo

select retindex ('teste','c1');
select criaindex ('teste', 'c1');

Companheiros, quem pode dar um código que melhore ainda mais esta criação de índices com função armazenada? Juliano, resolvi seu problema?

A Função DATE_PART

Esta utilíssima função retorna parte de uma data, hora ou timestamp para manipulação. É bastante prática e utilizada nas aplicações PostgreSQL. Equivale à função EXTRACT.

Aceita como parâmetros um texto com a parte da data a ser extraída e outro parâmetro com a data/ hora/ timestamp da qual será extraída parte.

Sintaxe:
1- date_part(text, timestamp) double precision
2 - date_part(text, interval) double precision

Exemplos:

1 - Date_part com current_date. Retornando dia da semana, dia, mês e ano.

SELECT date_part('dow', current_date); --DAY OF WEEK
SELECT date_part('day', current_date);
SELECT date_part('month', current_date);
SELECT date_part('year', current_date);

2 - Data_part com current_date retornando dia da semana, dia, mês e ano na mesma linha.

SELECT date_part('day', current_date) as dia, date_part('month', current_date) as mês, date_part('year', current_date) as ano;

3 - Data_part com current_time.

SELECT date_part('hour', current_time);
SELECT date_part('minute', current_time);
SELECT date_part('second', current_time);
SELECT date_part('milliseconds', current_time);

4 - Data_part com current_time retornando hora, minuto, segundo e milésimos de segundo em uma só linha.

SELECT date_part('hour', current_time), date_part('minute', current_time), date_part('second', current_time), date_part('milliseconds', current_time);

5 - Date_part com current_timestamp.

SELECT date_part('dow', current_timestamp);
SELECT date_part('day', current_timestamp);
SELECT date_part('month', current_timestamp);
SELECT date_part('milliseconds', current_timestamp);

6 - Date_part com current_timestamp, retornando dia da semana, dia, mês e os milisegundos da hora corrente.

SELECT date_part('dow', current_timestamp), date_part('day', current_timestamp), date_part('month', current_timestamp), date_part('milliseconds', current_timestamp);

7 - Date_part com conversão da string para campos data/ hora/ timestamp.

SELECT date_part('day', date '12-12-2007');
SELECT date_part('month', date '12-12-2007');
SELECT date_part('hour', time '02:12:45');
SELECT date_part('minute', time '02:12:45');
SELECT date_part('year', timestamp '12-12-2007 02:12:45');
SELECT date_part('hour', timestamp '12-12-2007 02:12:45');

8 - Sintaxes utilizando interval.

SELECT date_part('year', interval '4 years 3 months');
SELECT date_part('month', interval '4 years 3 months');
SELECT date_part('hour', interval '12 hour 10 minutes 12 seconds');
SELECT date_part('minute', interval '12 hour 10 minutes 15 seconds');
SELECT date_part('second', interval '12 hour 10 minutes 15 seconds');

Ótimo Livro Brasileiro de PostgreSQL!!!

O livro "Dominando o PostgreSQL", de Jorge Luiz Gonzaga, cobre as principais funções do banco, desde a instalação até a construção de funções e triggers. É uma ótima opção para quem utiliza versões 8.X. Não costumo comentar livros, mas ele tem se revelado uma ótima fonte.

Ficha técnica:
- Dominando o PostgreSQL
Autor: Gonzaga, Jorge Luiz
Editora: Ciência Moderna
ISBN: 9788573935592
Idioma: Português
Páginas: 244
Publicação: 2007
Edição: 1
Encadernação: Brochura
Peso: 512.00 gramas (!)

Pontos fortes:
- Grande Abrangência. São 36 capítulos falando de quase todos os aspectos desta tecnologia, incluindo instalação Linux e Windows.
- Suporte à linguagem SQL bastante detalhado, com muitos exemplos de sintaxes alternativas que aumentama produtividade.
- Cobre as versões 8.x, o que a maioria dos livros nacionais ainda não faz.

Pontos fracos:
- Ausência de índice remissivo (aquele que fica no fim da página)
- Certos temas mereciam maior detalhamento, como o WAL - Write-Ahead Log.
- O livro como um todo poderia ter textos mais detalhados. Os capítulos são muito curtos e os textos diretos demais, resultando em poucas páginas por capítulo.

Conclusão: é um ótimo livro. Não conheço melhor livro em português. E você?

PostgreSQL 8.3 Beta Liberado para Testes

Não recomendada para uso comercial, estas versão aguarda que você faça seu download e testes. Ah, você já pode baixar na página do programa de beta testes do PostgreSQL: Seja um Beta Tester.

Novas funcionalidades:
- Melhorias de desempenho como alterações para o Distributed Checkpoint, Commit Assíncrono entre outros recursos
- TSearch2 para busca em texto passa a ser integrada ao código e sintaxe mais amigável. (Possível tema para próximos posts)
- SQL:XML syntax. (Precisa de comentário? Tenho de testar isso logo!!!)
- Logging para arquivos CSV carregáveis no banco.
- Rebuilding automático de planos de execução de consultas em cache. (ótima idéia de melhoria de desempenho)
- ENUMs, UUIDs and arrays of tipos complexos.
- Suporte de autenticação para GSSAPI e SSPI. (o que diabos é isso?)

Podemos colaborar bastante baixand, testando o beta e encaminhando as alterações.

Documentação completa das novidades nas Release Notes.

A Função TIMEOFDAY

A função timeofday() retorna um texto com a data e hora. Não é muito utilizada.

Exemplos:

1 - Sintaxe básica.

select timeofday();
Retorno: "Thu Oct 04 12:33:55.953000 2007 BRT"

Função NOW

Função equivalente a CURRENT_TIMESTAMP. Pode ser utilizada em consultas, alterações, inserções e exclusões de registros.

Exemplos:

1 - Sintaxe básica.

select now();
Retorno: "2007-10-04 11:24:15.718-03"

2 - Consulta com outra função.

select now(), current_timestamp;
Retorno: "2007-10-04 11:25:11.625-03";"2007-10-04 11:25:11.625-03"

3 - Uso de NOW() em inserção.

insert into testedata values (current_date, current_time, now());

4 - Uso de NOW() em consulta.

select * from testedata where ctimestamp = now();

CURRENT_DATE, CURRENT_TIME e CURRENT_TIMESTAMP

São funções que retornam respectivamente a data atual, a hora corrente e o timestamp (data + hora) registrado no relógio do computador. Podem ser utilizadas na inserção, atualização, consulta e exclusão de dados.

Exemplos:

1 - Retornando a data atual.

SELECT current_date;

2 - Retornando a hora atual.

SELECT current_time;

3 - Retornando o timestamp corrente.

SELECT current_timestamp;

4 - Consulta retornando current_date, current_time e current_timestamp.

SELECT current_date, current_time, current_timestamp;

5 - Utilização das funções para inserção, alteração e exclusão de dados.

CREATE TABLE testedata(
cdata DATE,
ctime TIME,
ctimestamp timestamp);

insert into testedata values (current_date, current_time, current_timestamp);

insert into testedata values ('2007-10-21', '11:04:50.123', '2007-10-21 11:04:50.123');

update testedata set cdata = current_date;

delete from testedata where cdata <> current_date;

6 - Utilização das funções para consultas aos dados.

select * from testedata where ctime <> current_time;

Comandos de Manipulação de DATA/ HORA

O PostgreSQL manipula campos tipo data e hora com muita desenvoltura. Apresenta ainda o tipo de dados timestamp que engloba data e hora no mesmo campo. As principais funções de manipulação de datas são:

- current_date
- current_time
- current_timestamp
- now()
- timeofday()
- date_part
- date_trunc
- extract

Estas funções podem ser utilizadas na inserção, atualizações e exclusão dos dados, como será exemplificado nos próximos posts.