Store XML explain plan in PostgreSQL database

Question

How can I store XML explain plan (or any other format) in PostgreSQL database?

Test data: explain (verbose, format xml) select 1

Table to store results: create table tmp.explain_plan_data (expp xml);

My naive test failed:

insert into tmp.explain_plan_data values (explain (verbose, format xml) select 1);

It seems that explain cannot be used in any other select statement, the following statement does not work either:

select * from (explain (verbose, format xml) select 1) a

We are using PostreSQL 9.1.6

Answer 1

It is not possible to capture EXPLAIN output using subqueries, you should go for a PL/pgSQL procedure:

CREATE OR REPLACE FUNCTION explain_v_xml(in_sql text)
RETURNS TABLE(explain_line xml) AS $explain_v_xml$
BEGIN
    RETURN QUERY EXECUTE 'EXPLAIN (VERBOSE, FORMAT xml) '||in_sql;
END;
$explain_v_xml$ LANGUAGE plpgsql;

Now you can query it like this:

SELECT explain_line FROM explain_v_xml('SELECT * FROM pg_locks');

And insert into your target table:

INSERT INTO tmp.explain_plan_data SELECT explain_v_xml('SELECT 1');
SELECT * FROM tmp.explain_plan_data;

Perhaps explain output alone in the table is not so handy, I would rather added original query and timestamptz of the insert.