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 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