Re: Is it possible to store the output of EXPLAIN into a table

Souvik Bhattacherjee schrieb am 22.04.2019 um 17:27:
> Hi,
>
> I was just wondering if it is possible to store the output of EXPLAIN into a table.
>
> create temp table mytab as (select * from (explain select * from table1 where attr = 5) t);
>
>
> Unfortunately, the query above does not work.

You can't do that directly. You need to write a function that wraps the explain, and returns the plan as a result set.

Something like:

create or replace function show_plan(to_explain text)
   returns table (line_nr integer, line text)
as
$$
declare
   l_plan_line record;
   l_line integer;
begin
    l_line := 1;
    for l_plan_line in execute 'explain (analyze, verbose, format xml)'||to_explain loop
       return query select l_line, l_plan_line."QUERY PLAN"::text;
       l_line := l_line + 1;
    end loop;
end;
$$
language plpgsql;

The you can use:

create temp table mytab as
select *
from show_plan('select * from table1 where attr = 5');