On Thu, Feb 10, 2005 at 03:38:05PM -0500, Tom Lane wrote:
> Michael Fuhr <mike@fuhr.org> writes:
>
> > Any suggestions for the meantime?
>
> Update to CVS tip ;-)
Done. Below is a simple proof of concept for an explain() function
in PL/pgSQL. It's not necessarily correct -- it just shows what
could be done.
CREATE TYPE explain_in AS (
exp text
);
CREATE TYPE explain_out AS (
pname text,
startup_cost numeric(12,2),
total_cost numeric(12,2),
plan_rows integer,
plan_width integer
);
CREATE FUNCTION explain(query text) RETURNS SETOF explain_out AS $$
DECLARE
row explain_in;
ret explain_out;
BEGIN
FOR row IN EXECUTE 'EXPLAIN ' || query LOOP
ret.pname := substring(row.exp FROM '([^\\(]+) \\(');
ret.startup_cost := substring(row.exp FROM 'cost=(\\d+\\.\\d+)');
ret.total_cost := substring(row.exp FROM '\\.\\.(\\d+\\.\\d+) rows');
ret.plan_rows := substring(row.exp FROM ' rows=([[:digit:]]+)');
ret.plan_width := substring(row.exp FROM ' width=([[:digit:]]+)');
IF ret.plan_rows IS NOT NULL THEN
RETURN NEXT ret;
END IF;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql VOLATILE STRICT;
SELECT * FROM explain('SELECT count(*) FROM pg_class');
pname | startup_cost | total_cost | plan_rows | plan_width
-----------------------------+--------------+------------+-----------+------------
Aggregate | 26.69 | 26.69 | 1 | 0
-> Seq Scan on pg_class | 0.00 | 24.95 | 695 | 0
(2 rows)
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
