Re: Any reasons for 'DO' statement not returning result?

On Tue, Aug 16, 2016 at 3:11 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

> On Sun, Aug 14, 2016 at 5:58 AM, Chris Travers <chris(dot)travers(at)gmail(dot)com>
> wrote:
>
> >>
> >> All this seems to be a huge change which will definitely not appear any
> >> time soon.
> >
> > I am willing to bet that DO $$ $$; blocks are neither planned nor
> > parameterized. And the planner needs to know what is to be returned.
>
> The statements within a do block are absolutely planned and
> parameterized. There was some recent discussion with respect to not
> planning statements except under certain conditions (in a loop
> basically) to reduce memory consumption of long 'do' blocks.
>

Right. Which accounts for why people would expect to return results. But I
would bet the block itself is not

I mean when you run a function it is handed off to the language handler,
and that is totally planner opaque (I would expect that to be the case here
too). That function may make queries, and those may be planned but because
the functions can be in arbitrary languages, the planner cannot have
internal knowledge of the functions or do blocks.

And since the planner usually needs to know what is returned in order to
plan things like sorts, you'd have to be able to do something like:

do language plpgsql returning table (foo int, bar text) as
$$
--- insert logic here.
$$;

But at that point how much are you really saving over

CREATE FUNCTION pg_temp.temptest() returns table (foo int, bar text)
language plgsql as $$
-- insert logic here
$$; select pg_temp.temptest();

I am thinking adding a temporary keyword to functions would make a lot more
sense.

> merlin
>

--
Best Wishes,
Chris Travers

Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor
lock-in.
http://www.efficito.com/learn_more