postgresql - 循环 CopyTo 查询

Question

我有下表:

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;

Answer 1

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;