Search code examples
sqlpostgresqlpostgresql-copy

Postgresql: Export to csv data from EXECUTE output

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;
Solution

  • Use EXECUTE format

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

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