How to get EXPLAIN output with a SELECT statement?

Question

I'm trying to grab a query plan for my query. However, I do not have direct access to the db and can only run SELECT statements on my end.

Is there a way to wrap SELECT around EXPLAIN, maybe even encode it as string to generate tabular data to be extracted on my end.

Note: I cannot use psql (the default command line terminal).

Answer 1

Basic EXPLAIN returns a set of text values. But you cannot access that with a plain SELECT. You can EXECUTE EXPLAIN with a given query string dynamically in PL/pgSQL and return the result as SETOF text. You can even pass EXPLAIN options.
Create the function once. Then you can "call" EXPLAIN via SELECT:

CREATE FUNCTION f_explain(_qry text, _options text = NULL)
  RETURNS SETOF text
  LANGUAGE plpgsql AS
$func$
BEGIN
  RETURN QUERY EXECUTE
  format('EXPLAIN %s %s', _options, _qry);
END
$func$;

Example calls:

SELECT * FROM f_explain('SELECT 1');
SELECT * FROM f_explain('SELECT 1', '(ANALYZE, BUFFERS)');

Options, if given, must be in legal syntax form.

fiddle

Careful! With the option ANALYZE, the given query string is actually executed. Also, a maliciously formed query string can do anything the executing role is allowed to do. So this is open to SQL injection. Handle with care!

Related:

• EXPLAIN ANALYZE within PL/pgSQL gives error: "query has no destination for result data"

Alternatively, you can get EXPLAIN output string of messages (NOTICE, WARNING, ...) See:

• Getting the query plan for statements inside a stored procedure in PostgreSQL