On Sunday, July 10, 2011 11:54:10 am Uwe Bartels wrote: > 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 > 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.
I believe you are looking for: explain (ANALYZE, FORMAT YAML) create table...
> > 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
