Search Postgresql Archives

Re: Dynamic File Name for COPY TO in Stored Procedure

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxx

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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux