Search code examples
postgresqlescapingprocedure

Dynamic SQL fails on escape char

I am trying to write a procedure that loads a csv into a table. I create a dynamic sql and execute it.

v_sql = '\copy test_table from' || ' '''  || p_temp_dir || ''' '  || ' with csv header;';

 --insert into test
 --select v_sql;

EXECUTE v_sql;

When I check the generated string it is fine and I can run it properly, but if I run it from a procedure, I get an error:

syntax error at or near "\"

If I remove the \ character it works (throws an error that there is no such file; this job is intended to work on the local machine). How can I execute this SQL?

Solution

  • \copy is a psql command, not an SQL statement.

    You can only execute SQL statements with PL/pgSQL's EXECUTE statement.

    You cannot import a file from the client machine in a PostgreSQL function that runs on the server. You will have to write client code that does COPY ... FROM STDIN.