How to use variable value inside copy function

Question

I want to send data to .csv file using COPY function in Postgres. But I am using anonymous block so my table name should be value of variable.

COPY (SELECT cname.portal from user) To '/tmp/out.csv' With CSV;

Where cname is my variable inside anonymous block. I try to use EXECUTE format but it also didn't work. I tried something like:

EXECUTE format ('    COPY (select * from %s.portal,cname ) To '/tmp/out1.csv' With CSV');

Answer 1

In plpgsql code (not in plain SQL!):

EXECUTE format($$COPY (select * from %I.portal) TO '/tmp/out1.csv' WITH CSV$$, cname);

cname being the variable holding a (yet unescaped) schema name.

You need to understand:

• dollar quoting
• the format() function
• SQL injection and how to defend against it:
  • SQL injection in Postgres functions vs prepared queries

If you are processing this with a client where $ has a special meaning, you need to escape it properly. As a quick fix, you can switch to:

EXECUTE format('COPY (select * from %I.portal) TO ''/tmp/out1.csv'' WITH CSV', cname);