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