Re: COPY TO File: Using dynamic file name in stored procedure

Поиск
Список
Период
Сортировка
От Albe Laurenz
Тема Re: COPY TO File: Using dynamic file name in stored procedure
Дата
Msg-id D960CB61B694CF459DCFB4B0128514C20785D0C4@exadv11.host.magwien.gv.at
обсуждение исходный текст
Ответ на COPY TO File: Using dynamic file name in stored procedure  ("Carlos Oliva" <CarlosO@pbsinet.com>)
Список pgsql-general
Carlos Oliva wrote:
> 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 can't do this in an SQL function, you'll have to use
dynamic SQL in PL/pgSQL, like this:

CREATE OR REPLACE FUNCTION table_to_file(text) RETURNS void AS
$delimeter$
BEGIN
   EXECUTE 'COPY (SELECT * FROM test) TO ''' || $1 || ''' WITH CSV QUOTE
'' ''';
END;
$delimeter$
LANGUAGE plpgsql STRICT;

Yours,
Laurenz Albe

В списке pgsql-general по дате отправления:

Предыдущее
От: Richard Huxton
Дата:
Сообщение: Re: Problemas com client_encoding ?
Следующее
От: Simon Riggs
Дата:
Сообщение: Re: Optimise PostgreSQL for fast testing