That worked. Thank you Adrian -----Original Message----- From: Adrian Klaver [mailto:adrian.klaver@xxxxxxxxx] Sent: Thursday, February 23, 2012 9:58 AM To: pgsql-general@xxxxxxxxxxxxxx Cc: Carlos Oliva Subject: Re: Dynamic File Name for COPY TO in Stored Procedure On Thursday, February 23, 2012 6:42:53 am Carlos Oliva wrote: > Hi, > 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 will need to switch to using plpgsql and its EXECUTE statement to build the COPY statement : http://www.postgresql.org/docs/9.0/interactive/plpgsql-statements.html#P LPGSQL-STATEMENTS-EXECUTING-DYN -- Adrian Klaver adrian.klaver@xxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
