Why doesn't \COPY work when called from a PL/pqSQL function? I am trying to use the \COPY function to fill the database from text files on the client side. I am using the Postgres interactive terminal (psql) to do this task.

I wrote the following script and saved it to test.sql:

CREATE FUNCTION copy_stuff_to_db(path text, encoding text) RETURNS void
AS $$
BEGIN
    \COPY table1 FROM path || 'table1.asc' WITH ENCODING encoding;
END;
$$ LANGUAGE plpgsql;

PERFORM copy_stuff_to_db('/home/martin', 'ISO 8859-1')


I execute it like this:

psql --port 5432 --host localhost -d mydb --username postgres -f test.sql 


But I get an error:

psql:test.sql:12: ERROR:  syntax error at or near "\" 
LINE 4: \COPY table1 FROM path || 'table1.asc' WITH DE...
        ^


At first I thought that the terminal doesn't do PL/pgSQL and that I will have to put my files and run the script serverside. However, when I call the function directly (i.e. I modify the previous script to):

\COPY table1 FROM '/home/martin/table1.asc' WITH ENCODING 'ISO 8859-1';


Then everything works. It seems that the \COPY function does not work when called from another function for some reason.

What am I doing wrong?
TLDR: \copy is the psql instruction, not a PL/pgSQL command. It cannot be interpreted by the server, only by the psql client itself.



The psql client, as any other database client software, has very limited capabilities. It cannot by itself read or write table rows (because they are physically located at the database server), execute stored procedures and functions (because, again, they reside on the server and execute on the server) etc. All it can do is send an SQL command to the server and present its result to the user.

As an example, when you type select foo from bar in psql and press Enter, the client opens a cursor for the given query text, lets the server process the query, fetches the result set, and shows it to you formatted in some way.

Going back to \copy, here's what documentation has to say:


  Do not confuse COPY with the psql instruction \copy. \copy invokes COPY FROM STDIN or COPY TO STDOUT, and then fetches/stores the data in a file accessible to the psql client.


Similarly, when you execute with psql a script file that contains your function definition:

CREATE FUNCTION copy_stuff_to_db(path text, encoding text) RETURNS void
AS $$
BEGIN
    \COPY table1 FROM path || 'table1.asc' WITH ENCODING encoding;
END;
$$ LANGUAGE plpgsql;


it (psql) doesn't actually execute any of that; it sends the code as a single statement to the server for processing. The server attempts to parse it as PL/pgSQL code (because of LANGUAGE plpgsql), compile,  and store in the database for future invocations. Since \COPY is not a valid PL/pgSQL command, compilation fails with the error you see.

PS. Others may correct me, but I don't think you can achieve what you want in a function even using a proper COPY command, because it would require dynamic SQL, which I think cannot be used with COPY.