[GENERAL] Dynamic File Name for COPY TO in Stored Procedure

2012-02-23 Thread Carlos Oliva 
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.

Re: [GENERAL] Dynamic File Name for COPY TO in Stored Procedure

2012-02-23 Thread Adrian Klaver 
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#PLPGSQL-STATEMENTS-EXECUTING-DYN


-- 
Adrian Klaver
adrian.kla...@gmail.com

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Dynamic File Name for COPY TO in Stored Procedure

2012-02-23 Thread Carlos Oliva 
That worked.  Thank you Adrian


-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@gmail.com] 
Sent: Thursday, February 23, 2012 9:58 AM
To: pgsql-general@postgresql.org
Cc: Carlos Oliva
Subject: Re: [GENERAL] 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.kla...@gmail.com




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general