Обсуждение: return text from explain
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
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
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
--
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth
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
--
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth
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:I think you have to do EXPLAIN in a function and call the function. My
> 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.
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:
BEGIN
RETURN QUERY EXECUTE 'EXPLAIN '||in_sql;
END;
$explain$ LANGUAGE plpgsql;
SELECT * FROM explain('SELECT * FROM pg_locks');
--
Victor Y. Yegorov
Victor Y. Yegorov
cool, it does work with RETURN QUERY.
Thanx!
WBL
--
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth
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$BEGINRETURN 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