Skip to content
Advertisement

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

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.

Advertisement

Answer

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.