Re: execute block like Firebird does

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 < :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.klaver@aklaver.com