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.

Answers


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.


Need Your Help

UITextView Puzzle?

iphone objective-c xcode

In one project XCode4 intellisence feature shows setContentToHTMLString method of UITextView but in other project intellisence feature not showing setContentToHTMLString this method of UITextView.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.