Thread: Get execution plan of dynamic query

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
inEXPLAIN 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?

"=?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