Re: execute block like Firebird does

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

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

Em dom, 11 de fev de 2018 às 06:47, Andreas Kretschmer <
andreas(at)a-kretschmer(dot)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

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

2018-02-11 14:50 GMT+01:00 PegoraroF10 <marcos(at)f10(dot)com(dot)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
>
>

> > 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(at)wrkhors(dot)com +1 888 359 3508

Em dom, 11 de fev de 2018 às 13:54, Steven Lembark <lembark(at)wrkhors(dot)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

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

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

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

On Mon, Feb 12, 2018 at 6:48 AM, PegoraroF10 <marcos(at)f10(dot)com(dot)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.​

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

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

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

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 &#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

On Monday, February 12, 2018, PegoraroF10 <marcos(at)f10(dot)com(dot)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.

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

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

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
>
>

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

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

Hello,

On Wed, May 30, 2018 at 2:50 PM, PegoraroF10 <marcos(at)f10(dot)com(dot)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

On 2018-05-30 15:16:56 +0200, Félix GERZAGUET wrote:
> On Wed, May 30, 2018 at 2:50 PM, PegoraroF10 <marcos(at)f10(dot)com(dot)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(at)hjp(dot)at | management tools.
__/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>