Insert com a recuperação do ID

Buenas pessoal, já faz muito tempo que não posto nada, não por não ter o que postar, mas sim, por estar fazendo a graduação na área e o tempo faltando aos montes.

Hoje resolvi postar algo simples, mas que dá muito trabalho aos programadores em iniciantes quando sem fala em "inserts" em tabelas relacionas.

Um exemplo do que estou falando, é a simples ação de incluir o cabeçalho de um pedido e obter o ID do mesmo para incluir seus itens. Há programadores que utilizam uma tabela para estas duas ações, mas no meu caso, sempre uso duas, das quais chamo de mestre x detalhe.

Para dar um exemplo, é preciso criar um tabela simples. Segue o comando:

Create table "Pedido"(ID_Pedido serial primary key, Total Numeric);

Quando todo o processo é feito via banco de dados, ou seja, através de funções, podemos utilizar pelo menos duas opções para retornar este ID:


1º Através de comando "nextval"

create or replace function inserir_pedido1() returns integer as
$$
declare
  --Cria-se uma variável para receber o valor "nextval"
  xID_Pedido Integer default 0;
begin
    --Recuperando o ID via comando "nextval"

    xID_Pedido = nextval('"Pedido_id_pedido_seq"');


    --Insert utilizando a variável para gravar o ID na tabela mestre e retornar a   
    --função para depois utilizar na inclusão dos itens.

    Insert Into "Pedido" (ID_Pedido, Total) Values( XID_Pedido, 1.5);

    --Retornando o valor do ID
    Return XID_Pedido;
end;
$$
language plpgsql;


--Chamada da função que inclui o mestre do pedido
Select inserir_pedido1();


2º Através do comando "returning"

create or replace function inserir_pedido2() returns integer as
$$
declare
  --Cria-se uma variável para receber o valor "into"
  XID Integer;
begin
 --Recuperando o ID via comando "returning"

 Insert Into "Pedido" (Total) Values(1.5) returning ID_Pedido into XID;

 return XID;

end;
$$
language plpgsql;

--Chamada da função que inclui o mestre do pedido
Select inserir_pedido2();

A segunda opção, é a mais ideal a ser usada, levando em conta, que ao mesmo tempo que se envia os dados via comando "insert", se recebe o valor "id" gravado pela "sequence" do banco, sem a necessidade de executar dois comandos, ou seja, abrir duas sessões em vez de uma.

O comando "returning" retorna o valor gerado pela "sequence" e gravado na tabela no campo primário.

PostrgreSQL Uso do "Copy To"

Introdução 


Pessoal, hoje, eu gostaria de mostrar um exemplo simples, de como usar o comando "Copy To".  Este comando, faz a carga de uma tabela para dentro de um arquivo. 


Sintaxe do comando


COPY tablename [ ( column [, ...] ) ]
    TO { 'filename' | STDOUT }
    [ [ WITH ] 
          [ BINARY ]
          [ OIDS ]
          [ DELIMITER [ AS ] 'delimiter' ]
          [ NULL [ AS ] 'null string' ]
          [ CSV [ HEADER ]
                [ QUOTE [ AS ] 'quote' ] 
                [ ESCAPE [ AS ] 'escape' ]
                [ FORCE QUOTE column [, ...] ]


Obs: Maiores explicações sobre cada parâmetro, acesse a documentação em português
do PostgreSQL em http://pgdocptbr.sourceforge.net/pg80/sql-copy.html
                
Mãos ao trabalho.  


Vamos criar uma função, onde o conteúdo de uma de nossas tabelas, neste caso, a de "clientes" serão gravados dentro de um arquivo.



CREATE OR REPLACE FUNCTION "CopyTo"(Caminho Varchar)  RETURNS boolean AS
$BODY$
Declare
  /*
  Duas variáveis, A primeira, "Retorno" do tipo "Record" utilizada para guardar registros. Ela   assume a strutura 
  da tabela ou tabelas informadas no SQL. 
  A segunda, "Sql" do tipo "Varchar" utilizada para guardar os comandos SQL, que após  serão executados por    
  outro comando "Execute". 
  */
  Retorno Record;
  Sql     Varchar default '';
Begin 
  /*
  Aqui, é criado uma tabela tempória para receber dados criteriados, de outra tabela de nosso banco de dados.
  */
  Sql = 'Create Temp Table "RemessaTemp"("CodigoCliente" integer) On Commit Drop;';
  Execute Sql;


  /*
  Neste ponto, o SQL, envia para dentro da variável "Retorno" os registros 
  encontrados e criteriados na claúsula "Where".
  */
  For Retorno in
      Select "CodigoCliente"
      From   "Cliente"
      Where  "DataNascimentoCliente" between '2012-01-01' and '2012-03-01'
  Loop 
    /* 
    Após, entramos em um loop, dando um "Insert" dos dados para dentro da tabela temporária.
    */
    XSql = 'Insert Into "Temp" ("CodigoCliente")  Values ('||Quote_Literal(XRetorno."CodigoCliente")||');';
    Execute XSql;
  End Loop;


  /*
  Agora vamos para o final, criar o arquivo com os dados contidos na tabela  temporária.
  */
  XSql = 'Copy "Temp" To '||Quote_Literal($1);
  Execute XSql;
  Return true;
end;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;


Para executar a função, onde que cria o arquivo com os dados de uma tabela, faça:


Select "CopyTo"('Diretório para gravação\Nome do arquivo.csv');


Considerações: No próximo artigo, eu irei demonstrar o inverso deste comando, utilizando o "Copy From", onde irei ler o contéudo de um arquivo e gravar dentro de uma tabela. Uma detalhe importante, mas não exatamente uma regra, é que, o arquivo lido, deverá ter a mesma estrutura de registros igual a sua tabela. No exemplo acima, eu uso um único campo, com isto o arquivo terá uma única coluna. Para demais colunas, usar os caracteres determinados pela sintaxe do comando. 

Uso dos comandos "Create Types" e "Execute" em funções PostgreSQL

       Neste artigo, eu tento demostrar o uso que eu faço dos comandos "Create Type" e do "Execute". 

O "Create Type", permite criar um novo tipo de dado. No meu caso, uso ele para as saídas de dados(lista campos e seus conteúdos pertencentes a uma tabela pesquisada) das funções que faço no banco.

O "Execute", é um comando utilizado em funções "plpgsql", para executar sql, contidos dentro de uma variavel do tipo texto. Este procedimento, é interessante, para que possámos unir strings mais complexas, fazendo delas, um sql mais enxuto, e dando a possíbilidade de NÃO usar comandos como: "Case When" ou "If then", que em muitas vezes, não deixa que o banco uses seus índices, necessários para agilizar uma determinada consulta.

Para criar um type faça:

Create type "ListaPessoas_Type" as
 ("CodigoPessoa"   Integer,
  "NomePessoa"     Varchar(100)) ;

A função a seguir, lista dois campos de uma tabela, chamada "Pessoas", onde a informações listadas, poderão serem "Clientes" ou "Fornecedores".  Vejam, que os types criado, cuja o nome ficou "ListaPessoas_Type", é atrelado a função logo nas primeiras linhas, onde que representa a saída dos dados.

A proposta aqui, é mostrar o uso do create type e do execute. Sei, que consultas como estas, poderiam serem feitas de uma outra forma, de tamanho menor e numa função do tipo "sql" e não "plpgsql".

Para criar a função faça:

Create or replace function "ListaPessoas"(varchar(15))
Returns setof "ListaPessoas_Type" As
$$
declare
   XSQL     Text;
   XRetorno Record; 
begin
  /*Montagem do sql em uma variável text*/
  if $1 = 'Clientes' then
     XSQL = 'Select "CodigoPessoa","NomePessoa"

             From   "Pessoas"

             Where  "TipoCliente" = ''Clientes''     

             Order by "NomePessoa"';
  else   
     XSQL = 'Select "CodigoPessoa","NomePessoa"

             From   "Pessoas"

             Where  "TipoCliente" = ''Clientes''     

             Order by "NomePessoa"';

  end if;
  

  /*Executa o conteúdo da variável XSQL*/

  For XRetorno in
      Execute XSQL
  loop
    Return Next XRetorno;
  end loop;
end;
$$ Language plpgsql;

Linha de comando para fazer a chamada a função

 
Select * From "ListaPessoas"('Clientes');

Observações: Em testes passados, consegui comprovar, o ganho em tempo de retorno de dados pesquisado, quando usamos quando criamos os tipos de campos para o retorno de uma função. 

Backup banco PostgreSQL pelo Linux

Gente, novamente venho a contribuir um pouquinho, com algum pouco conhecimento que tenho sobre Linux e PostgreSQL. Neste script(Linux), do qual dou o nome de "backup.postgres.sh", faço o backup do banco pg, levando em conta:
1º A existência da pasta para receber o arquivo(mkdir -p);
2º Uma analise, e ao mesmo tempo, a exclusão dos arquivos a mais de 3 dias(find);
3º O backup propriamente dito(pg_dump);
4º Por final, o vacuum para limpar e organizar e com isto agilizar o banco para o dia seguinte(vacuum).


#Scritp responsável pela cópia do banco de dados PostgreSQL
#Autor: Marcos Andre G.A
#Data1: 02/04/2012


#1º Mensagem que mostra na tela a data e hora de início do backup.
DATA=`date +%Y%m%d`
HORA=`date +%H%M`
echo "+++ ."
echo "+++ Data/Hora do inicio da copia. "$DATA $HORA
echo "+++ ."

#2º Criando, caso não exista, "mkdir -p" a pasta para receber o banco.

mkdir -p /opt/backups/base_de_dados/

#3º Procedimento de limpeza dos backups com data a mais de 3 dias.
echo "+++ Executando limpeza das copias dos bancos de dados a mais de 3 dias"
find /opt/backups/base_de_dados/ -name 'Trabin*' -ctime +3 -exec rm -fv {} \;



#4º Procedimento que gera cópia do bando de dados.

echo "+++ Gerando copia do banco. Aguarde..."

pg_dump -Fc -Z9 -U postgres -h localhost -f /opt/backups/base_de_dados//Nome_do_Banco_$DATA$HORA.bkp  "Nome_do_Banco"

echo "+++ Nome_do_Banco copiado!"

#5º Neste ponto, gero um vacuum. Abaixo, a lista dos parâmetros possíveis.

echo " "

echo "+++ Gerando o vacuum do banco, Aguarde..."
#-a : passa em todos bancos de dados;
#-d base : para escolher o banco de dados;
#-f : vacuum full;
#-z - vacuum analyze;
#-v : verbose;
#-h : servidor remoto postgres;
#-U : login do usuarioo do banco.


vacuumdb -d "Nome_do_Banco"    -U postgres -q -z
#echo "+++ Vacuum - TrabinBase ok"

Campos associados em uma consulta SQL

Buenas pessoal, hoje rapidamente lembrei de algo que muitas vezes é comum mas não é de uso de todos. Fazer SQL com campos de tabelas associadas. Ou a grosso modo, pegar o valor de um campo de outra tabela e mostrar no sql que estamos construindo. No PG, eu tenho sempre uma função que ao passar o ID que esta gravado na tabela principal a tabela secundária, me retorna o valor do campo que desejo. Isto, daria para fazer sem a necessidade de uma função, mas é mais organizado e tu pode aproveitar em outras partes do sistema.

Bom, esta função, recebe o ID da Cidade que esta na tabela de clientes e retorna o nome da cidade

CREATE OR REPLACE FUNCTION "RetornaCidadeNome"(Integer)
  RETURNS Varchar AS
$BODY$
   Select Cast("NomeCidade" as Varchar)
   From   "Cliente"
   Where  "CodigoIDCliente"=$1
$BODY$
  LANGUAGE 'sql';

Este é o SQL que mostra os dados dos clientes mais o nome da cidade, do cliente 1 ao 1000

Select "CodigoCliente",
           "NomeCliente,
           "RetornaCidadeNome"("CodigoIDCidadeCliente") as "XNomeCidade"
From "Cidade"
Where "CodigoCliente" between 1 and 1000;

Detalhes: Aspas duplas, garante que no postgresql o nome da função fique como foi digitada.

Obs: Pessoal, fiz algumas alterações pontuações, das quais, estão em negrito.

Cálculo do Módulo11 para o Nosso Número

No exemplo anterior postado, eu mostro como fazer um cálculo do dígito verificador módulo10 do banco do brasil, e neste, mostro como é feito o cálculo usando o módulo11. Essas duas funções, referente aos chamados DV(Dígito Verificador), podem serem executadas sem nenhuma alteração, pois, elas não fazem referência as tabelas do banco de dados, são funções das quais chamo de "Genéricas".

CREATE OR REPLACE FUNCTION "TS_ParcelaBloquetoBancoBrasilModulo11"(xdocumento character varying)
  RETURNS character varying AS
$BODY$
declare
  XNumero     Varchar;
  XNumeroOrg  Varchar;
  XCont       Integer;
  XParcial    Integer;
  XDivisorM11 Integer;
  XResto      Integer;
  XDigVerM11  Integer;
  XMultiplo   Integer;
begin
  /*
   Função responsável por calcular o DV módulo 11
   Esta informação vai impressa no bloqueto
  */
  --Pegando somente os números do documentos
  XNumeroOrg = regexp_replace(XDocumento, '[^[:digit:]]', '', 'g');

  /*
  *** Calculando Módulo "11" ***
  */
  XDivisorM11 = 2;
  XParcial    = 0;
  XNumero     = trim(XNumeroOrg);
  XCont       = length(XNumero);
  loop
    --Pegando número a número e multiplicando por 2 a 9 e acumulando o resultado linha a linha
    if XDivisorM11 > 9 then
       XDivisorM11 = 2;
    end if;
    XParcial    = XParcial    + Cast(SubStr(trim(XNumero),XCont,1) as Integer) * XDivisorM11;
    XDivisorM11 = XDivisorM11 + 1;
    XCont       = XCont - 1;
    if XCont = 0 then
       Exit;
    end if;

  end loop;
  /*
  Tratando o resultado da multiplicação
  */
  XResto     = Cast(((XParcial-(trunc(Cast(XParcial as Numeric(15,2))/11))*11)) as Integer);
  XDigVerM11 = 11 - XResto;
  if XDigVerM11 = 0 or XDigVerM11 = 10 or XDigVerM11 = 11 then
     XDigVerM11 = 1;
  end if;

  Return XDigVerM11;
end;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION "TS_ParcelaBloquetoBancoBrasilModulo11"(character varying) OWNER TO postgres;

*** Para executar esta função digite: ***
Select "TS_ParcelaBloquetoBancoBrasilModulo11"('12345678')

Cálculo do Módulo10 para o Nosso Número

Neste exemplo, criei em plpgsql, uma função para o cálculo do módulo10, para usar na geração do "Nosso Número" para a remessa e bloquetos do banco do brasil. 


CREATE OR REPLACE FUNCTION "TS_CalculaModulo10BancoBrasili"(xdocumento character varying)
  RETURNS character varying AS
$BODY$
declare 
  XNumero     Varchar;
  XNumeroOrg  Varchar; 
  XCont       Integer;
  XParcial    Integer;
  XParcialPosterior Integer;
  XDivisorM10 Integer;
  XResto      Integer;
  XDigVerM10  Varchar(1);
  XMultiplo   Integer;
  XTotalM10   Integer; 
begin
  /*
   Função responsável por calcular o módulo 10 
   para os campos da linha digitável cobrança banco do brasil
  */
  --Pegando somente os números do documentos
  XNumeroOrg = regexp_replace(XDocumento, '[^[:digit:]]', '', 'g');


  /* 
  *** Calculando Módulo "10" *** 
  */
  XDivisorM10 = 2;
  XParcial    = 0;
  XNumero     = trim(XNumeroOrg);
  XCont       = length(XNumero);
  loop
    XTotalM10 = Cast(SubStr(trim(XNumero),XCont,1) as Integer) * XDivisorM10;
    if XTotalM10 < 10 then
       XParcial    = XParcial    + XTotalM10;
    else
       XParcial    = XParcial    + (Cast(SubStr(XTotalM10,1,1) as Integer) + Cast(SubStr(XTotalM10,2,1) as Integer));
    end if;
    XCont       = XCont - 1;
    if XCont = 0 then
       Exit; 
    end if;
    --Pegando número a número e multiplicando por 2 e 1 e acumulando o resultado linha a linha
    if XDivisorM10 = 1 then
       XDivisorM10 = 2;
    else
       XDivisorM10 = 1;
    end if;


  end loop;
  /*
  Após achar o resto da divisão, encontrar o maior 
  valor da soma e fazer a subtração
  */
  if Length(XParcial) <= 2 then
     XParcialPosterior = (SubStr(Cast(XParcial+10 as Varchar),1,1)||'0');
  elsif Length(XParcial) = 3 then  
     XParcialPosterior = (SubStr(Cast(XParcial+10 as Varchar),1,2)||'0');
  elsif Length(XParcial) = 4 then  
     XParcialPosterior = (SubStr(Cast(XParcial+10 as Varchar),1,2)||'0');
  end if;   
  
  if (XParcialPosterior-XParcial) = 10  then -- Se maior que 9 desmembrar o número e soma-los Resto = 18 fazer 1 + 8 = 9
     Return (0);
  else   
     Return (XParcialPosterior-XParcial);
  end if; 
end;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

*** Para executar esta função digite: ***
Select "TS_CalculaModulo10BancoBrasili"('12345678')

No próximo post, mostrarei a função que cálcula o DV, usando o módulo11.