plpgsql在For循环中选择语句以创建多个CSV文件 [英] plpgsql Select statement in For loop to create multiple CSV files

问题描述

我想重复以下查询8760次，将2替换为1到8760年的每小时。这个想法是为每个小时创建一个单独的CSV文件以供进一步处理。

I would like to repeat the following query 8760 times, replacing ‘2’ with 1 to 8760 for every hour in the year. The idea is to create a separate CSV file for each hour for further processing.

COPY 
(SELECT *
FROM 
  public.completedsolarirad2012
WHERE 
  completedsolarirad2012."UniquetmstmpID" = 2)
TO 'C:\temp\2012hour2.csv' WITH DELIMITER ','
CSV HEADER

我把以下函数放在一起（仅用几个小时测试）：

I have put together the following function (testing with only a few hours):

CREATE OR REPLACE FUNCTION everyhour()
  RETURNS void AS
$BODY$BEGIN
FOR i IN 0..5 LOOP
  EXECUTE $x$ 
  COPY (
    SELECT *
    FROM 
    public.completedsolarirad2012
    WHERE 
    completedsolarirad2012."UniquetmstmpID" = i
    )
   TO $concat$ 'C:\temp.' || i::text 
         || '.out.csv' WITH DELIMITER ',' CSV HEADER $concat$
    $x$;
END LOOP;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION everyhour()
  OWNER TO postgres;

我似乎有两个独立的问题：

I seem to be having two separate problems:

首先，我得到：

错误：列i不存在。

Error: column "i" does not exist.

其次，当我测试连接语句时，只需用eg替换i 2，我获得：

Secondly, when I test the concatenation statement only by replacing "i" with e.g. "2", I get:

错误：COPY到文件不允许使用相对路径

Error: relative path not allowed for COPY to file

我是postgres超级用户，所以我不明白为什么我有这个问题。

I am the postgres superuser, so I do not understand why I am having this problem.

注意：删除concatenation语句周围的 $ concat $ 双引号会产生以下错误：

Note: Removing the $concat$ double-quoting around the concatenation statement gives the following error:

ERROR:  syntax error at or near "||"
LINE 9:    TO 'C:\temp.' || i::text

我将非常感谢任何帮助。

I would be very grateful for any help.

推荐答案

假设您的服务器操作系统是Windows。

Assuming your server OS is Windows.

CREATE OR REPLACE FUNCTION everyhour()
  RETURNS void AS
$func$
BEGIN
FOR i IN 0..5 LOOP
   EXECUTE '
   COPY (
      SELECT *
      FROM   public.completedsolarirad2012 c
      WHERE  c."UniquetmstmpID" = ' || i || $x$)
   TO 'C:/temp.'$x$ || i || $x$'.out.csv' WITH DELIMITER ',' CSV HEADER$x$;
END LOOP;
END
$func$  LANGUAGE plpgsql;

• 您有一层美元报价太多。


• 您也不小心连接字母i而不是其值。


• 使用正斜杠，即使有窗口。或者，您可能需要加倍反斜杠，具体取决于您的设置。此相关答案中的更多内容：
  
  PostgreSQL：将结果数据从SQL查询导出到Excel / CSV


• You had one layer of dollar-quoting too many.
• You also accidentally concatenated the letter "i" instead of its value.
• Use forward-slashes, even with windows. Or you may have to double up the backslashes, depending on your settings. More in this related answer:
  PostgreSQL: export resulting data from SQL query to Excel/CSV

从Postgres 9.1开始，您可以使用 format（） 以简化复杂连接：

Since Postgres 9.1 you can use format() to simplify complex concatenations:

CREATE OR REPLACE FUNCTION everyhour()
  RETURNS void AS
$func$
BEGIN
FOR i IN 0..5 LOOP
   EXECUTE format($x$COPY (
   SELECT *
   FROM   public.completedsolarirad2012 c
   WHERE  c."UniquetmstmpID" = %1$s)
   TO 'C:/temp.%1$s.out.csv' WITH DELIMITER ',' CSV HEADER$x$, i);
END LOOP;
END
$func$  LANGUAGE plpgsql;

这篇关于plpgsql在For循环中选择语句以创建多个CSV文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！