Re: execute block like Firebird does

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