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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general