Hi Pavel, I don't believe it. the second answer on sunday evening within half an hour.
and it works. excellent!!! best regards, Uwe On 10 July 2011 21:20, Pavel Stehule <pavel.steh...@gmail.com> wrote: > Hello > > > > 2011/7/10 Uwe Bartels <uwe.bart...@gmail.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 > > >
