Can I use \copy command into a function of postgresql?

Question

I am trying to create this statement into a function:

\copy aux("nombre") TO '/home/david/lugares.csv' delimiters ';';

So I do the next:

 CREATE OR REPLACE FUNCTION crearcsv()
      RETURNS void AS
    $BODY$
    DECLARE STATEMENT TEXT; 
    BEGIN
        RAISE NOTICE 'CREAR CSV';
        STATEMENT:= '\copy aux ("nombre") TO ''/home/david/lugares.csv'' delimiters '';'';';    
        RAISE NOTICE '%',STATEMENT;
        EXECUTE STATEMENT;
    END;$BODY$
      LANGUAGE plpgsql VOLATILE
      COST 100;

But I get the next when I call to the function:

NOTICE: \copy aux ("nombre") TO '/home/david/lugares.csv' delimiters ';'; ERROR: syntax error at or near "\"
LINE 1: \copy aux ("nombre") TO '/home/david/lugares.csv' delimiters... ^
QUERY: \copy aux ("nombre") TO '/home/david/lugares.csv' delimiters ';';
CONTEXT: PL/pgSQL function crearcsv() line 7 at EXECUTE statement**

This statement works fine in PSQL console

Any help?

Blinnie

That's because \copy is a psql command that does more or less what the sql command copy does - with the most important difference that \copy uses files on the filesystem of the computer running psql, and copy uses files on the server's filesystem. So, they are highly similar but not identical, and you cannot use \copy in a function as it's not an sql command. – Alginate

\copy is a psql command and can only be executed from within psql. It is not a general SQL statement. – Remorse

Then, how can I do this function? – Blinnie

@user3733164 You can't. There's no way to read or write files on the client via a pl/pgsql function – Mailman

Answer 1

You can simply change \copy in copy. COPY is the "sql variant" of \copy, works in a database function, the syntax is identical but has some differences which can be relevant for you:

COPY is the Postgres method of data-loading. Postgres's COPY comes in two separate variants, COPY and \COPY: COPY is server based, \COPY is client based.

COPY will be run by the PostgreSQL backend (user "postgres"). The backend user requires permissions to read & write to the data file in order to copy from/to it. You need to use an absolute pathname with COPY. \COPY on the other hand, runs under the current $USER, and with that users environment. And \COPY can handle relative pathnames. The psql \COPY is accordingly much easier to use if it handles what you need.

With either of these you'll also need to have insert/update or select permission on the table in order to COPY to or from it.

From https://wiki.postgresql.org/wiki/COPY

The main difference is that COPY will write the output file on the file system where the postgres server is running, not on the server where you execute COPY. This will be the same, if you have a postgres server running on localhost, but can be big problem by more complex scenarios.

See also the documentation: http://www.postgresql.org/docs/9.3/static/sql-copy.html

and this answer: Save PL/pgSQL output from PostgreSQL to a CSV file

Haplography

The drawback of this is that copy can only be used by a superuser in Postgres as it will write files on the server. – Remorse

As well as a_horse_with_no_name says, I can't use COPY because it needs to be used by a superuser. Sorry because of I didnt mentioned it – Blinnie

File system IO operations are generally uncomfortable via database functions (but not impossible: in depends on your scenario). You can try with plpython or plperl, but you could have similar problems as with plpgsql. Try with a shell script which execute psql. – Haplography

Answer 2

You might be better writing a Python script that connects to the DB and runs the COPY command. Psycopg2 is the best adapter for this.

Balneal