Postgresql – How to Export Each Row of a Table into Separate Text Files

pgadminpostgresql

I have a table in my database that contains data with three columns: id, name, and notes. The notes column contains unstructured text data that I want exported for each row as its own text file. This means if I had 5 rows in my table, then I would export 5 .txt files. Here is my attempt so far:

do $$
  declare
    arow record;
    files varchar;
  begin
    for arow in
    select id, name, note from multi_text
    loop
      files := '/tmp/'||arow.id||'_'||arow.name||'.txt';
      COPY (select note from multi_text where id = arow.id) TO files (FORMAT CSV);
    end loop;
  end;
$$;

Currently, there is a ERROR: syntax error at or near "files". I am using PostgreSQL in pgadmin. Any help is much appreciated!

Best Answer

You have to use dynamic sql, so something like this:

do $$
  declare
    arow record;
    files varchar;
  begin
    for arow in
    select id, name, notes from multi_text
    loop
      files := '/tmp/'||arow.id||'_'||arow.name||'.txt';
      execute format('COPY (select notes from multi_text where id = %L) TO %L (FORMAT CSV)',  arow.id,files);
    end loop;
  end;
$$;

It is possible that a maliciously crafted value of arow.name could cause the value in "files" to climb out of the /tmp/ directory and write a file someplace else, which could lead to security breaches. If arow.id is a text type (rather than an integer for example) it would be even easier for a maliciously crafted value of that to do so.

Related Question