Thread: Bad side-effect from making EXPLAIN return a select result

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

Tom Lane wrote:> which was useful for examining the behavior of the planner with> parameterized queries.>> In current
CVStip this doesn't work anymore --- the EXPLAIN> executes just fine, but plpgsql discards the result, and you never>
getto 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'malso strongly tempted to try to make the SQL-language equivalent> work:>> regression=# create function foo(int)
returnssetof 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