Hello,
On Wed, May 30, 2018 at 2:50 PM, PegoraroF10 <marcos@xxxxxxxxxx> 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
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
