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).
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.
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:
Alternatively, you can get EXPLAIN output string of messages (NOTICE, WARNING, ...) See:
