Re: [SQL] using explain output within pgsql

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 ....

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
> >
>