使用自定义文件名路径复制csv [英] COPY csv using custom filename path

问题描述

尝试使用COPY函数将查询导出到CSV时遇到一些问题.

I'm getting some issues while trying to export a query to CSV using the COPY function.

如果不使用TO上的自定义文件名，则COPY运行正常并成功导出查询.

The COPY runs ok and exports the query successfully if not using custom filenames on the TO.

此问题与在创建的文件名中添加日期戳"(类型)有关.

The issue is related to add a "datestamp" (kinda) to the filename created.

declare var1 varchar(25);
DECLARE STATEMENT TEXT;
select into var1 current_date -1;
STATEMENT := 'COPY (SELECT * from myTable) To ''E'C:\\Exports\\export_'||var1||'.csv' ''With CSV';
EXECUTE STATEMENT;

在这种情况下，var1得到一个类似于2013-12-16的值，我需要将其添加到文件名中以获得export_2012-12-16.csv

In this case, var1 gets a value like 2013-12-16 and I need to add that to the filename to obtain export_2012-12-16.csv

我假设'放错了位置.我尝试了几种组合，但均未成功，当然，错误是ERROR: syntax error at or near "C".

I'm assuming that the ' are misplaced. I've tried several combinations without success and off course the error is ERROR: syntax error at or near "C".

推荐答案

plpgsql代码可以像这样工作:

plpgsql code could work like this:

...
DECLARE
   var1 text;
BEGIN
var1 := to_char(current_date - 1, 'YYYY-MM-DD');
EXECUTE $$COPY (SELECT * from myTable)
          TO E'C:\\Exports\\export_$$ || var1 || $$.csv' WITH CSV$$;
...

您的报价陷入混乱.使用美元引用进行简化.请注意，此处SO突出显示的语法具有误导性，因为它不理解美元报价.

Your quotes got tangled. Using dollar-quoting to simplify. Note that syntax highlighting here on SO is misleading because it does not understand dollar-quoting.

DECLARE仅需要一次(虽然不是错误).另外，BEGIN丢失了.

DECLARE is only needed once (not an error though). Plus, BEGIN was missing.

然后to_char()使日期的文本表示与语言环境无关.

And to_char() makes the text representation of a date independent of the locale.

这篇关于使用自定义文件名路径复制csv的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！