Re: Any reasons for 'DO' statement not returning result? - Mailing list pgsql-general

On Mon, Aug 8, 2016 at 7:25 PM, Xtra Coder <xtracoder@gmail.com> wrote:
> Hi,
>
> I'm just curious about the reasons of the design of 'DO' statement so that
> it is not able to return result of the SELECT in its body.
>
> References:
>     https://www.postgresql.org/docs/current/static/sql-do.html
>
> http://stackoverflow.com/questions/14652477/how-to-perform-a-select-query-in-a-do-block
>
> With some former experience with MsSQL server, where 'complex' script is
> executed easily and straightforward without any 'wrapping', like this
> dummy-one ...
>
>     DECLARE @a int;
>     DECLARE @b int;
>     ...
>     select @a + @b as "a+b"
>
> ... every time I need to execute some one-time-through-away complex code in
> PostgreSQL which returns rowset I'm disappointed - this has to be wrapped
> into normal 'temp' function which I have to delete all the time in current
> session, thus making an anonymous 'DO' statement use-less in 95% of my
> use-cases.
>
> So ... may someone know good reasons for such inconvenient design of 'DO'
> statement?

IIRC past discussion concluded DO statements should be allowed to
return values.

What you (or at least I-) really want though is stored procedures.  To
me, this means the following:

*) Ability to embed collection of statements in the database under a name
*) Ability to invoke those statements via CALL <name>, which does not
automatically create a transaction and a snapshot (unlike
functions/DO)

I used to think that we needed to pick a procedural language (for
example, pl/pgsql) to leverage the various programming niceties of the
database (such as variables and flow control).  Today I'm thinking it
ought to be vanilla SQL for starters, with some judicious SQL
extensions to be hashed out later.

merlin