How to output "explain analyze" result to a table column?

Question

I have my_table with two text columns: my_query, which contains various DML queries in text form; and and my_explanation, which is entirely populated with nulls.

I'd like to do something like:

    UPDATE my_table SET my_explanation = execute explain analyze my_table.my_query

So that the my_explanation column on each row is updated to contain the textual output of the explain analyze of the query contained in its my_query column.

I tried making a function with cursors but it didn't work. I'm still learning pgplsql. Any ideas?

score 3 · Answer 1 · answered Aug 09 '17 at 23:04

EXECUTE is a PL/pgSQL statement; it can't be used in an SQL query, only in a LANGUAGE plpgsql function or a DO block.

You probably want a function like this:

CREATE FUNCTION explain_query(sql TEXT) RETURNS SETOF TEXT AS $$
BEGIN
  RETURN QUERY EXECUTE 'EXPLAIN ANALYZE ' || sql;
END
$$
LANGUAGE plpgsql;

Because EXPLAIN returns multiple rows, you'll need to aggregate the output to fit it into a single record in my_table:

UPDATE my_table
SET my_explanation = (
  SELECT string_agg(plan, E'\n')
  FROM explain_query(my_query) plan
)

How to output "explain analyze" result to a table column?

1 Answers1