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 &#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(dot)klaver(at)aklaver(dot)com