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

Question

I am trying to understand the query plan for a select statement within a PL/pgSQL function, but I keep getting errors. My question: how do I get the query plan?

Following is a simple case that reproduces the problem.

The table in question is named test_table.

CREATE TABLE test_table
(
  name character varying,
  id integer
);

The function is as follows:

DROP FUNCTION IF EXISTS test_function_1(INTEGER);
CREATE OR REPLACE FUNCTION test_function_1(inId INTEGER) 
RETURNS TABLE(outName varchar)
AS 
$$
BEGIN
  -- is there a way to get the explain analyze output?
  explain analyze select t.name from test_table t where t.id = inId;

  -- return query select t.name from test_table t where t.id = inId;
END;
$$ LANGUAGE plpgsql;

When I run

select * from test_function_1(10);

I get the error:

ERROR:  query has no destination for result data
CONTEXT:  PL/pgSQL function test_function_1(integer) line 3 at SQL statement

The function works fine if I uncomment the commented portion and comment out explain analyze.

Answer 1

Or you can use this simpler form with RETURN QUERY:

CREATE OR REPLACE FUNCTION f_explain_analyze(int)
  RETURNS SETOF text AS
$func$
BEGIN
   RETURN QUERY
   EXPLAIN ANALYZE SELECT * FROM foo WHERE v = $1;
END
$func$ LANGUAGE plpgsql;

Call:

SELECT * FROM f_explain_analyze(1);

Works for me in Postgres 9.3.

Answer 2

Any query has to have a known target in plpgsql (or you can throw the result away with a PERFORM statement). So you can do:

CREATE OR REPLACE FUNCTION fx(text)
RETURNS void AS $$
DECLARE t text;
BEGIN
  FOR t IN EXPLAIN ANALYZE SELECT * FROM foo WHERE v = $1
  LOOP
    RAISE NOTICE '%', t;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

postgres=# SELECT fx('1');
NOTICE:  Seq Scan on foo  (cost=0.00..1.18 rows=1 width=3) (actual time=0.024..0.024 rows=0 loops=1)
NOTICE:    Filter: ((v)::text = '1'::text)
NOTICE:    Rows Removed by Filter: 14
NOTICE:  Planning time: 0.103 ms
NOTICE:  Total runtime: 0.065 ms
 fx 
────

(1 row)

Another possibility to get the plan for embedded SQL is using a prepared statement:

postgres=# PREPARE xx(text) AS SELECT * FROM foo WHERE v = $1;
PREPARE
Time: 0.810 ms

postgres=# EXPLAIN ANALYZE EXECUTE xx('1');
                                         QUERY PLAN                                          
─────────────────────────────────────────────────────────────────────────────────────────────
 Seq Scan on foo  (cost=0.00..1.18 rows=1 width=3) (actual time=0.030..0.030 rows=0 loops=1)
   Filter: ((v)::text = '1'::text)
   Rows Removed by Filter: 14
 Total runtime: 0.083 ms
(4 rows)