In 7.2 and before it would work to do EXPLAIN in a plpgsql function:

regression=# create function foo(int) returns int as '
regression'# begin
regression'# explain select * from tenk1 where unique1  = $1;
regression'# return 1;
regression'# end;' language plpgsql;
CREATE
regression=# select foo(1);
NOTICE:  QUERY PLAN:

Index Scan using tenk1_unique1 on tenk1  (cost=0.00..6.00 rows=1 width=148)

 foo
-----
   1
(1 row)

which was useful for examining the behavior of the planner with
parameterized queries.

In current CVS tip this doesn't work anymore --- the EXPLAIN executes
just fine, but plpgsql discards the result, and you never get to see it.

Not sure what to do about this.  Probably plpgsql should be tweaked to
do something with EXPLAIN, but what?  Should it treat it like a SELECT?
Or just issue the output as a NOTICE (seems like a step backwards
though).

I'm also strongly tempted to try to make the SQL-language equivalent work:

regression=# create function foo(int) returns setof text as
regression-# 'explain select * from tenk1 where unique1  = $1;'
regression-# language sql;
ERROR:  function declared to return text, but final statement is not a SELECT

			regards, tom lane

Wed, 08 May 2002 13:00:07 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Bad side-effect from making EXPLAIN return a select result
Tom Lane wrote:
 > which was useful for examining the behavior of the planner with
 > parameterized queries.
 >
 > In current CVS tip this doesn't work anymore --- the EXPLAIN
 > executes just fine, but plpgsql discards the result, and you never
 > get to see it.
 >
 > Not sure what to do about this.  Probably plpgsql should be tweaked
 > to do something with EXPLAIN, but what?  Should it treat it like a
 > SELECT? Or just issue the output as a NOTICE (seems like a step
 > backwards though).
 >
 > I'm also strongly tempted to try to make the SQL-language equivalent
 > work:
 >
 > regression=# create function foo(int) returns setof text as regression-#
 > 'explain select * from tenk1 where unique1  = $1;' regression-#
 > language sql; ERROR:  function declared to return text, but final
 > statement is not a SELECT

If EXPLAIN was treated as a select, and modified to use the
ExprMultipleResult API, then the SRF stuff would allow you to get output
from a SQL function (for that matter a SQL function could do it today
since it's only one result column).

PLpgSQL currently doesn't seem to have a way to return setof anything
(although it can be defined to), but I was planning to look at that
after finishing SRFs. E.g.

CREATE TABLE foo (fooid int, foosubid int, fooname text, primary
key(fooid,foosubid));
INSERT INTO foo VALUES(1,1,'Joe');
INSERT INTO foo VALUES(1,2,'Ed');
INSERT INTO foo VALUES(2,1,'Mary');
CREATE OR REPLACE FUNCTION testplpgsql() RETURNS setof int AS 'DECLARE
fooint int; BEGIN SELECT fooid into fooint FROM foo; RETURN fooint;
END;' LANGUAGE 'plpgsql';

test=# select testplpgsql();	<== old style API
Cancel request sent	<== seems to hang, never returns anything, ctl-c
WARNING:  Error occurred while executing PL/pgSQL function testplpgsql
WARNING:  line 1 at select into variables
ERROR:  Query was cancelled.
test=#

This never even returns the first row. Am I missing something on this,
or did plpgsql never support setof results? If so, how?

Joe



Wed, 08 May 2002 18:27:06 -0700
Joe Conway <mail@joeconway.com>
Re: Bad side-effect from making EXPLAIN return a select