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 and then maybe use that temp table in COPY? — Radu Gheorghiu
– Radu Gheorghiu, Commented Apr 12, 2019 at 16:44

Kaushik Nayak · Accepted Answer · 2019-04-12 16:45:18Z

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, 2019 at 16:45

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lala Over a year ago

Awesome! This worked. I used it like sql EXECUTE format('COPY (%s) TO ''/tmp/test.csv'';', qe);

Collectives™ on Stack Overflow

Postgresql: Export to csv data from EXECUTE output

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related