Dynamic File Name for COPY TO in Stored Procedure

From: "Carlos Oliva" <CarlosO(at)pbsinet(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Dynamic File Name for COPY TO in Stored Procedure
Date: 2012-02-23 14:42:53
Message-ID: C34A4F8A078F5E4285EB8B10FF0DAF3101F69FDB@pbsi-sbsxch.PosBus.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2012-02-23 14:57:33 Re: Dynamic File Name for COPY TO in Stored Procedure
Previous Message Chris Travers 2012-02-23 14:22:51 Re: Postgresql as main database