Re: Bad side-effect from making EXPLAIN return a select

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