From: Gavin Flower <GavinFlower@archidevsys.co.nz>
To: Pavel Stehule <pavel.stehule@gmail.com>
Cc: Uwe Bartels <uwe.bartels@gmail.com>
Cc: pgsql-admin@postgresql.org
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] using explain output within pgsql
Date: Mon, 11 Jul 2011 10:48:03 +1200
Message-ID: <4E1A2C23.3090800@archidevsys.co.nz> (raw)
In-Reply-To: <CAFj8pRD7uarK50_Y9U=MCZMwF2=a2RQXKD+b=9PGv1PWOOmAsw@mail.gmail.com>
References: <CAPGEe=46ckcX+BCRnO7ayNf0abZQHe0i62_QLCAGj7oq0hKUzA@mail.gmail.com>
<CAFj8pRCks_KympRa1nCMQrGN3+L5aH03rg_ozZe_v5Enp6R4xA@mail.gmail.com>
<CAPGEe=4SYw0yXx0ujiQ3FT=3Gk1frXUXEPBzsWw34Bf2y6ymiA@mail.gmail.com>
<CAFj8pRD7uarK50_Y9U=MCZMwF2=a2RQXKD+b=9PGv1PWOOmAsw@mail.gmail.com>
On 11/07/11 08:18, Pavel Stehule wrote:
> 2011/7/10 Uwe Bartels<uwe.bartels@gmail.com>:
>> Hi Pavel,
>>
>> is it posible to get this running even with dynamic sql?
>> I didn't write that. I'm using execute to run this create table ....
>>
> probably yes
>
> postgres=# do $$
> declare x text;
> begin
> execute e'explain(format yaml) select * from data where value = \'a\'' into x;
> raise notice '%', x;
> end;
> $$ language plpgsql;
> NOTICE: - Plan:
> Node Type: "Seq Scan"
> Relation Name: "data"
> Alias: "data"
> Startup Cost: 0.00
> Total Cost: 23.38
> Plan Rows: 5
> Plan Width: 46
> Filter: "((value)::text = 'a'::text)"
> DO
[...]
I find that I understand things better if I rephrase things, so I took
Pavel's code and converted it to use variables so I could see more
clearly what is happening.
I think using variables makes the use of 'execute' more understandable.
I hope this version is of value to to others, I have included all the
code required to run it as a working example.
CREATE TABLE data
(
id int,
value text
);
INSERT INTO
data (id, value)
VALUES
(1, 'a'),
(2, 'b');
do $$
declare
v_sql_query text;
v_sql_explain text;
v_result text;
begin
v_sql_query :=e'SELECT * FROM data d WHERE value = \'a\'';
v_sql_explain :=e'EXPLAIN(FORMAT YAML) ' || v_sql_query;
execute v_sql_explain into v_result;
raise notice 'v_result: %', v_result;
end;
$$ language plpgsql;
Cheers,
Gavin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: GavinFlower@archidevsys.co.nz, pavel.stehule@gmail.com, uwe.bartels@gmail.com, pgsql-admin@postgresql.org
Subject: Re: [SQL] using explain output within pgsql
In-Reply-To: <4E1A2C23.3090800@archidevsys.co.nz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox