Re: return text from explain

Buce, thx for answering.
I cant't find the example you mean.
Tried a function, but won't work..

create or replace function test() returns setof record as $$
declare
t_rec record;
begin
for t_rec in (
explain
select *
from (values ('a'),('b'), ('c')) foo(x)
where x > 'a'
) loop
return next t_rec;
end loop;
end;
$$ language plpgsql;

select *
from test() as (x text)

ERROR: syntax error at or near "explain"
LINE 6: explain

When i comment-out the "explain", then it works.
You can also run the query from "explain" to "x > 'a'".

Cheers,

WBL

On Thu, Sep 6, 2012 at 8:03 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> On Thu, Sep 6, 2012 at 07:18:50PM +0200, Willy-Bas Loos wrote:
> > Hi,
> >
> > Is it possible to use the output of explain as text values?
> > This won't work:
> >
> > explain select *
> > from (values (1),(2),(3)) foo(x)
> > where x > 2
> >
> > What i really want is to explain analyze a dynamic query that i build up
> in a
> > function.
> > If it returns a value i can do stuff with it, but i can't find out how
> to grasp
> > the query plan as a value.
> >
> > pgAdmin shows it as text values in the data output tab, but that might
> be a
> > hack outside the database realm.
>
> I think you have to do EXPLAIN in a function and call the function. My
> CTE presentation has an example of that:
>
> http://momjian.us/main/presentations/features.html#cte
>
> --
> Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> + It's impossible for everything to be true. +
>

--
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth