Search Postgresql Archives

Re: execute block like Firebird does

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

Attachment: signature.asc
Description: PGP signature


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux