PostgreSQL – put the explain result in the table?

SELECT maxcost FROM (
 EXPLAIN VERBOSE 
 SELECT COUNT(*) 
 FROM Mytable
 WHERE value>17);< /pre> 
The above content cannot be used in any form I have tried, and I wrote the attribute maxcost to demonstrate how concise it is to extract specific bits of data (in this case, the maximum estimated cost of the query). I can Is there anything to make me a part of there? I would rather be able to work in a simple SQL console.
 

CREATE OR REPLACE FUNCTION estimate_cost(IN query text, 
OUT startup numeric,
OUT totalcost numeric, 
OUT planrows numeric , 
OUT planwidth numeric)
AS
$BODY$
DECLARE
 query_explain text;
 explanation xml;
 nsarray text[][] ;
BEGIN
nsarray := ARRAY[ARRAY['x','http://www.postgresql.org/2009/explain']];
query_explain :=e'EXPLAIN( FORMAT XML) '|| query;
EXECUTE query_explain INTO explanation;
startup := (xpath('/x:explain/x:Query/x:Plan/x:Startup-Cost/text() ', explanation, nsarray))[1];
totalcost := (xpath('/x:explain/x:Query/x:Plan/x:Total-Cost/text()', explanation, nsarray) )[1];
planrows := (xpath('/x:explain/x:Query/x:Plan/x:Plan-Rows/text()', explanation, nsarray))[1];< br / >planwidth := (xpath('/x:explain/x:Query/x:Plan/x:Plan-Width/text()', explanation, nsarray))[1];
RETURN;
END;
$BODY$
LANGUAGE plpgsql;

SELECT totalcost
FROM estimate_cost('SELECT COUNT(*) 
 FROM Mytable
 WHERE value>17');

I see from Postgres 8.1 docs EXPLAIN generated data similar to the table:

Prior to PostgreSQL 7.3, the plan was emitted in the form of a NOTICE
message. Now it appears as a query result (formatted like a table with
a single text column).

I am using 9.0, for this, the docs say that the output can be of various types, including TEXT and XML. What I really want to do is treat the output as a standard query result in order to generate a simple report for a query or a set of queries, for example,

SELECT maxcost FROM ( 
 EXPLAIN VERBOSE 
 SELECT COUNT(*) 
 FROM Mytable
 WHERE value>17);

The above content cannot be used in any form I have tried , And I wrote the attribute maxcost to demonstrate how concise it is to extract a specific bit of data (in this case, the maximum estimated cost of the query). Is there anything I can do to make me a part of it? I would rather be able to work in a simple SQL console.

There are no other answers so far, so this is my own effort.

< /p>

The interpretation results can be read into variables in plpgsql, and because the output can be XML, EXPLAIN can be wrapped in a storage function to generate top-level costs using xpath:

CREATE OR REPLACE FUNCTION estimate_cost(IN query text, 
OUT startup numeric,
OUT totalcost numeric, 
OUT planrows numeric, 
OUT planwidth numeric)
AS
$BODY$
DECLARE
 query_explain text;
 explanation xml;
 nsarray text[][];
BEGIN
nsarray: = ARRAY[ARRAY['x','http://www.postgresql.org/2009/explain']];
query_explain :=e'EXPLAIN(FORMAT XML) '|| query;
 EXECUTE query_explain INTO explanation;
startup := (xpath('/x:explain/x:Query/x:Plan/x:Startup-Cost/text()', explanation, nsarray))[1];< br />totalcost := (xpath('/x:explain/x:Query/x:Plan/x:Total-Cost/text()', explanation, nsarray))[1];
planrows := (xpath('/x:explain/x:Query/x:Plan/x:Plan-Rows/text()', explanation, nsarray))[1];
planwidth := (xpath('/x :explain/x:Query/x:Plan/x:Plan -Width/text()', explanation, nsarray))[1];
RETURN;
END;
$BODY$
LANGUAGE plpgsql;

Therefore, the example of the question becomes:

SELECT totalcost
FROM estimate_cost('SELECT COUNT(*) 
 FROM Mytable
 WHERE value> 17');