Thread: execute block like Firebird does

execute block like Firebird does

From

PegoraroF10

Date:

11 February 2018, 11:57:24

We are migrating our databases from Firebird to PostGres. A useful feature
Firebird has is Execute Block.
What it does is just return a record set from that dynamic SQL, just like a
PostGres function, but without creating it.
It sound like ...
execute block returns(ID Integer, Name varchar(50), LastInvoice Date, ...)
as 
begin
  select bla, bla, bla into ...;
  select bla, bla into ...;
  suspend;
end
I know we could create a function but we have several hundred of these
blocks running, so ... it would be a huge work to do. 
So, there is a way to run a dynamic sql which returns a set of records ?



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

Re: execute block like Firebird does

From

Andreas Kretschmer

Date:

11 February 2018, 14:46:38


Am 11.02.2018 um 06:57 schrieb PegoraroF10:
> We are migrating our databases from Firebird to PostGres. A useful feature
> Firebird has is Execute Block.
> What it does is just return a record set from that dynamic SQL, just like a
> PostGres function, but without creating it.
> It sound like ...
> execute block returns(ID Integer, Name varchar(50), LastInvoice Date, ...)
> as
> begin
>    select bla, bla, bla into ...;
>    select bla, bla into ...;
>    suspend;
> end
> I know we could create a function but we have several hundred of these
> blocks running, so ... it would be a huge work to do.
> So, there is a way to run a dynamic sql which returns a set of records ?

you can use a DO - block:


https://www.postgresql.org/docs/current/static/sql-do.html


Regards, Andreas

-- 
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

Re: execute block like Firebird does

From

Fabrízio de Royes Mello

Date:

11 February 2018, 19:39:44

Em dom, 11 de fev de 2018 às 06:47, Andreas Kretschmer <andreas@a-kretschmer.de> escreveu:

Am 11.02.2018 um 06:57 schrieb PegoraroF10:
> We are migrating our databases from Firebird to PostGres. A useful feature
> Firebird has is Execute Block.
> What it does is just return a record set from that dynamic SQL, just like a
> PostGres function, but without creating it.
> It sound like ...
> execute block returns(ID Integer, Name varchar(50), LastInvoice Date, ...)
> as
> begin
> select bla, bla, bla into ...;
> select bla, bla into ...;
> suspend;
> end
> I know we could create a function but we have several hundred of these
> blocks running, so ... it would be a huge work to do.
> So, there is a way to run a dynamic sql which returns a set of records ?

you can use a DO - block:

https://www.postgresql.org/docs/current/static/sql-do.html

But DO blocks returns "void", I mean you can't return values/records from this statement.

Regards,

Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Re: execute block like Firebird does

From

PegoraroF10

Date:

11 February 2018, 19:50:46

but DO doesn´t return values, or it does ?

execute block returns(ID Integer, Name varchar(50), LastInvoice Date, ...)
as
begin
  for select ID, Name from Customers where ... into ID, Name do begin
    select bla, bla, bla from functionX(ID) into ...;
    if ... then
      bla = X
    else
      bla = Y;
    if bla = XXX then
      suspend; -- here we return a record and as we are inside a loop we
will return several records;
  end
end

As you can see, this entire block can be called from client dynamically,
their result fields are defined when it runs and we can return a set of
records. So, DO doen´t work this way, does it ?



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

Re: execute block like Firebird does

From

Pavel Stehule

Date:

11 February 2018, 20:38:35

2018-02-11 14:50 GMT+01:00 PegoraroF10 <marcos@f10.com.br>:

but DO doesn´t return values, or it does ?

execute block returns(ID Integer, Name varchar(50), LastInvoice Date, ...)
as
begin
for select ID, Name from Customers where ... into ID, Name do begin
select bla, bla, bla from functionX(ID) into ...;
if ... then
bla = X
else
bla = Y;
if bla = XXX then
suspend; -- here we return a record and as we are inside a loop we
will return several records;
end
end

As you can see, this entire block can be called from client dynamically,
their result fields are defined when it runs and we can return a set of
records. So, DO doen´t work this way, does it ?

You can use temporary function in PostgreSQL. DO command has not result. Theoretically, you can use a cursor with transaction scope. It can be filled in DO command and outer can be read by FETCH command.

Regards

Pavel

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

Re: execute block like Firebird does

From

Steven Lembark

Date:

11 February 2018, 21:52:41

> > you can use a DO - block:
> >
> >
> > https://www.postgresql.org/docs/current/static/sql-do.html
> >  
> 
> But DO blocks returns "void", I mean you can't return values/records
> from this statement.

Insert the necessary records into a temporary table, process them,
post-process them into variables or destination table?

-- 
Steven Lembark                                       1505 National Ave
Workhorse Computing                                 Rockford, IL 61103
lembark@wrkhors.com                                    +1 888 359 3508

Re: execute block like Firebird does

From

Fabrízio de Royes Mello

Date:

11 February 2018, 22:23:29

Em dom, 11 de fev de 2018 às 13:54, Steven Lembark <lembark@wrkhors.com> escreveu:

> > you can use a DO - block:
> >
> >
> > https://www.postgresql.org/docs/current/static/sql-do.html
> >
>
> But DO blocks returns "void", I mean you can't return values/records
> from this statement.

Insert the necessary records into a temporary table, process them,
post-process them into variables or destination table?

Can be a solution, but it can lead to a catalog bloat.

Regards,

Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Re: execute block like Firebird does

From

Thiemo Kellner

Date:

11 February 2018, 22:41:43


On 02/11/18 06:57, PegoraroF10 wrote:
> I know we could create a function but we have several hundred of these
> blocks running, so ... it would be a huge work to do.
> So, there is a way to run a dynamic sql which returns a set of records ?

But don't you need to touch the code of those blocks anyway? Could you 
write automating code to prepend the function header?

-- 
+49 (0)1578-772 37 37
+41 (0)78 947 36 21
SIP/iptel.org: thiemo.kellner
Öffentlicher PGP-Schlüssel:
http://pgp.mit.edu/pks/lookup?op=get&search=0xCA167FB0E717AFFC

Attachment

thiemo.vcf

Re: execute block like Firebird does

From

PegoraroF10

Date:

12 February 2018, 19:48:21

We can change all those execute blocks, but it would be a huge work if we
need to rewrite them all.
Today, just for a test, I replaced a Firebird execute block to a Postgres
CTE. OK, worked but I spend 40 minutes and the problem is that we have
hundreds of these execute blocks and on each one we need to rethink,
rewrite, retest.

When we changed all our triggers and procedures from Firebird to PostGres we
needed only to rewrite "first 1" to "limit 1", "starting with" to "like" as
examples. So, just a Search and Replace will do solve it.

And now if PostGres doesn´t have something similar to Execute Block we have
to change lots of things. As you may know, change a function body to a CTE
is not so trivial.

Another approach to solve my problem would be a function that receives a
dynamic SQL, runs it and returns a XML or JSON and on client side I convert
that XML back to a recordset. Is that possible ?



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

Re: execute block like Firebird does

From

Adrian Klaver

Date:

12 February 2018, 22:30:30

On 02/12/2018 05:48 AM, PegoraroF10 wrote:
> We can change all those execute blocks, but it would be a huge work if we
> need to rewrite them all.
> Today, just for a test, I replaced a Firebird execute block to a Postgres
> CTE. OK, worked but I spend 40 minutes and the problem is that we have
> hundreds of these execute blocks and on each one we need to rethink,
> rewrite, retest.
> 
> When we changed all our triggers and procedures from Firebird to PostGres we
> needed only to rewrite "first 1" to "limit 1", "starting with" to "like" as
> examples. So, just a Search and Replace will do solve it.
> 
> And now if PostGres doesn´t have something similar to Execute Block we have
> to change lots of things. As you may know, change a function body to a CTE
> is not so trivial.

I do not see a direct correspondence between Execute Block and anything 
in Postgres. This means one way or another you will be rewriting code.

> 
> Another approach to solve my problem would be a function that receives a
> dynamic SQL, runs it and returns a XML or JSON and on client side I convert
> that XML back to a recordset. Is that possible ?

Why not just return a recordset directly?:

https://www.postgresql.org/docs/10/static/plpgsql-overview.html#PLPGSQL-ARGS-RESULTS

"PL/pgSQL functions can also be declared to return a “set” (or table) of 
any data type that can be returned as a single instance. Such a function 
generates its output by executing RETURN NEXT for each desired element 
of the result set, or by using RETURN QUERY to output the result of 
evaluating a query."

Evaluating what you are trying to do would be helped by a complete 
working example of one of your Execute Blocks.

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

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: execute block like Firebird does

From

"David G. Johnston"

Date:

12 February 2018, 22:48:47

On Mon, Feb 12, 2018 at 6:48 AM, PegoraroF10 <marcos@f10.com.br> wrote:

Another approach to solve my problem would be a function that receives a
dynamic SQL, runs it and returns a XML or JSON and on client side I convert
that XML back to a recordset. Is that possible ?

Yes, you can pass "text" SQL into a pl/pgsql function and "EXECUTE" it. That text must be plain SQL though, not pl/pgsql.

Converting pl/pgsql into plain SQL and executing it as a CTE seems like an over-solution. What should be reasonably possible to rewrite the "execute block" as a "create function" then modify your clients to do send "select * from function();" instead of "execute block ..."

If I was you I'd even be curious enough to see if maybe there is an external third-party extension "pl/firebase" language out there which would let you comfortably copy-paste the block text into the function body with minimal or no editing.

David J.

p.s. reading PostGres is hard on our (mine at least) eyes. Its either Postgres, or PostgreSQL - neither with a capital G.

Re: execute block like Firebird does

From

PegoraroF10

Date:

12 February 2018, 22:57:05

I know I need to think a different approach of what execute blocks does on
Firebird.
What I was trying to was just let them the way they were wrote, because
their results are correct and with more time replace them to a new way.

But, if that way cannot be used, I´ll probably write some hundred of
functions right now and use them, because this way works for sure. And then,
with more time to spend, I´ll replace them one by one.





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

Re: execute block like Firebird does

From

Adrian Klaver

Date:

12 February 2018, 23:08:13

On 02/12/2018 08:57 AM, PegoraroF10 wrote:
> I know I need to think a different approach of what execute blocks does on
> Firebird.
> What I was trying to was just let them the way they were wrote, because
> their results are correct and with more time replace them to a new way.

That may not be necessary. As someone mentioned upstream you maybe able 
to replace the EXECUTE BLOCK with CREATE OR REPLACE FUNCTION and then do 
some clean up/additions to the enclosed code. To be more certain about 
this we would need to see a complete example of one of EXECUTE BLOCKs. 
If that is possible for security reasons, then a made example that does 
the same thing.

> 
> But, if that way cannot be used, I´ll probably write some hundred of
> functions right now and use them, because this way works for sure. And then,
> with more time to spend, I´ll replace them one by one.

Why? Once you create the functions and they do the task what is the 
purpose of replicating them and more to the point how would you?

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

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: execute block like Firebird does

From

"Daniel Verite"

Date:

12 February 2018, 23:44:47

    PegoraroF10 wrote:

> Another approach to solve my problem would be a function that receives a
> dynamic SQL, runs it and returns a XML or JSON and on client side I convert
> that XML back to a recordset. Is that possible ?

Yet another tool that can be handy to transfer polymorphic
results is a cursor through the plpgsql REFCURSORs:

https://www.postgresql.org/docs/10/static/plpgsql-cursors.html

Here's an actual example with a DO block :

  BEGIN;

  DO $$
   DECLARE
     c1 refcursor := 'cursor1';
     c2 refcursor := 'cursor2';
   BEGIN
     OPEN c1 FOR select 'result #1 column 1', 'result #1 column 2';
     OPEN c2 FOR select 'result #2 column 1', 'result #2 column 2';
   END;
  $$ LANGUAGE plpgsql;

  FETCH cursor1;
  FETCH cursor2;

  COMMIT;

The interface is a bit weird because the value of the refcursor variable
is the name of    the underlying SQL cursor object. The name can
also be auto-generated by postgres; the above code uses fixed
names instead. Anyway that cursor, once instanciated in the
DO block, can be fetched from with FETCH statements initiated
client-side or by other server-side code.

The above code will retrieve two independant resultsets:

postgres=# FETCH cursor1;
      ?column?        |       ?column?
--------------------+--------------------
 result #1 column 1 | result #1 column 2
(1 row)

postgres=# FETCH cursor2;
      ?column?        |       ?column?
--------------------+--------------------
 result #2 column 1 | result #2 column 2
(1 row)

These cursors disappear at transaction end, or they can be explicitly
closed with CLOSE statements.

Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

Re: execute block like Firebird does

From

PegoraroF10

Date:

13 February 2018, 00:02:13

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 ?

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.

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

Re: execute block like Firebird does

From

"David G. Johnston"

Date:

13 February 2018, 00:15:21

On Monday, February 12, 2018, PegoraroF10 <marcos@f10.com.br> 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.

I love PostgreSQL...but why are you migrating away from something with this extent of dependency on Firebird?

I'd probably be trying to figure out some kind of hybrid platform here and not a clean cut-over.

David J.

Re: execute block like Firebird does

From

Adrian Klaver

Date:

13 February 2018, 00:18:16

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

Re: execute block like Firebird does

From

PegoraroF10

Date:

13 February 2018, 00:31:35

I loved Firebird but now we have to have some cool things that Postgres has
and Firebird doesn´t.
Fiirebird has just 3 cool features that Postgres doesn´t: Computed by
columns, Position for fields and triggers and execute blocks, just that.

Replication, PITR, JSON and JSONB, XML, inherited tables, arrays, grouping
sets, User defined datatypes, SELECT without a FROM clause, Parallel
queries, Tuple comparison, Transactional DDL and a lot of other useful
things that Firebird doesn´t know what it is.



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

Re: execute block like Firebird does

From

Edson Carlos Ericksson Richter

Date:

14 February 2018, 19:00:28

Em 11/02/2018 03:57, PegoraroF10 escreveu:
> We are migrating our databases from Firebird to PostGres. A useful feature
> Firebird has is Execute Block.
> What it does is just return a record set from that dynamic SQL, just like a
> PostGres function, but without creating it.
> It sound like ...
> execute block returns(ID Integer, Name varchar(50), LastInvoice Date, ...)
> as
> begin
>    select bla, bla, bla into ...;
>    select bla, bla into ...;
>    suspend;
> end
> I know we could create a function but we have several hundred of these
> blocks running, so ... it would be a huge work to do.
> So, there is a way to run a dynamic sql which returns a set of records ?

Can't you use "with ... select ..."?

Like:

with qry1 as (select bla, bla, bla from xyz), qry2 as (select bla, bla 
from ...)
select * from qry1
union all
select * from qry2


?

Regards,

Edson

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

Re: execute block like Firebird does

From

PegoraroF10

Date:

30 May 2018, 18:50:59

Some time ago I´ve posted this thread because we had lots of these execute
blocks to be translated to Postgres. Now, continuing on same matter, I would
like to discuss the same topic, basically calling the server one time only,
instead of several times.
Usually we want get some values from server and then insert or update some
records based on that returned values. Each of these calls will spend time
and this is the point I would like to discuss.

How to send a script to server and return one or more values from that
execution ?

You´ll probably answer me that I could solve that with a function. But
suppose those executions are dynamic, depends on businness rules or any
other problem.

So, is that possible to change a DO structure is ran, to be possible to
return one or more values ?
It would be like ...
DO returns(ID Integer, Description Text) as
$$
begin
  select ...
  insert ...
  select ... into ID, Description
end
$$

Using this way would be possible to create that script on client, call it
just one time and have a result for that execution, exactly the way a
"execute block" does on Firebird.

Is that possible or there is a way to call just one time the server to
return values without creating a function to each call ?

What do you think change how DO structure is ran to have results from ?
Version 12, what do you think ?



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

Re: execute block like Firebird does

From

Adrian Klaver

Date:

30 May 2018, 19:16:02

On 05/30/2018 05:50 AM, PegoraroF10 wrote:
> Some time ago I´ve posted this thread because we had lots of these execute
> blocks to be translated to Postgres. Now, continuing on same matter, I would
> like to discuss the same topic, basically calling the server one time only,
> instead of several times.
> Usually we want get some values from server and then insert or update some
> records based on that returned values. Each of these calls will spend time
> and this is the point I would like to discuss.
> 
> How to send a script to server and return one or more values from that
> execution ?
> 
> You´ll probably answer me that I could solve that with a function. But
> suppose those executions are dynamic, depends on businness rules or any
> other problem.

Which can be done in a function.

> 
> So, is that possible to change a DO structure is ran, to be possible to
> return one or more values ? > It would be like ...

Looks like a function.

> DO returns(ID Integer, Description Text) as
> $$
> begin
>    select ...
>    insert ...
>    select ... into ID, Description
> end
> $$
> 
> Using this way would be possible to create that script on client, call it
> just one time and have a result for that execution, exactly the way a
> "execute block" does on Firebird.

BEGIN;

CREATE FUNCTION some_func() RETURNS ...

SELECT * FROM some_func(); -- Grab the results in the script.

ROLLBACK;

> 
> Is that possible or there is a way to call just one time the server to
> return values without creating a function to each call ?

A DO block is creating a function:

https://www.postgresql.org/docs/10/static/sql-do.html

"DO executes an anonymous code block, or in other words a transient 
anonymous function in a procedural language."


> 
> What do you think change how DO structure is ran to have results from ?
> Version 12, what do you think ?

Basically you are asking for DO to be what does not exist at the moment, 
CREATE TEMPORARY FUNCTION. I would prefer having CREATE TEMPORARY FUNCTION.

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


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: execute block like Firebird does

From

Félix GERZAGUET

Date:

30 May 2018, 19:16:56

Hello,

On Wed, May 30, 2018 at 2:50 PM, PegoraroF10 <marcos@f10.com.br> wrote:

How to send a script to server and return one or more values from that
execution ?

You´ll probably answer me that I could solve that with a function. But
suppose those executions are dynamic, depends on businness rules or any
other problem.

So, is that possible to change a DO structure is ran, to be possible to
return one or more values ?
It would be like ...
DO returns(ID Integer, Description Text) as
$$
begin
select ...
insert ...
select ... into ID, Description
end
$$

Using this way would be possible to create that script on client, call it
just one time and have a result for that execution, exactly the way a
"execute block" does on Firebird.

Is that possible or there is a way to call just one time the server to
return values without creating a function to each call ?

What do you think change how DO structure is ran to have results from ?
Version 12, what do you think ?

Since you seems to be able to construct dynamically the statement from the client application, I think it is already possible to do that in one SQL statement using CTE.

For example:

Assuming we have the follwing schema:

create table t(c1 text, c2 text);

You can then do:

with stmt1 as (
select c1, c2 from t
union all
select 'value1', 'value2'
)
, stmt2 as (
insert into t
select s.c1, s.c2
from stmt1 s
returning c1
)
select *
from stmt2

;

So you can construct arbitrary complex thing using any combination of SELECT, UPDATE and DELETE.

Félix

Re: execute block like Firebird does

From

"Peter J. Holzer"

Date:

31 May 2018, 02:25:04

On 2018-05-30 15:16:56 +0200, Félix GERZAGUET wrote:
> On Wed, May 30, 2018 at 2:50 PM, PegoraroF10 <marcos@f10.com.br> wrote:
>     So, is that possible to change a DO structure is ran, to be possible to
>     return one or more values ?
>     It would be like ...
>     DO returns(ID Integer, Description Text) as
>     $$
>     begin
>       select ...
>       insert ...
>       select ... into ID, Description
>     end
>     $$
>
>     Using this way would be possible to create that script on client, call it
>     just one time and have a result for that execution, exactly the way a
>     "execute block" does on Firebird.
[...]
>
> Since you seems to be able to construct dynamically the statement from the
> client application, I think it is already possible to do that in one SQL
> statement using CTE.
[...]
> So you can construct arbitrary complex thing using any combination of SELECT,
> UPDATE and DELETE.

But note that:

| The sub-statements in WITH are executed concurrently with each other and
| with the main query. Therefore, when using data-modifying statements in
| WITH, the order in which the specified updates actually happen is
| unpredictable. All the statements are executed with the same snapshot
| (see Chapter 13), so they cannot “see” one another's effects on the
| target tables. This alleviates the effects of the unpredictability of
| the actual order of row updates, and means that RETURNING data is the
| only way to communicate changes between different WITH sub-statements
| and the main query.
    -- https://www.postgresql.org/docs/10/static/queries-with.html#QUERIES-WITH-MODIFYING

In a DO block the statements are processed sequentially and each
statement sees the results of the previous statements.

        hp


--
   _  | Peter J. Holzer    | we build much bigger, better disasters now
|_|_) |                    | because we have much more sophisticated
| |   | hjp@hjp.at         | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>

Attachment

signature.asc