1

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!

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.