Re: execute block like Firebird does

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