how to export results to CSV in a loop

Question

I have a SQL query

       select time,t_count,sum(t_count) over (order by time) as
       cumulative_t_count ,cumulative_t_count/sum(t_count) as percentage_cumulative count,state,hour from (select distinct
       time,count(distinct num_id) as t_count,state,hour from (select * from
       public.table1 where day=1 and time>0 order by time)as A group by
       time,hour,state order by hour,state )B where state=1 and hour=1; 

which gives me a results in the below format:

time  t_count  cumulative_t_count  state  hour
_____ _____  _________________     ____  ____
1      10          10               1    1
2      20          30               1    1
3      30          60               1    1
4      60          120              1    1

Likewise I have 80 state and hour from 0-23.I want to export all the results to CSV files by changing the state from 1-80 and for the hours 1,7,14,19.So now I am manually changing the state and hour in the above code and exporting the results as CSV.But it seems I have to change state 1 to 80 and hours 1,7,14,19 for each state.

BEGIN
FOR i IN 1..80 LOOP


  FOR i IN 1,7,14,19 LOOP
      /copy ( select time,t_count,sum(t_count) over (order by time) as
       cumulative_t_count ,cumulative_t_count/sum(t_count) as     percentage_cumulative count,state,hour from (select distinct
       time,count(distinct num_id) as t_count,state,hour from (select * from
       public.table1 where day=1 and time>0 order by time)as A group by
       time,hour,state order by hour,state )B where state=i and hour=j)

     To '/tmp/state_i_hour_j.csv' With CSV
 END LOOP

END LOOP

But this did not work.I also want to export each result set in CSV format.Any help is appreciated.

Answer 1

If you only need to do this the once, you can use the following to create the sql statements you need. You can then copy the output records and execute them.

WITH states AS
(SELECT generate_series(1,80) as i),
hours AS
(SELECT j FROM (values (1),(14),(17),(19) ) s(j))
SELECT 'copy ( select time,t_count,sum(t_count) over (order by time) as
       cumulative_t_count ,cumulative_t_count/sum(t_count) as     percentage_cumulative count,state,hour from (select distinct
       time,count(distinct num_id) as t_count,state,hour from (select * from
       public.table1 where day=1 and time>0 order by time)as A group by
       time,hour,state order by hour,state )B where state=' ||i || ' and hour=' ||j || ')

     To ''/tmp/state_' ||i || '_hour_' ||j || '.csv'' With CSV;' from states,hours