Translate

Pesquisar este blog

sexta-feira, 19 de junho de 2015

Introdução à Linguagem PL/PGSQL do PostgreSQL - Parte 2.

Controle de fluxo.

Toda linguagem de programação têm controle de fluxo, que são
instruções IF, CASE, FOR, WHILE, FUNCTION, etc.


Declaração de variável local:
CREATE OR REPLACE FUNCTION myfunction(entrada integer)
  RETURNS integer AS
$BODY$
declare
    i int;
    v varchar(200);
    rec record;
begin
...




IF-THEN-ELSE:

IF <condição SQL> THEN
 <instrução>;
 <instrução>;
ELSIF  <condição SQL> THEN
  <instrução>;
  <instrução>;
ELSE
  <instrução>;
  <instrução>;
END IF;



LOOP-END LOOP:

LOOP
  <instrução>;
  <instrução>;
    EXIT WHEN <condição SQL>;
  <instrução>;
  <instrução>;
END LOOP;

* Observação: A instrução EXIT WHEN pode ser usada em qualquer lugar dentro do loop.



RECORD IN SELECT LOOP-END LOOP:

declare
   rec record;
begin
     FOR rec IN select id, nome from tabela order by nome
     LOOP
     <instrução>;
     <instrução>;
     END LOOP;
...


* Observação: É preciso criar uma variável do tipo record ou rowtype para usar nesse tipo de loop;



Arrays:
Arrays de qualquer tipo de dados podem ser declarados. Coloca-se o tipo de dados seguido de [].

declare
   arr varchar[];
   str varchar;
begin
   arr:=array(select nome from usuario limit 100); --Aqui definimos com um SELECT em uma tabela.
   arr:=ARRAY['Zé', 'Fulano', 'Ciclano'];                --Colchetes é diferente de parênteses.
   arr:=string_to_array('Zé;Fulano;Beltrano',  ';');   --É possível expandir varchar delimitado para array.
   str:=array_to_string(arr,  ';');                                --O oposto também é possível.

   arr:=arr||'Lóide';                                                    --Concatenando um array.

   IF array_length(arr, 1)>5 THEN                            --Retornando o tamanho de um array.
   ...
   END IF;

   FOR str IN SELECT unnest(arr)                         --Estamos fazendo um LOOP com o array.
   LOOP
   <instrução>;
   <instrução>;
   END LOOP;


end;



CASE (com variável):

CASE var
WHEN <valor1>,<valor2> THEN
  <instrução>;
  <instrução>;
WHEN <valor3>,<valor4> THEN
  <instrução>;
  <instrução>;
...
ELSE
   <instrução>;
   <instrução>;
END CASE;



CASE (sem variável):

CASE
   WHEN <condição1> THEN
  <instrução>;
  <instrução>;
WHEN <condição2> THEN
  <instrução>;
  <instrução>;
...
ELSE
   <instrução>;
   <instrução>;
END CASE;



FOR:

FOR i IN 1..10 --i vai de 1 a 10
LOOP
    <instrução>;
    <instrução>;
END LOOP;

FOR i IN REVERSE 10..1 --i vai de 10 a 1
LOOP
    <instrução>;
    <instrução>; 
END LOOP;

FOR i IN REVERSE 10..1 BY 2 --i vai de 10,8,6,4,2
LOOP
    <instrução>;
    <instrução>; 
END LOOP;

quinta-feira, 18 de junho de 2015

Introdução à Linguagem PL/PGSQL do PostgreSQL - Parte 1

Programar dentro do banco de dados é uma polêmica entre desenvolvedores, com certeza. Mas há vantagens em usar o banco de dados para desenvolver a lógica de negócio dos aplicativos.

Eu mesmo já desenvolvi usando o PL/PGSQL, e achei muito produtivo e performático.

Produtivo principalmente quando o sistema exige consultas SQL complexas, e você está usando uma linguagem de programação como Java.
Performático porque uma consulta pré-compilada no banco exige menos passos do banco para executar as instruções.

A principal desvantagem consiste em que não poderá portar para outros DBs esse código feito. Mas se você usa apenas um banco de dados isso não é problema.

Um exemplo de função:
CREATE OR REPLACE FUNCTION teste(entrada integer)
  RETURNS integer AS
$BODY$
begin
    return entrada*5;
end;

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;


A função teste apenas retorna o parâmetro entrada vezes 5. Para executá-lo com o parâmetro chame o seguinte comando:

Select * from teste(6)

Ela retornará 30 que é 5*6.




Veja outro exemplo. Suponha que você tenha uma tabela usuarios (id int primary key, nome varchar, descricao varchar):

CREATE OR REPLACE FUNCTION get_usuario(id_user int, out user_nome varchar, out user_descricao varchar)
  RETURNS record AS
$BODY$
begin


   select a.nome, a.descricao from usuarios as a
   where a.id= id_user
   into user_nome, user_descricao;

   if not found then
     raise exception '%', 'Usuário não encontrado';
   end if;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;



  • Veja que os parâmetros user_nome e user_descricao são parâmetros de saída(OUT). 
  • O tipo de retorno da função de torna record. Isso tem que ser porque temos mais de um valor de retorno.
  • Pegamos os dados que precisamos com a instrução SELECT e colocamos os valores nas variáveis usando a cláusula INTO.
  • Em seguida é usada a variável do PG found. Ela serve para testar se algum registro retornado com SELECT ou UPDATE foi encontrado. Se nenhum registro foi encontrado, found será igual a false. Se o registro não foi encontrado, disparamos exceção.

select * from get_usuario(7)
Isso retornará os dados do usuário 7, ou dispara exceção se ele não existe.
Observe que não colocamos os parâmetros de saída, eles é que retornarão da função.





Também é possível retornar várias linhas de uma vez só. Para isso o tipo de retorno deve ser precedido de SETOF.

CREATE OR REPLACE FUNCTION get_usuarios(out user_id int, out user_nome varchar, out user_descricao varchar)
  RETURNS setof record AS
$BODY$
begin

  return query select id, nome, descricao from usuarios
  order by nome;  


end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

  • Quando o retorno da função é declarado com SETOF, deve-se retornar os dados com a instrução RETURN QUERY ao invés de setar uma variável sozinha. 
  • É possível chamar RETURN QUERY mais de uma vez, porque ele NÃO vai retornar da função imediatamente.

Atualizando o PostgreSQL de versão

Ao contrário do Firebird, o PostgreSQL usa um formato de banco para cada versão.

Até mesmo trocar a mesma versão do PG em Windows e Linux, ou entre 32 ou 64 bits não vai funcionar. Por motivos óbvios, restaurar um Point-In-Time Recovery (PITR) também não vai funcionar.

Por isso o PostgreSQL você deve fazer um dump da database antiga com o  pg_dumpall. E depois de atualizar a database deve fazer um restore com psql.

$ pg_dumpall -h myhost -p porta > db.sql

Agora instale o novo PostgreSQL, depois faça a restauração da base com o psql.

$ cat db.sql | psql -h myhost -p porta -U usuario

Atualizando o Firebird de versão.

No Firebird, não é necessário fazer o dump de uma database, apenas instalar o novo banco, desinstalando o antigo. Os bancos de dados (Arquivos FDB) podem permanecer inalterados porque o novo banco sempre reconhece as versões antigas.

Mas se você está migrando para uma versão mais antiga isso pode ser diferente. É preciso ver o dialeto em que o banco está e ver se o antigo banco suporta.