在动态COPY语句中使用函数变量 [英] Use function variable in dynamic COPY statement

问题描述

根据PostgreSQL的文档，可以直接从查询将数据复制到csv文件，而无需使用中间表.我很好奇该怎么做.

According to docs of PostgreSQL it is possible to copy data to csv file right from a query without using an intermediate table. I am curious how to do that.

CREATE OR REPLACE FUNCTION m_tbl(my_var integer)
    RETURNS void AS
$BODY$
DECLARE
BEGIN

   COPY (
       select my_var
   )
   TO 'c:/temp/out.csv';

END;
$$ LANGUAGE plpgsql;

我收到一个错误:没有这样的列'my_var'.

I get an error: no such column 'my_var'.

推荐答案

是的，无论查询是否引用表，都可以从任何查询中获取COPY.

Yes, it is possible to COPY from any query, whether or not it refers to a table.

但是，COPY是不可计划的语句，是实用程序语句.它不支持查询参数-查询参数是PL/PgSQL如何将变量插入语句中.

However, COPY is a non-plannable statement, a utility statement. It doesn't support query parameters - and query parameters are how PL/PgSQL implements the insertion of variables into statements.

因此您不能在COPY中使用PL/PgSQL变量.

So you can't use PL/PgSQL variables with COPY.

您必须改为将动态SQL与EXECUTE一起使用.有关示例，请参见Pl/PgSQL文档.堆栈溢出和 https://dba.stackexchange.com/上也有很多示例.

You must instead use dynamic SQL with EXECUTE. See the Pl/PgSQL documentation for examples. There are lots of examples here on Stack Overflow and on https://dba.stackexchange.com/ too.

类似的东西:

EXECUTE format('
   COPY (
       select %L
   )
   TO ''c:/temp/out.csv'';
', my_var);

如果您希望文件 path 是动态的，则同样适用-您将使用:

The same applies if you want the file path to be dynamic - you'd use:

EXECUTE format('
   COPY (
       select %L
   )
   TO %L;
', my_var, 'file_name.csv');

它也适用于动态的列名，但是对于'my_value'这样的文字，您可以使用%I(用于标识符，例如"my_name")，而不是使用%L.有关%I和%L的详细信息，请参见format的文档.

It also works for dynamic column names but you would use %I (for identifier, like "my_name") instead of %L for literal like 'my_value'. For details on %I and %L, see the documentation for format.

这篇关于在动态COPY语句中使用函数变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！