return text from explain

correction.
What won't work is:

select y||'--some text'
from
(
explain select *
from (values (1),(2), (3)) foo(x)
where x > 2
) bar(y)

Cheers,

WBL

On Thu, Sep 6, 2012 at 7:18 PM, Willy-Bas Loos <willybas@gmail.com> 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.

Cheers,

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

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

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@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

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@momjian.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@momjian.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

Bruce Momjian <bruce@momjian.us> writes:

On Thu, Sep 6, 2012 at 07:18:50PM +0200, Willy-Bas Loos wrote:

Is it possible to use the output of explain as text values?

I think you have to do EXPLAIN in a function and call the function.

Yeah, IIRC you can use EXPLAIN as the source statement in a plpgsql
FOR loop, ie
FOR text_variable IN EXPLAIN ... LOOP
which gets you the output one line at a time.

regards, tom lane

On Thu, Sep 6, 2012 at 10:15 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Bruce Momjian <bruce@momjian.us> writes:

On Thu, Sep 6, 2012 at 07:18:50PM +0200, Willy-Bas Loos wrote:

Is it possible to use the output of explain as text values?

I think you have to do EXPLAIN in a function and call the function.

Yeah, IIRC you can use EXPLAIN as the source statement in a plpgsql
FOR loop, ie
FOR text_variable IN EXPLAIN ... LOOP
which gets you the output one line at a time.

nope,
ERROR: syntax error at or near "explain"

that's postgres 9.1

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

Hope this helps:

CREATE OR REPLACE FUNCTION explain(in_sql text) RETURNS TABLE(explain_line
text) AS $explain$
BEGIN
RETURN QUERY EXECUTE 'EXPLAIN '||in_sql;
END;
$explain$ LANGUAGE plpgsql;

SELECT * FROM explain('SELECT * FROM pg_locks');

--
Victor Y. Yegorov

cool, it does work with RETURN QUERY.

Thanx!

WBL

On Fri, Sep 7, 2012 at 11:00 AM, Виктор Егоров <vyegorov@gmail.com> wrote:

Hope this helps:

CREATE OR REPLACE FUNCTION explain(in_sql text) RETURNS TABLE(explain_line
text) AS $explain$
BEGIN
RETURN QUERY EXECUTE 'EXPLAIN '||in_sql;
END;
$explain$ LANGUAGE plpgsql;

SELECT * FROM explain('SELECT * FROM pg_locks');

--
Victor Y. Yegorov

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