COPY TO File: Using dynamic file name in stored procedure

Carlos Oliva wrote:

What would it be the correct format for using a variable in a stored

procedure that uses COPY TO?

I have the current stored procedure:
CREATE FUNCTION Table_To_File(text) RETURNS void AS $delimeter$
COPY (SELECT * FROM table_to_xml('table', true, false, '')) TO '$1'

WITH CSV QUOTE ' ';

$delimeter$
LANGUAGE SQL;

When I run the stored procedure: psql -d <db name> -c "select
Table_To_File('some_absolute_file_name')"; I get the error that I

must use absolute file names.

When I replace TO '$1' with TO '/tmp/toto.xml', the stored procedure

runs fine and creates the local

file.

I have tried several different ways to escape the $1 variable to no

avail. At best, I get the same

error.

You can't do this in an SQL function, you'll have to use
dynamic SQL in PL/pgSQL, like this:

CREATE OR REPLACE FUNCTION table_to_file(text) RETURNS void AS
$delimeter$
BEGIN
EXECUTE 'COPY (SELECT * FROM test) TO ''' || $1 || ''' WITH CSV QUOTE
'' ''';
END;
$delimeter$
LANGUAGE plpgsql STRICT;

Yours,
Laurenz Albe