Re: execute block like Firebird does

On 05/30/2018 05:50 AM, PegoraroF10 wrote:
> Some time ago I´ve posted this thread because we had lots of these execute
> blocks to be translated to Postgres. Now, continuing on same matter, I would
> like to discuss the same topic, basically calling the server one time only,
> instead of several times.
> Usually we want get some values from server and then insert or update some
> records based on that returned values. Each of these calls will spend time
> and this is the point I would like to discuss.
> 
> 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.

Which can be done in a function.

> 
> So, is that possible to change a DO structure is ran, to be possible to
> return one or more values ? > It would be like ...

Looks like a function.

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

BEGIN;

CREATE FUNCTION some_func() RETURNS ...

SELECT * FROM some_func(); -- Grab the results in the script.

ROLLBACK;

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

A DO block is creating a function:

https://www.postgresql.org/docs/10/static/sql-do.html

"DO executes an anonymous code block, or in other words a transient 
anonymous function in a procedural language."


> 
> What do you think change how DO structure is ran to have results from ?
> Version 12, what do you think ?

Basically you are asking for DO to be what does not exist at the moment, 
CREATE TEMPORARY FUNCTION. I would prefer having CREATE TEMPORARY FUNCTION.

> 
> 
> 
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com