On 11/07/11 08:18, Pavel Stehule wrote:
2011/7/10 Uwe Bartels<uwe.bart...@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
