Tamanho tabela e database POSTGRES

Para determinar o tamanho de uma tabela no banco de dados atual,  o seguinte comando. Substituir tablename pelo nome da tabela que se deseja verificar:

SELECT pg_size_pretty( pg_total_relation_size('tablename') );

Para determinar o tamanho de um banco de dados, o seguinte comando. Substituir dbname pelo nome do banco de dados que se deseja verificar:

SELECT pg_size_pretty( pg_database_size('dbname') );

Hibernate Annotations

Requer:
a) Hibernate Core 3.3 ou superior
b) JDK 5.0 ou superior

Configuração:
a) arquivos MINLIB3.3


import org.hibernate.*;
import org.hibernate.cfg.*;
import test.*;
import test.animals.Dog;

public class HibernateUtil {

private static final SessionFactory sessionFactory;

static {
try {

sessionFactory = new AnnotationConfiguration()
configure().buildSessionFactory();
} catch (Throwable ex) {
// Log exception!
throw new ExceptionInInitializerError(ex);
}
}

public static Session getSession()
throws HibernateException {
return sessionFactory.openSession();
}
}

SQL - Demorada

Consultas demoradas

SELECT pid, now(), datname, usename, now()-query_start as tempo_execucao, waiting, client_addr, query
FROM pg_stat_activity
WHERE query not like  '%%' and query_start < now() - interval '5 seconds'  order by 3 desc;

Cancelar uma consulta

 SELECT pg_cancel_backend(pid of the postgres process);

Replicação Postgresql utilizando SLONY (Instalação)

Necessitei trabalhar com replicação de banco de dados pensando em alta disponibilidade de acesso aos dados. O local onde o servidor de dados esta instalado não possui um no break. Tenho que fazer uma migração para outro local, porém como todos os sistemas acessam este banco de dados, não conseguia pensar em uma estratégia para esta migração.

A solução que implantamos faz o seguinte:

 

As minhas informações são gravadas no Banco 1. Todas as minhas aplicações estão apontando para o Banco 1. A estratégia adotada foi replicar o Banco 1 no site 1 para o Banco 2 no site 2. Tanto o banco como o ambiente do Site 2 são mais atualizados que do site 1.

Assim que as aplicações forem atualizados pode-se desligar o site 1 ou inverter a replicação.

Para replicação utilizei o Slony. A escolha do Slony aconteceu antes de entender todas as possibilidades de replicação que o Postgresql oferece.

Para instalação do Slony 2.2.4 em ambiente linux, segui a receita:

1) Baixar o Slony do site http://slony.info/downloads/, quando escrevi este post, a versão mais atualizada disponível era: slony1-2.2.4.tar.bz2.

2) Na pasta onde baixei o pacote, executei o comando:

            tar -vjxf slony1-2.2.4.tar.bz2

faz com que o pacote seja descompactado e cria uma pasta denominda: slony1-2.2.4

3) É necessário ter instalado um compilador C em sua máquina / servidor. No lugar se utilizar o configure.sh que vem na pasta slony1-2.2.4 utilize o configure.sh seguinte:

export PGMAIN=/DBA/postgresql/9.4.1

./configure \

    --prefix=$PGMAIN \

    --bindir=$PGMAIN/bin \

    --datadir=$PGMAIN/share \

    --libdir=$PGMAIN/lib \

    --with-pgconfigdir=$PGMAIN/bin \

    --with-pgbindir=$PGMAIN/bin \

    --with-pgincludedir=$PGMAIN/include \

    --with-pglibdir=$PGMAIN/lib \

    --with-pgsharedir=$PGMAIN/share

onde na variável PGMAIN deve ser inserida a pasta onde esta sua instalação do POSTGRESQL.

Execute do arquivo configure.sh.

Caso você receba a mensagem:

checking PostgreSQL for thread-safety... configure: error: PQisthreadsafe test failed - PostgreSQL needs to be compiled with --enable-thread-safety

Procure em sua máquina, a pasta onde esta instalado o arquivo: libpq-fe.h

find / -name libpq-fe.h

/usr/include/postgresql/libpq-fe.h

Coloque o resultado no arquivo configure.sh na variável with-pgincludedir

export PGMAIN=/DBA/postgresql/9.4.1

./configure \

    --prefix=$PGMAIN \

    --bindir=$PGMAIN/bin \

    --datadir=$PGMAIN/share \

    --libdir=$PGMAIN/lib \

    --with-pgconfigdir=$PGMAIN/bin \

    --with-pgbindir=$PGMAIN/bin \

    --with-pgincludedir=/usr/include/postgresql \

    --with-pglibdir=$PGMAIN/lib \

    --with-pgsharedir=$PGMAIN/share

Execute novamente o arquivo configure.sh

4) Em seguida execute os comandos:

make all

make install

5) Esta instalado o Slony em uma máquina.É necessário repetir procedimento para cada servidor de banco de dados.

6) Para entender como funciona o Slony utilizei o tutorial que encontrei no site:

http://slony.info/documentation/2.2/tutorial.html

Infelizmente tem alguns erros neste tutorial que podem ser resolvidos lendo os arquivos de LOG gerados.

GRANT POSTGRES

Problema: Configurar acesso a um usuário para fazer consultas em TABELAS no NOME_SCHEMA

Comando para garantir acesso ao NOME_SCHEMA

GRANT USAGE ON SCHEMA nome_schema TO usuario

Comando para dar autorização de CONSULTA ao NOME_SCHEMA

GRANT SELECT ON ALL TABLES IN SCHEMA nome_schema TO usuario;

XML formatado

Necessitei criar um arquivo formatado:

select  '<doc>''||E'\n'
                '<field name="cd_email">'|| cd_email ||'</field>'||E'\n'||
                '<field name="ds_remetente_email">'|| ds_remetente_email ||'</field>'||E'\n'||
                '<field name="ds_destinatario_email">'|| ds_destinatario_email ||'</field>'||E'\n'||
                '<field name="ds_copia">'|| ds_copia ||'</field>'||E'\n'||
                '<field name="ds_assunto">'|| ds_assunto ||'</field>'||E'\n'||
                '<field name="ds_mensagem_txt">'|| strip_tags(ds_mensagem_txt) ||'</field>'||E'\n'||
                '<field name="dt_abertura">'|| dt_abertura ||'</field>'||E'\n'||
                '<field name="dt_envio">'|| dt_envio ||'</field>'||E'\n'||
                '<field name="dt_recebimento">'|| dt_recebimento ||'</field>'||E'\n'||
        '<doc>'||E'\n'||E'\n'FROM crm.tb_email;

A solução acima gera um trabalho muito grande para escrever a consulta.

Com a expressão abaixo tem-se:

select  'xmlelement(name field,  xmlattributes(''' || coluna  || ''' as name), ' ||coluna|| '),' from
(select  a.attname as coluna
from pg_catalog.pg_attribute a inner join pg_stat_user_tables c on a.attrelid = c.relid
WHERE a.attnum > 0
and NOT a.attisdropped
and c.relname = 'tb_email') as foo

o seguinte resultado:

 xmlelement(name field,  xmlattributes('cd_email' as name), cd_email),
 xmlelement(name field,  xmlattributes('cd_caixa_email' as name), cd_caixa_email),
 xmlelement(name field,  xmlattributes('cd_usuario_cadastro' as name), cd_usuario_cadastro),
 xmlelement(name field,  xmlattributes('cd_usuario_abertura' as name), cd_usuario_abertura),
 xmlelement(name field,  xmlattributes('cd_usuario_responsavel' as name), cd_usuario_responsavel),
 xmlelement(name field,  xmlattributes('cd_status_documento' as name), cd_status_documento),
 xmlelement(name field,  xmlattributes('cd_status_fluxo' as name), cd_status_fluxo),
 xmlelement(name field,  xmlattributes('ds_remetente_email' as name), ds_remetente_email),
 xmlelement(name field,  xmlattributes('ds_destinatario_email' as name), ds_destinatario_email),
 xmlelement(name field,  xmlattributes('ds_copia' as name), ds_copia),
 xmlelement(name field,  xmlattributes('ds_assunto' as name), ds_assunto),
 xmlelement(name field,  xmlattributes('ds_mensagem' as name), ds_mensagem),
 xmlelement(name field,  xmlattributes('nu_protocolo_email' as name), nu_protocolo_email),
 xmlelement(name field,  xmlattributes('ds_mensagem_txt' as name), ds_mensagem_txt),
 xmlelement(name field,  xmlattributes('ds_mensagem_srch' as name), ds_mensagem_srch),
 xmlelement(name field,  xmlattributes('dt_abertura' as name), dt_abertura),
 xmlelement(name field,  xmlattributes('dt_envio' as name), dt_envio),
 xmlelement(name field,  xmlattributes('dt_recebimento' as name), dt_recebimento),
 xmlelement(name field,  xmlattributes('dt_auditoria' as name), dt_auditoria),

com o resultado pode-se criar uma nova consulta

select '< doc >',

 xmlelement(name field,  xmlattributes('cd_email' as name), cd_email),

 xmlelement(name field,  xmlattributes('cd_caixa_email' as name), cd_caixa_email),

 xmlelement(name field,  xmlattributes('cd_usuario_cadastro' as name), cd_usuario_cadastro),

 xmlelement(name field,  xmlattributes('cd_usuario_abertura' as name), cd_usuario_abertura),

 xmlelement(name field,  xmlattributes('cd_usuario_responsavel' as name), cd_usuario_responsavel),

 xmlelement(name field,  xmlattributes('cd_status_documento' as name), cd_status_documento),

 xmlelement(name field,  xmlattributes('cd_status_fluxo' as name), cd_status_fluxo),

 xmlelement(name field,  xmlattributes('ds_remetente_email' as name), ds_remetente_email),

 xmlelement(name field,  xmlattributes('ds_destinatario_email' as name), ds_destinatario_email),

 xmlelement(name field,  xmlattributes('ds_copia' as name), ds_copia),

 xmlelement(name field,  xmlattributes('ds_assunto' as name), ds_assunto),

 xmlelement(name field,  xmlattributes('ds_mensagem' as name), ds_mensagem),

 xmlelement(name field,  xmlattributes('nu_protocolo_email' as name), nu_protocolo_email),

 xmlelement(name field,  xmlattributes('ds_mensagem_txt' as name), ds_mensagem_txt),

 xmlelement(name field,  xmlattributes('ds_mensagem_srch' as name), ds_mensagem_srch),

 xmlelement(name field,  xmlattributes('dt_abertura' as name), dt_abertura),

 xmlelement(name field,  xmlattributes('dt_envio' as name), dt_envio),

 xmlelement(name field,  xmlattributes('dt_recebimento' as name), dt_recebimento),

 xmlelement(name field,  xmlattributes('dt_auditoria' as name), dt_auditoria),

'< / doc >'

from crm.tb_email

Alterar proprietário de todas as tabelas

Alterar o proprietário de tabelas do postgresql

select 'alter table ' ||relname || ' owner to novo_usuario;'
from pg_stat_user_tables
order by 1

Alterar os locais do INDEX postgresql

select 'ALTER INDEX '||schemaname||'.'||indexname||' SET TABLESPACE fastspace ;'
from pg_catalog.pg_indexes
where schemaname <> 'pg_catalog'
order by tablename;

Instalação POSTGRES/POSTGIS em FEDORA/RED HAT

Para verificar a versão do REDHAT

 cat /etc/redhat-release


Ajustando o YUM para acessar os repositórios através do PROXY


Para permitir todas operações do yum usando um servidor proxy, especifique os detalhes do servidor proxy em /etc/yum.conf. A configuração proxy deve especificar o servidor proxy como uma URL completa, incluindo o número da porta TCP. Se o seu servidor proxy necessita de usuário e senha, especifique adicionando as configurações proxy_username e proxy_password.

# The proxy server - proxy server:port number 
proxy=http://mycache.mydomain.com:3128 
# The account details for yum connections 
proxy_username=yum-user 
proxy_password=qwerty

Fonte:http://docs.fedoraproject.org/pt-BR/Fedora_Core/5/html/Software_Management_Guide/sn-yum-proxy-server.html 

Configurando o repositório YUM
Localize em sua distribuição Linux o arquivo com a extensão .repo, localizado em:

• Fedora: /etc/yum.repos.d/fedora.repo and /etc/yum.repos.d/fedora-updates.repo, seção [fedora] 
• CentOS: /etc/yum.repos.d/CentOS-Base.repo, seção [base] and [updates] sections
• Red Hat: /etc/yum/pluginconf.d/rhnplugin.conf seção[main] 

Inserir a linha:

exclude=postgresql*

Para listar os pacotes (packages) disponíveis

yum list postgres*

yum remove libaep.so

sudo yum install http://mirror.centos.org/centos/6/os/x86_64/Packages/openssl-1.0.1e-
15.el6.x86_64.rpm

Para instalar o servidor POSTGRESQL

yum install postgresql93-server.x86_64

ou

yum install postgresql93-server 

Para criar e inicializar um database

service postgresql-9.3 initdb

chkconfig postgresql-9.3 on

Para ajustes nas permissões de acesso

vim /var/lib/pgsql/9.3/data/pg_hba.conf

vim /var/lib/pgsql/9.3/data/postgres.conf

Fonte: https://wiki.postgresql.org/wiki/YUM_Installation

Para instalar os compiladores necessários

yum install gcc gcc-c++

Para instalar o LIBXML2

tar vxzf libxml2-2.9.0.tar.gz
cd libxml2-2.9.0
./configure --prefix=/usr --disable-static --with-history && make
make check
make install

Para instalar o GEOS

tar -xvjf geos-3.4.2.tar.bz2
cd geos-3.4.2
./configure
make
make check
make install

Para instalar o PROJ.4

tar -xzvf proj-4.8.0.tar.gz
cd proj-4.8.0
./configure
make
make check
make install

Para instalar o GDAL (a última versão não funcionou - gdal-1.10.1)  

tar -xvzf gdal-1.10.0a.tar.gz
cd gdal-1.10.0
./configure
make

make install

Na pasta postgis-2.1.2

export PATH=$PATH:/usr/pgsql-9.3/bin/

./configure --with-geosconfig=/home/local/MDS/caio.nakashima/postgres/geos-3.4.2/tools/geos-config --with-gdalconfig=/usr/local/bin/gdal-config

make

make install

Testando o Postgis

com usuário postgres, criar um database

createdb mapas

createlang plpgsql mapas

procurar pelo arquivo postgis.sql

find / -name postgis.sql

/usr/pgsql-9.3/share/contrib/postgis-2.1/postgis.sql

Para carregar as definições dos objetos e funções do POSTGIS no banco de dados, executa-se o comando abaixo

 psql -d mapas -f postgis.sql

Para completar ajustar os identificadores do sistema de coordenadas EPSG. Carrega-se o arquivo spatial_ref_sys.sql para popular a tabela spatial_ref_sys.

psql -d mapas -f spatial_ref_sys.sql

Para restaurar cópias antigas de versões anteriores

psql -d mapas -f legacy.sql

Para carga de shape files

/usr/pgsql-9.3/bin/shp2pgsql -c -W LATIN1 53UFE250GC_SIR.shp mapas.shp_uf | psql -d mapas

/usr/pgsql-9.3/bin/shp2pgsql -a -W LATIN1 53MUE250GC_SIR.  mapas.shp_mu | psql -d mapas

Verificar se um ponto esta contido em um poligono

Esta cada vez mais recorrente a pergunta:

"Esta coordenada (latitude, longitude) de um equipamento esta dentro do município?"

A consulta baixo permite responder esta pergunta.

select ST_Contains(the_geom, ST_Point(longitude::float,latitude::float) ), uf, nome
from mapas.shp_munic
order by uf, nome

Com a base do IBGE de municípios, pode-se pegar as informações de (latitude, longitude) e executar a pesquisa.

Copia de tabelas em servidores diferentes com PLPGSQL

Um desafio que apareceu foi fazer cópia de 54 tabelas de um servidor Postgres de um servidor para outro. Estas tabelas são formadas por 2 grupos de 27 tabelas referentes à 27 unidades da federação. O primeiro caminho que segui para solução do problema, foi fazer uma cópia de segurança, vulgo BACKUP, e restaurar em outro servidor. O problema apareceu quando percebi que o arquivo resultante da cópia era muito grande e o processo de restauração levaria mais tempo que a cópia. Assim passei para outra solução, criar um script para fazer a cópia da tabela. Para isso utilizei o dblink e a linguagem plpgsql.

O código para criar a função foi:


create or replace function copia_pessoa(esquema varchar, uf varchar) returns text as $$
declare
     tabela1 varchar;

begin
        tabela1 := 'tab_cad_'|| esquema || '.tb_pessoa_uf_' || uf;
RAISE NOTICE 'Tabela destino %' ,tabela1;
execute 'create table '|| tabela1 ||' as
select * from dblink 
(''port=porta dbname=database host=iphost user=user password=senha'',
''select * from  ' || tabela1 || ' '') as t1
(
  cd_ibge character varying(13),
  cod_familiar_fam character varying(11),
  num_membro_fmla character varying(11),
  marc_pbf numeric(8,0),
  marc_bsp numeric(8,0)
)' ;

execute ' CREATE INDEX idx_cd_ibge_uf'||uf||'_pess_'||esquema||'   ON ' || tabela1 || '  USING btree   (cd_ibge)';

execute ' CREATE INDEX idx_cod_familiar_fam_uf' ||uf|| '_pess_' ||esquema||' ON '|| tabela1 ||' USING btree   (cod_familiar_fam)';



return 'Sucesso ' || tabela1;
end;

$$ language plpgsql;


Destaco que neste código pude fazer com que o nome da tabela é uma variável conforme o parâmetro passado.

tabela1 := 'tab_cad_'|| esquema || '.tb_pessoa_uf_' || uf;


O nome da tabela é composto pelo nome do esquema e código do ibge da UF.

O comando EXECUTE permite que o comando SQL seja executado com parâmetros, neste caso o nome da tabela.

execute 'create table '|| tabela1 ||' as
select * from dblink ...';

Para utilizar a função pode-se utilizar o comando abaixo, onde o primeiro parâmetro é o nome do esquema e o segundo parâmetro é o código ibge da UF.

select copia_pessoa('nome_do_esquema','12')

Uma outra forma de utilização foi:

select copia_pessoa('18052013',ibge::varchar) from estados

onde na tabela estados se obtém os códigos do IBGE dos estado no campo ibge.

create table estados (ibge integer, uf char(2));

 insert into estados (ibge, uf) values (11,'RO');
 insert into estados (ibge, uf) values (12,'AC');
 insert into estados (ibge, uf) values (13,'AM');
 insert into estados (ibge, uf) values (14,'RR');
 insert into estados (ibge, uf) values (16,'AP');
 insert into estados (ibge, uf) values (17,'TO');
 insert into estados (ibge, uf) values (22,'PI');
 insert into estados (ibge, uf) values (23,'CE');
 insert into estados (ibge, uf) values (24,'RN');
 insert into estados (ibge, uf) values (25,'PB');
 insert into estados (ibge, uf) values (27,'AL');
 insert into estados (ibge, uf) values (28,'SE');
 insert into estados (ibge, uf) values (29,'BA');
 insert into estados (ibge, uf) values (31,'MG');
 insert into estados (ibge, uf) values (41,'PR');
 insert into estados (ibge, uf) values (43,'RS');
 insert into estados (ibge, uf) values (50,'MS');
 insert into estados (ibge, uf) values (51,'MT');
 insert into estados (ibge, uf) values (52,'GO');
 insert into estados (ibge, uf) values (53,'DF');
 insert into estados (ibge, uf) values (15,'PA');
 insert into estados (ibge, uf) values (21,'MA');
 insert into estados (ibge, uf) values (33,'RJ');
 insert into estados (ibge, uf) values (35,'SP');
 insert into estados (ibge, uf) values (42,'SC');
 insert into estados (ibge, uf) values (26,'PE');

 insert into estados (ibge, uf) values (32,'ES');

UPDATE com um comando SQL

Precisei atualizar a tabela pradin.s00031label os campos UF, MUNICIPIO, LAT, LON a partir da tabela mapas.shp_munic. Estava com preguiça em escrever 4 consultas SQL uma para cada campo. Com a expressão abaixo consegui fazer um comando SQL para atualizar 4 campos em um SGBD Postgresql 9.2.

update pradin.s00031label
set
  uf = b.uf,
  municipio = b.nome ,
  lat = b.latitude,
  lon = b.longitude
from
    mapas.shp_munic b
where  
    s00031label.ibge=b.ibge

comando UPDATE e DBLINK

O problema que tive é atualizar o campo "ibge7" da tabela "dados" com informações contidas em um outro banco de dados.

Utilizei o comando UPDATE e DBLINK

update sisfam.dados as l set ibge7 = (
select geocodigo from
(
select geocodigo, nome, ibge, sigla
from dblink
(
'dbname=database
hostaddr=enderecoip
user=usuario
password=senha
port=porta',

'
select m.geocodigo, m.nome, m.ibge, u.sigla
from mapas.shp_munic m , mapas.shp_uf u
where trunc(m.ibge/10000)=u.ibge'
)
as t1(geocodigo bigint, nome varchar, ibge bigint, sigla char(2))
)
 as f
 where  lower(trim(f.sigla))=lower(trim(l.uf))
and lower(trim(f.nome)) = lower(trim(l.municipio))
)

Consulta Recursiva

Tenho uma tabelas órgãos que necessito apresentar uma hierarquia.

select * from orgaos

codigo	codigo_pai	sigla
3	2	SAGI
1	null	PR
4	3	DGI
5	3	DFD
6	3	DM
7	3	DA
2	1	MDS
12	1	MTE
13	1	MRE
11	3	Gab/SAGI

Para representar este organograma

A consulta abaixo:


with recursive org (sigla, codigo, codigo_pai, path,deep,nome) as (
 select sigla, codigo, codigo_pai, array[codigo],1, nome
   from orgaos
   where codigo_pai is null
 union all
   select o1.sigla, o1.codigo, o1.codigo_pai,  path || o1.codigo ,o2.deep+1, o1.nome
   from orgaos o1, org o2
   where o2.codigo = o1.codigo_pai
 
)

 select * from org

me gerou

orgao	orgao_pai	sigla	path	deep
1		PR	{1}	1
2	1	MDS	{1,2}	2
3	2	SAGI	{1,2,3}	3
4	3	DGI	{1,2,3,4}	4
5	3	DFD	{1,2,3,5}	4
6	3	DM	{1,2,3,6}	4
7	3	DA	{1,2,3,7}	4
11	3	Gab/SAGI	{1,2,3,11}	4
12	1	MTE	{1,12}	2
13	1	MRE	{1,13}	2

Consulta XML

A função xmlforest() do postgresql emite uma saida em XML

select xmlforest(ibge,sigla,nome) from estados where sigla='PR'

"41PRParaná"

Algorítimo de DICE

"Dice's coefficient, named after Lee Raymond Dice and also known as the Dice coefficient, is a similarity measure over sets."


Código para PSQL (POSTGRESQL)


CREATE OR REPLACE FUNCTION cgu_planilha.dice(texto1 character varying, texto2 character varying)
  RETURNS double precision AS
$BODY$
DECLARE
        qtde1 integer;
        qtde2 integer;
        tam_texto1 integer;
        tam_texto2 integer;
        tam_texto integer;
        nt float;
        nx float;
        ny float;
        ch1 varchar;
        ch2 varchar;
t1 varchar;
t2 varchar;
BEGIN
    qtde1 := 0;
    t1 := lower(texto1);
    t2 := lower(texto2);
    t1 := translate(t1, 'áéíóúàèìòùãõâêîôôäëïöüçÁÉÍÓÚÀÈÌÒÙÃÕÂÊÎÔÛÄËÏÖÜÇ', 'aeiouaeiouaoaeiooaeioucAEIOUAEIOUAOAEIOOAEIOUC');
    t2 := translate(t2, 'áéíóúàèìòùãõâêîôôäëïöüçÁÉÍÓÚÀÈÌÒÙÃÕÂÊÎÔÛÄËÏÖÜÇ', 'aeiouaeiouaoaeiooaeioucAEIOUAEIOUAOAEIOOAEIOUC');

    tam_texto1 := length(t1);
    tam_texto2 := length(t2);
    nx = tam_texto1-1;
    ny = tam_texto2-1;
 
    if (tam_texto2 > tam_texto1)  then
       tam_texto := tam_texto1;
    else
       tam_texto := tam_texto2;
    end if;
    nt := 0;
    LOOP
           ch1 = substr(t1, qtde1, 2);
           ch2 = substr(t2, qtde1, 2);
           if (ch1 = ch2) then
              nt := nt +1;
           end if;
           qtde1 := qtde1 + 1;
           IF qtde1 > tam_texto THEN
              EXIT;  -- exit loop
           END IF;
        END LOOP;
    RETURN (2*nt)/(nx+ny);
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION cgu_planilha.dice(character varying, character varying) OWNER TO postgres;

Aplicação do algoritmo para uma frase

CREATE OR REPLACE FUNCTION dice_frase2(texto1 character varying, texto2 character varying)
  RETURNS boolean AS
$BODY$
DECLARE
        alvo_pesquisa varchar;
t1 varchar;
t2 varchar;
tamanho2 integer;
tamanho1 integer;
i integer;
j integer;
texto_original varchar;
BEGIN
   -- determinando o tamanho da segunda palavra
   tamanho2:=length(texto2);
   tamanho1:=length(texto1);
   -- pesquisando na primeira frase
   i := 1;
   texto_original := texto1;
   loop
        alvo_pesquisa := substr(texto_original, i, tamanho2);
        insert into lixo values(alvo_pesquisa, texto2, dice(alvo_pesquisa, texto2));
        if (dice(alvo_pesquisa, texto2)>0.75) then
             return true;
         end if;
i := i+1;
if (tamanho1 <= i) then
  exit;
end if;
   end loop;
   return false;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

Aplicação do algoritmo para um conjunto de palavras em qualquer ordem

CREATE OR REPLACE FUNCTION dice_frase3(texto1 character varying, texto2 character varying)
  RETURNS boolean AS
$BODY$
DECLARE
        ch2 varchar;
        t1 varchar;
t2 varchar;
i integer;
j integer;
k integer;
BEGIN
   j := 1;
   k := 1;
   loop
t2 = split_part(texto2, ' ',j);
if  (length(t2)=0) then
   exit;
end if;
i:=1;
loop
t1 = split_part(texto1, ' ',i);
if  (length(t1)=0) then
   exit;
end if;
insert into lixo values (t1, t2, i ||' '|| j || ' '|| k);
if (dice(t1, t2)>0.75) then
   k := k+1;
   exit;
end if;
i := i+1;
end loop;
j := j+1;
   end loop;
   if (j = k) then
        return true;
   else    
return false;
   end if;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION dice_frase(character varying, character varying)
  OWNER TO postgres;

Campos POSTGRES

Consulta para apresentar os campos de uma tabela POSTGRES

select c.relname, a.attname as "Column", pg_catalog.format_type(a.atttypid, a.atttypmod) as "Datatype"
from pg_catalog.pg_attribute a inner join pg_stat_user_tables c
on a.attrelid = c.relid
WHERE a.attnum > 0
and NOT a.attisdropped
and c.relname = 'nome tabela'


Para mudar o tipo de letra de campos da tabela. Neste exemplo estou transformando para caixa baixa todos os campos de 'nome tabela'

update pg_catalog.pg_attribute a  set attname=lower(attname)
where a.attrelid in
(select relid from pg_stat_user_tables c
WHERE  c.relname = 'nome tabela' )
and a.attnum > 0
and NOT a.attisdropped

Comandos SQL utilizados

-- Número para ser utilizado na tabela com próximo identificador.
select max(id_mapa)+1 from mapas.map_description

-- Inserir a descricao
insert into mapas.map_description (
id_mapa ,
titulo ,
tipo_conexao ,
conexao_usuario ,
conexao_senha ,
conexao_dbname,
conexao_host ,
conexao_port ,
sql ,
publicado,
fonte ,
fundored ,
fundogreen,
fundoblue)
(
select ###,
titulo ,
tipo_conexao ,
conexao_usuario ,
conexao_senha ,
conexao_dbname,
conexao_host ,
conexao_port ,
sql ,
publicado,
fonte ,
fundored ,
fundogreen,
fundoblue
from mapas.map_description
where id_mapa =##)

-- inserir detalhe
insert into mapas.map_detail (
id_mapa,
ordem ,
nome_camada,
tipo_conexao ,
conexao_usuario,
conexao_senha ,
conexao_dbname,
conexao_host,
conexao_port,
sql,
tipo_mapa,
projecao ,
color_red ,
color_green,
color_blue ,
outline_red ,
outline_green,
outline_blue ,
symbol ,
symbolsize,
labelitem,
lablefont,
labelsize,
checked,
show,
sql_qtde,
qtde_unidade)
(select
###,
ordem ,
nome_camada,
tipo_conexao ,
conexao_usuario,
conexao_senha ,
conexao_dbname,
conexao_host,
conexao_port,
sql,
tipo_mapa,
projecao ,
color_red ,
color_green,
color_blue ,
outline_red ,
outline_green,
outline_blue ,
symbol ,
symbolsize,
labelitem,
lablefont,
labelsize,
checked,
show,
sql_qtde,
qtde_unidade
from mapas.map_detail
where id_mapa=##)

DBlink

Link com exemplos da utilização do DBlink

http://pt.wikibooks.org/wiki/PostgreSQL_Prático/Replicação

Verificar se o conteúdo de um campo postgresql é numérico

O meu problema é verificar se o conteúdo de um campo texto (resposta) é numérico.

select pergunta, resposta
from dados.itemquest
where (resposta ~ '^[0-9.-]+$');

A expressão:

where (resposta ~ '^[0-9.-]+$');

retorna true para conteúdo numérico.