Bad side-effect from making EXPLAIN return a select result

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: pgsql-hackers(at)postgreSQL(dot)org
Subject: Bad side-effect from making EXPLAIN return a select result
Date: 2002-05-08 17:00:07
Message-ID: 3343.1020877207@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Scott Marlowe 2002-05-08 17:21:49 Re: postgresql 7.1 file descriptor
Previous Message Zeugswetter Andreas SB SD 2002-05-08 16:47:46 Re: non-standard escapes in string literals