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.

`\copy` is a `psql` only command, **not** a SQL statement - that's the reason you will not find it in the [list of SQL commands](https://www.postgresql.org/docs/current/static/sql-commands.html) - you can't use that inside a PL/pgSQL function — , Dec 29 '16 at 13:47
Thanks for the explanation but copy command won't work because I am not a superuser.So how can I export files in a loop as CSV — John, Dec 30 '16 at 01:58

score 1 · Accepted Answer · answered Dec 29 '16 at 15:18

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

This SQL query gives the below `copy ( select time,t_count,sum(t_count) over (order by time)` in the table 320 rows but no CSV are generated in the destination — John, Dec 30 '16 at 01:51
All the query does is create the query statements you need. You then need to execute them, e.g. by copying and pasting them in PGadmin. — mlinth, Dec 30 '16 at 09:15

how to export results to CSV in a loop

1 Answers1