Re: execute block like Firebird does

Hello,

On Wed, May 30, 2018 at 2:50 PM, PegoraroF10 <marcos(at)f10(dot)com(dot)br> 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