On 02/12/2018 10:02 AM, PegoraroF10 wrote:
Explaining better my problem. All reports our customer use are customizable.
So, when a customer runs a report it just runs all SQLs that are inside that
report, being SQL or Execute Blocks. But because they are completelly
customizable, one customer has 80 reports with 300 Execute Blocks on them
and other one has just 10 reports with 100 execute blocks and they can be
used to different purposes. If, instead of rewriting them, just create a
function on each one, imagine that Customer A will have 300 hundred
functions and Customer B will have only 100. And worse, some of those
functions have same header but different body. Can you imagine a mess like
that ?


I can, but not sure why it would have to happen? While Postgres supports function overloading there is no requirement that you have to do it. Since you are charge of naming the functions you could create distinct names for function, maybe appended with customer name or id for instance.


95% of those execute blocks are simple and can be replaced by a well done
SQL or a CTE. But we have hundreds of these blocks and we need to not just
recompile them but rewrite them using a different approach.

That is going to happen whatever path you choose. I would start with some of the easier blocks and see how difficult it would be to transform to pl/pgsql. If it is not that bad(from below I would think not) then you can knock out the 95% in a reasonable time. Then you can turn your attention to the 5%.

For below see:

https://www.postgresql.org/docs/10/static/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING


Show you some examples of our execute blocks.
This one is easy to understand and can be easily replaced.
execute block returns(CargaTotal Moeda) as
   declare variable Aluno_ID I32;
   declare variable Turma_ID I32;
   declare variable MateriasIn t10000;
   declare variable Presente I16;
   declare variable JustificativaHistorico_ID I32;
   declare variable qtdeAulas i32;
   declare variable qtdePresencas i32;
begin
   select Pessoa_Id From mov_Contrato Where Contrato_Id = %d Into :Aluno_Id;
   Turma_Id = %d;
   qtdeAulas     = 0;
   qtdePresencas = 0;
   for select Presente, JustificativaHistorico_ID from col_Aula A inner join
col_Frequencia F on F.Aula_ID = A.Aula_ID where
     a.Materia_Id in (select distinct a.Materia_Id from col_aula a where
a.Turma_Id = :Turma_Id) and f.Aluno_ID = :Aluno_Id
   into :Presente, :JustificativaHistorico_ID do begin
     qtdeAulas = :qtdeAulas + 1;
     if ((:Presente=1) or (:JustificativaHistorico_ID is not Null)) then
       qtdePresencas = :qtdePresencas + 1;
   end
   if (:qtdeAulas > 0) then
     CargaTotal = (Cast(:qtdePresencas as Moeda) / Cast(:qtdeAulas as Moeda)
* 100.00);
   else
     CargaTotal = 0;
   Suspend;
end

But other ones needs to be carefully read to be translated.

execute block returns (
   Curso_ID         type of column col_Curso.Curso_ID,
   Turma_ID         type of column col_Turma.Turma_ID,
   Curso            type of column col_Curso.Descricao,
   Turma            type of column col_Turma.Nome,
   Iniciando        Logico,
   PeriodoSequencia I32,
   Periodo          T50,
   Ordem            I32,
   DescricaoSemana  varchar(15),
   SemanaInicio     type of column col_Aula.Data,
   SemanaFim        type of column col_Aula.Data,
   AulaData         Data,
   Contrato_ID      type of column mov_Contrato.Contrato_ID,
   Contrato         type of column mov_Contrato.NumeroContrato,
   Aluno_ID         type of column rel_AlunoTurma.Aluno_ID,
   AlunoDaAula      type of column rel_AlunoTurma.lkAluno,
   StatusAtual      type of column mov_Contrato.lkStatus,
   StatusNoPeriodo  type of column mov_Contrato.lkStatus,
   Presente         type of column col_Frequencia.Presente
) as
   declare variable Semanas          I32 = %0:d;
   declare variable I                I32;
   declare variable tmpData          Data;
   declare variable PrevIni          Data = '%1:s'; --Execute block doesn´t
have IN Param, so we change this variable using Format();
   declare variable PrevFim          Data = '%2:s'; --This one too.
   declare variable HoraInicio       VarChar(6) = ' 00:00';
   declare variable HoraFinal        VarChar(6) = ' 23:59';
   declare variable PeriodoManha     type of column sys_LookUp.Descricao =
'Matutino';
   declare variable PeriodoTarde     type of column sys_LookUp.Descricao =
'Vespertino';
   declare variable PeriodoNoite     type of column sys_LookUp.Descricao =
'Noturno';
   declare variable StatusPauta      Memo;
   declare variable StatusDesistente I32;
   declare variable sqlTemp           Memo;
   declare variable Turmas Memo = ':ListaTurma';
   declare variable sqlPeriodo Memo = 'select :Numero, '':Descricao'',
'':DataIni'', '':DataFim'' from rdb$database:where';
   declare variable sqlAulas Memo;
   declare variable sqlLista Memo = 'select distinct
     col_Curso.Curso_ID,
     col_Curso.Descricao,
     col_Turma.Turma_ID,
     col_Turma.Nome,
     case when extract(hour from col_Aula.Data) < 12 then 1 when extract(hour
from col_Aula.Data) between 12 and 18 then 2 when extract(hour from
col_Aula.Data) > 18 then 3 end,
     case when extract(hour from col_Aula.Data) < 12 then '':PeriodoManha''
when extract(hour from col_Aula.Data) between 12 and 18 then
'':PeriodoTarde'' when extract(hour from col_Aula.Data) > 18 then
'':PeriodoNoite'' end
   from
     col_Turma inner join col_Curso using(Curso_ID) inner join col_Aula
using(Turma_ID)
   where 1=1
     and col_Turma.Turma_ID in (:Turmas)
     and col_Aula.Data between '':PrevIni'' and '':PrevFim''
   order by
     col_Curso.Descricao,/*Iniciando  */ 5,  /* PeriodoSequencia */6,
col_Turma.Nome';
begin
   I= 2;
   tmpData = dateADD(-1 Day to :prevIni);
   sqlAulas = '';
   while (:I &#60; :Semanas + 2) do begin
     I = :I + 1;
     sqlAulas = sqlAulas || replace(:sqlPeriodo, ':Numero', :I);
     sqlAulas = replace(:sqlAulas, ':Descricao', 'Semana ' || lpad(:I - 2, 2,
0) );
     sqlAulas = replace(:sqlAulas, ':DataIni',   :tmpData || :HoraInicio);
     tmpData = dateadd(1 week to :tmpData);
     sqlAulas = replace(:sqlAulas, ':DataFim',   :tmpData || :HoraFinal);
     sqlAulas = replace(:sqlAulas, ':where',   ' union' || ascii_char(13));
   end
   sqlLista = replace(:sqlLista, ':PeriodoManha', :PeriodoManha);
   sqlLista = replace(:sqlLista, ':PeriodoTarde', :PeriodoTarde);
   sqlLista = replace(:sqlLista, ':PeriodoNoite', :PeriodoNoite);
   sqlLista = replace(:sqlLista, ':Turmas', :Turmas);
   sqlLista = replace(:sqlLista, ':PrevIni', :PrevIni || :HoraInicio);
   sqlLista = replace(:sqlLista, ':PrevFim', :PrevFim || :HoraFinal);
   for execute statement :sqlLista into :Curso_ID, :Curso, :Turma_ID, :Turma,
:PeriodoSequencia, :Periodo
   do begin
     select min(col_Aula.data) from col_aula where col_Aula.Turma_ID =
:Turma_ID into :tmpData;
     if (:tmpData is not null) then begin
       sqlTemp = :sqlAulas || replace(:sqlPeriodo, ':Numero', 0);
       sqlTemp = replace(:sqlTemp, ':Descricao', 'Primeira Aula');
       sqlTemp = replace(:sqlTemp, ':DataIni',   :tmpData || :HoraInicio);
       sqlTemp = replace(:sqlTemp, ':DataFim',   :tmpData || :HoraFinal);
       sqlTemp = replace(:sqlTemp, ':where',   ' union' || ascii_char(13));
     end
     select max(col_Aula.data) from col_aula where col_Aula.Turma_ID =
:Turma_ID and col_Aula.data > :prevIni into :tmpData;
     if (:tmpData is not null) then begin
       sqlTemp = :sqlTemp || replace(:sqlPeriodo, ':Numero', 1);
       sqlTemp = replace(:sqlTemp, ':Descricao', 'Ultima Aula');
       sqlTemp = replace(:sqlTemp, ':DataIni',   :tmpData || :HoraInicio);
       sqlTemp = replace(:sqlTemp, ':DataFim',   :tmpData || :HoraFinal);
       sqlTemp = replace(:sqlTemp, ':where',   ' union');
     end
     sqlTemp = substring(trim(:sqlTemp) from 1 for
(OCTET_LENGTH(trim(:sqlTemp)) - OCTET_LENGTH(' union'))) || ' where 1=1
order by 1';
     for execute statement :sqlTemp into :ordem, DescricaoSemana,
:SemanaInicio, :SemanaFim do begin
       for select
         alunoaula.Data,
         alunoaula.contrato_id,
         alunoaula.numerocontrato,
         alunoaula.aluno_id,
         alunoaula.lkaluno,
         alunoaula.statusperiodo,
         alunoaula.statusatual,
         alunoaula.presente
       from
         alunoaula(null, :SemanaInicio, :SemanaFim, :Turma_ID)
       into
         :AulaData, :Contrato_ID, :Contrato, :Aluno_ID, :AlunoDaAula,
:StatusNoPeriodo, :statusAtual, :Presente
       do
         suspend;
     end
   end
end

As you can see, they are like functions, have for, while, if, etc.




--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html




--
Adrian Klaver
adrian.kla...@aklaver.com

Reply via email to