postgresql - 循环复制到查询

我有下表：

writing_agent_number|agent_name       |individual_sub_name
-------------------- ----------------- --------------------
300699              |J Todd Foster    |Haash,matthew
300699              |J Todd Foster    |Lea, Monique
300693              |Loran J Marmes   |Jensen, Jeffrey A
300842              |Mattthew Atkinson|Castano, Karissa
300927              |Richard Melberg  |Burk, Barbara
300694              |Ryan Moran       |Moran, Maura C
300697              |Scott W Lange, Jr|Stewart, Allisia

我的目标：我想创建一个名为“agent_name.[timestamp].csv”的单独csv文件，其中agent_name是“agent_name”列中每个不同代理的名称。
下面是我所拥有的（请原谅，我是sql新手）我假设我丢失了一个输入代理名称的变量，但我不确定从这里到哪里。感谢您的帮助：

CREATE OR REPLACE FUNCTION get_commissions()
$func$
BEGIN LOOP
   EXECUTE '

COPY
(SELECT
writing_agent_number AS "Writing Number"
,agent_name AS "Agent Name"
,indiv_sub_name AS "Client Name"

FROM rdf_nwh_ind
TO '/Users/ryanmoran/Downloads/agent_name.csv'
WITH CSV Delimiter ',' Header;


END LOOP;'
END
$func$  LANGUAGE plpgsql;

最佳答案

CREATE OR REPLACE FUNCTION get_commissions() RETURNS bool AS $func$
DECLARE
    v_agent_name text;
BEGIN
   FOR v_agent_name IN SELECT DISTINCT agent_name FROM rdf_nwh_ind
   LOOP
       EXECUTE format($$COPY (
           SELECT writing_agent_number AS "Writing Number",
                  agent_name AS "Agent Name",
                  indiv_sub_name AS "Client Name"
             FROM rdf_nwh_ind
            WHERE agent_name = %s
           )
           TO '/Users/Shared/%s.%s.csv'
           WITH CSV Delimiter ',' Header$$,
               quote_nullable(v_agent_name),
               replace(v_agent_name, ' ', '_'),
               to_char(now(), 'YYYY-DD-MM'));
    END LOOP;
    RETURN true;
END;
$func$  LANGUAGE plpgsql;

关于postgresql - 循环复制到查询，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/42043450/