Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id 51DE1B5DBC4
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Sun, 10 Jul 2011 19:48:18 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024)
	with ESMTP id 29130-01; Sun, 10 Jul 2011 22:48:12 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mbx.knossos.net.nz (mbx.knossos.net.nz [202.160.48.10])
	by mail.postgresql.org (Postfix) with ESMTP id 929CCB5D803;
	Sun, 10 Jul 2011 19:48:10 -0300 (ADT)
Received: from [10.1.1.3] (60-234-150-59.bitstream.orcon.net.nz
	[60.234.150.59]) (authenticated bits=0)
	by mbx.knossos.net.nz (8.14.4/8.14.4) with ESMTP id p6AMm3D5030797
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NOT);
	Mon, 11 Jul 2011 10:48:04 +1200
Message-ID: <4E1A2C23.3090800@archidevsys.co.nz>
Date: Mon, 11 Jul 2011 10:48:03 +1200
From: Gavin Flower <GavinFlower@archidevsys.co.nz>
Organization: ArchiDevSys
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.18) Gecko/20110621 Fedora/3.1.11-1.fc14
	Lightning/1.0b3pre Thunderbird/3.1.11
MIME-Version: 1.0
To: Pavel Stehule <pavel.stehule@gmail.com>
CC: Uwe Bartels <uwe.bartels@gmail.com>, pgsql-admin@postgresql.org,
	pgsql-sql@postgresql.org
Subject: Re: [SQL] using explain output within pgsql
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>
In-Reply-To: 
 <CAFj8pRD7uarK50_Y9U=MCZMwF2=a2RQXKD+b=9PGv1PWOOmAsw@mail.gmail.com>
Content-Type: multipart/alternative;
	boundary="------------020409010903030807090503"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 201107/86
X-Sequence-Number: 37865

This is a multi-part message in MIME format.
--------------020409010903030807090503
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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

--------------020409010903030807090503
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <meta content="text/html; charset=UTF-8" http-equiv="Content-Type">
  </head>
  <body bgcolor="#ffffff" text="#000000">
    On 11/07/11 08:18, Pavel Stehule wrote:
    <blockquote
cite="mid:CAFj8pRD7uarK50_Y9U=MCZMwF2=a2RQXKD+b=9PGv1PWOOmAsw@mail.gmail.com"
      type="cite">
      <pre wrap="">2011/7/10 Uwe Bartels <a class="moz-txt-link-rfc2396E" href="mailto:uwe.bartels@gmail.com">&lt;uwe.bartels@gmail.com&gt;</a>:
</pre>
      <blockquote type="cite">
        <pre wrap="">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 ....

</pre>
      </blockquote>
      <pre wrap="">
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</pre>
    </blockquote>
    [...]<br>
    <br>
    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.<br>
    <br>
    I think using variables makes the use of 'execute' more
    understandable.<br>
    <br>
    I hope this version is of value to to others, I have included all
    the code required to run it as a working example.<br>
    <br>
    <font face="Courier New, Courier, monospace">CREATE TABLE data<br>
      (<br>
          id      int,<br>
          value   text<br>
      );<br>
      <br>
      INSERT INTO <br>
          data (id, value)<br>
      VALUES<br>
          (1, 'a'),<br>
          (2, 'b');<br>
      <br>
      do $$<br>
      declare<br>
          v_sql_query    text;<br>
          v_sql_explain  text;<br>
          v_result       text;<br>
      begin<br>
      v_sql_query :=e'SELECT * FROM data d WHERE value = \'a\'';<br>
      v_sql_explain :=e'EXPLAIN(FORMAT YAML) ' || v_sql_query;<br>
      execute v_sql_explain into v_result;<br>
      raise notice 'v_result: %', v_result;<br>
      end;<br>
      $$ language plpgsql;</font><br>
    <br>
    <br>
    Cheers,<br>
    Gavin<br>
  </body>
</html>

--------------020409010903030807090503--