Re: Understanding EXPLAIN ANALYZE output

Michael Fuhr <mike(at)fuhr(dot)org> writes:
> I started looking at this earlier. Is it even possible to get
> EXPLAIN output via SPI (and thus the PLs' interfaces to SPI)?

Hmm ... my first reaction was "sure", but:

regression=# create or replace function foo() returns setof text as $$
regression$# declare x record;
regression$# begin
regression$# for x in explain select * from tenk1 where unique1 = 42
regression$# loop
regression$# return next x."QUERY PLAN";
regression$# end loop;
regression$# return;
regression$# end$$ language plpgsql;
CREATE FUNCTION
regression=# select * from foo();
ERROR: cannot open non-SELECT query as cursor
CONTEXT: PL/pgSQL function "foo" line 3 at for over select rows

(And no, EXECUTE doesn't help.) This seems like an oversight. We
already have some understanding in the backend that certain utility
commands return query results; the SPI code should be letting those
results be scanned as if they were ordinary SELECT results.

regards, tom lane