execute block like Firebird does

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



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

Re: execute block like Firebird does

[Andreas Kretschmer]

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

[Fabrízio de Royes Mello]

Em dom, 11 de fev de 2018 às 06:47, Andreas Kretschmer <
andr...@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

[PegoraroF10]

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

[Pavel Stehule]

2018-02-11 14:50 GMT+01:00 PegoraroF10 :

> 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

[Steven Lembark]

> > 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
lemb...@wrkhors.com+1 888 359 3508

Re: execute block like Firebird does

[Fabrízio de Royes Mello]

Em dom, 11 de fev de 2018 às 13:54, Steven Lembark 
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

[Thiemo Kellner]

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

Re: execute block like Firebird does

[PegoraroF10]

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

[Adrian Klaver]

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

Re: execute block like Firebird does

[David G. Johnston]

On Mon, Feb 12, 2018 at 6:48 AM, 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 ?


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

[PegoraroF10]

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

[Adrian Klaver]

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

Re: execute block like Firebird does

[Daniel Verite]

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

[PegoraroF10]

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 t1;
  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,
  Cursotype of column col_Curso.Descricao,
  Turmatype of column col_Turma.Nome,
  IniciandoLogico,
  PeriodoSequencia I32,
  Periodo  T50,
  OrdemI32,
  DescricaoSemana  varchar(15),
  SemanaInicio type of column col_Aula.Data,
  SemanaFimtype 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 II32;
  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 HoraFinalVarChar(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 begi

Re: execute block like Firebird does

[David G. Johnston]

On Monday, February 12, 2018, 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.
>

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

[Adrian Klaver]

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 t1;
   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,
   Cursotype of column col_Curso.Descricao,
   Turmatype of column col_Turma.Nome,
   IniciandoLogico,
   PeriodoSequencia I32,
   Periodo  T50,
   OrdemI32,
   DescricaoSemana  varchar(15),
   SemanaInicio type of column col_Aula.Data,
   SemanaFimtype 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 II32;
   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 HoraFinalVarChar(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,

Re: execute block like Firebird does

[PegoraroF10]

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

[Edson Carlos Ericksson Richter]

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

[PegoraroF10]

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

[Adrian Klaver]

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

Re: execute block like Firebird does

[Félix GERZAGUET]

Hello,

On Wed, May 30, 2018 at 2:50 PM, PegoraroF10  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

[Peter J. Holzer]

On 2018-05-30 15:16:56 +0200, Félix GERZAGUET wrote:
> On Wed, May 30, 2018 at 2:50 PM, PegoraroF10  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
| |   | h...@hjp.at | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson 


signature.asc
Description: PGP signature