如何在PostgreSQL中使用FOR循环自动执行多个CSV导出

Question

我正试图通过符号导出一系列csv文件

CREATE OR REPLACE FUNCTION exportcrypto()
RETURNS void AS
$func$
declare
irec record;
BEGIN
FOR irec IN
SELECT DISTINCT symbol FROM daily
LOOP
EXECUTE 
'COPY (SELECT o as open, h as high, l as low, c as close, v as volume, 0.0 as dividend, 1.0 as split FROM daily WHERE symbol =' || irec.symbol || ')
TO "C:/Users/d/Documents/sdata/db_exports/crypto/' || irec.symbol || '.csv" WITH DELIMITER "," CSV HEADER;'
END LOOP;
END
$func$  
LANGUAGE plpgsql;

我在END LOOP或TO中遇到了各种错误，比如语法，并尝试了复制字符串的变体。

Answer 1

用单引号替换双引号并删除分号，除非symbol是数字(假设不是(，否则也在其周围添加引号：

EXECUTE 
'COPY (SELECT o as open, h as high, l as low, c as close, v as volume, 0.0 as dividend, 1.0 as split FROM daily WHERE symbol = ''' || irec.symbol || ''') TO ''C:/Users/d/Documents/sdata/db_exports/crypto/' || irec.symbol || '.csv'' WITH DELIMITER '','' CSV HEADER';

文字单引号'被编码为双引号''。

此外，与其将返回void的函数定义为一个过程，不如将其定义为LANGUAGE，所以原因应该是：

CREATE OR REPLACE PROCEDURE exportcrypto()
LANGUAGE plpgsql
AS $func$
declare irec record;
BEGIN
FOR irec IN SELECT DISTINCT symbol FROM daily LOOP
EXECUTE 
'COPY (SELECT o as open, h as high, l as low, c as close, v as volume, 0.0 as dividend, 1.0 as split FROM daily WHERE symbol = ''' || irec.symbol || ''') TO ''C:/Users/d/Documents/sdata/db_exports/crypto/' || irec.symbol || '.csv'' WITH DELIMITER '','' CSV HEADER';
END LOOP;
END
$func$