Обсуждение: Dynamic File Name for COPY TO in Stored Procedure
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.
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.klaver@gmail.com
That worked. Thank you Adrian
-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@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.klaver@gmail.com