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(dot)klaver(at)aklaver(dot)com