Re: generic options for explain

Robert Haas <robertmhaas@gmail.com> writes:
> I agree with this, but there is a lot of sentiment (which I share)
> that it should be possible to capture EXPLAIN output using subselect
> or CTAS syntax, regardless of exactly what that output ends up being.

Well, it should be possible to capture the output, but let's not
prejudge the syntax.

> That seems to require that EXPLAIN be a fully-reserved keyword, so I
> wonder what we think about that.

Nonstarter, especially when it's so obviously possible to do it without
that.  The threshold for reserving words that aren't reserved by SQL
spec has to be really high, because you will break innocent applications
that way.

Before anyone gets overly excited about having special syntax for this,
I should point out that you can do it today, for instance like so:

regression=# create function expl(q text) returns setof text as $$
regression$# declare r record;
regression$# begin
regression$#   for r in execute 'explain ' || q loop
regression$#     return next r."QUERY PLAN";
regression$#   end loop;
regression$# end$$ language plpgsql;
CREATE FUNCTION

regression=# select * from expl('select * from tenk1');                           expl                             
-------------------------------------------------------------Seq Scan on tenk1  (cost=0.00..458.00 rows=10000
width=244)
(1 row)

Admittedly this is a bit inconvenient, but the point is that the
functionality does exist.  There is no need to have a built-in
version of this function unless we get significant advantages
from having it built-in, and right now I'm not seeing those.
        regards, tom lane