How to get results of EXPLAIN of dynamic query maked up in PL/PGSQL function?
I found option 'debug_print_plan', but it produces incomprehensible output. Is there an option to dump execution plan in EXPLAIN format?

EXPLAIN return generic result set in client application, as 'SELECT' command do. But in PL/PGSQL function
FOR rec IN EXPLAIN query_text LOOP ...
and
FOR rec IN EXECUTE('EXPLAIN '||query_text) LOOP ...
both failed with error "cannot open non-SELECT query as cursor".

I can receive EXPLAIN results through
select * from dblink('EXPLAIN '||query_text) (query_plan text)
but it doesn't work if queries use temporary tables.

PL/Perl function spi_exec_query('EXPLAIN select ...') returns no rows and status=SPI_OK_UTILITY (PostgreSQL 8.0.0rc1 on i686-pc-mingw32).

Is it another way?


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

"=?KOI8-R?Q?=E1=CC=C5=CB=D3=C5=CA =FB.?=" <savbr@rin.ru> writes:
> How to get results of EXPLAIN of dynamic query maked up in PL/PGSQL function?

Pretend that it's a prepared statement.

For example, if your plpgsql function has

declare x int;
y int;
begin
...
select f1 into x from tab1 where f2 = y;

then this will show the same plan as plpgsql will be using:

prepare foo(int) as select f1 from tab1 where f2 = $1;

explain analyze execute foo(42);

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org