Postgresql: Export to csv data from EXECUTE output

Question

I am trying to export some aggregated data in a csv file. I want to use postgresql's command COPY, like in the code below, but i am getting the data from an EXECUTE command that will return a dynamic table. The COPY command only takes data from a table, but i cannot save the result in a temp table because i don't know the number of columns that will be generated from executing the query. Also, I am trying to save large data set and i would avoid duplicating it in intermediary tables. Does anybody know any workaround for this?

CREATE OR REPLACE FUNCTION ExportSnapshotToCsv(qe TEXT)
 RETURNS void AS $$
BEGIN
 COPY (Execute qe) To '/tmp/test.csv' With CSV DELIMITER ',';
END; $$
LANGUAGE plpgsql;

I do believe that you might be able to save your data from the EXECUTE command into a temp table, like [here](https://stackoverflow.com/questions/29964708/select-into-temp-table-in-postgresql) and then maybe use that temp table in COPY? — Radu Gheorghiu, Apr 12 '19 at 16:44

score 1 · Accepted Answer · answered Apr 12 '19 at 16:45

1

Use EXECUTE format

DO
$$
DECLARE
   myfile text := '/tmp/test.csv';
BEGIN

      EXECUTE format('COPY (
         SELECT * from %I
         ) TO ''%s'';'
       , 'employees',myfile);
 END 
$$;

answered Apr 12 '19 at 16:45

Kaushik Nayak

30,772
5
32
45

Awesome! This worked. I used it like ```sql EXECUTE format('COPY (%s) TO ''/tmp/test.csv'';', qe); ``` – Lala Apr 12 '19 at 16:59

Postgresql: Export to csv data from EXECUTE output

1 Answers1