Re: [SQL] using explain output within pgsql

2011/7/10 Uwe Bartels <uwe(dot)bartels(at)gmail(dot)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

> best regards,
> Uwe
>
> On 10 July 2011 21:20, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>>
>> Hello
>>
>>
>>
>> 2011/7/10 Uwe Bartels <uwe(dot)bartels(at)gmail(dot)com>:
>> > Hi,
>> >
>> > I'm starting up a datawarehouse with patitioning.
>> > my etl processes write directly into the corresponding partitions
>> > instead of
>> > using triggers.
>> >
>> > The reports I run in the datawarehouse are stored in a cache within the
>> > same
>> > database.
>> > Now I'd like to store besides the results the dependencies to the tables
>> > which were used to generate the report. with this information i could
>> > invalidate cache results for the tables I'm going to import with my etl
>> Hello
>>
>> try
>>
>> FOR l_explain IN EXPLAIN ANALYZE ...
>> LOOP
>>   ...
>>
>> Regards
>>
>> Pavel Stehule
>>
>> > processes.
>> >
>> > explain analyze gives me the information which table or patition is read
>> > from for each report. e.g
>> > explain analyze (FORMAT YAML) create table cache.report234 as select
>> > col1,col2,sum(clicks) from dwh.event_log_weekly where week >=
>> > '2011-06-27'
>> > and week <= '2011-07-11' group by col1,col2;
>> >
>> > now I'd like to store the output of explain analyze in a pgsql variable
>> > for
>> > further processing. that looks something like this.
>> >
>> > DO $$declare l_explain text;
>> > begin
>> > l_explain := explain analyze (FORMAT YAML) create table cache.report234
>> > as
>> > select col1,col2,sum(clicks) from dwh.event_log_weekly where week >=
>> > '2011-06-27' and week <= '2011-07-11' group by col1,col2;
>> > select l_explain;
>> > end$$;
>> >
>> > But that doesn't work. I get a syntax error.
>> >
>> > Does anybody has an idea how to retrieve the output of explain within
>> > pgsql
>> > and store this in a variable?
>> > An alternative would be any other way to extract the information about
>> > tables used by arbitrary sql statements.
>> >
>> > best regards,
>> > Uwe
>> >
>
>