CREATE OR REPLACE FUNCTION tmp_table_to_file(text) RETURNS void AS
$delimeter$
DECLARE
    curtime timestamp := now();
BEGIN
    EXECUTE format ('COPY (
    select
        distinct(b.worker_role_callsign) as Driver_Callsign
        ,a.lastname||'' ''||a.firstname||'' ''||a.MiddleName as FIO
        ,array_agg(c.contact_value) as Phones
        ,a.Order_count
        ,a.bonusSum
        ,a.commissionSum
        ,a.distanceSum
        ,a.idleBeforeSeatSum
        ,a.awaitSum
    from
        tmp_driver_stat a,
        core.worker_role b,
        core.contact c,
        core.worker_contact wc
    where
        b.worker_role_worker_id = a.Driver_id
        and wc.worker_contact_worker_id=b.worker_role_worker_id
        and wc.worker_contact_contact_id=c.contact_id
    group by
        b.worker_role_callsign
        ,a.lastname
        ,a.firstname
        ,a.MiddleName
--      ,c.contact_value
        ,a.Order_count
        ,a.bonusSum
        ,a.commissionSum
        ,a.distanceSum
        ,a.idleBeforeSeatSum
        ,a.awaitSum
    order by 1
    ) TO %L (FORMAT CSV, HEADER, DELIMITER '';'' )', $1);
END;
$delimeter$
LANGUAGE plpgsql;
DO $$
DECLARE
dep_id bigint;
dep_name text;
BEGIN
FOR dep_id IN select department_id from core.department where department_type_id=3 order by department_id LOOP
drop table if exists tmp_driver_stat;
RAISE NOTICE 'generate %', dep_id;
create table tmp_driver_stat AS
select
(json_array_elements(report_json_driver_orders)->>'driverId')::bigint AS Driver_id
,json_array_elements(report_json_driver_orders)->>'lastName' AS LastName
,json_array_elements(report_json_driver_orders)->>'firstName' AS FirstName
,json_array_elements(report_json_driver_orders)->>'middleName' AS MiddleName
,json_array_elements(report_json_driver_orders)->>'orderCnt' AS Order_count
,json_array_elements(report_json_driver_orders)->>'bonusSum' AS BonusSum
,json_array_elements(report_json_driver_orders)->>'commissionSum' AS CommissionSum
,json_array_elements(report_json_driver_orders)->>'distanceSum' AS DistanceSum
,json_array_elements(report_json_driver_orders)->>'idleBeforeSeatSum' AS idleBeforeSeatSum
,json_array_elements(report_json_driver_orders)->>'awaitSum' AS awaitSum
from interface.report_json_driver_orders(
report_json_driver_orders_department_id_ := dep_id ::bigint
,report_json_driver_orders_timezone_:='+03:00'::character varying
,report_json_driver_orders_dt_type_:=1::smallint
,report_json_driver_orders_begin_dt_:='2018-12-01 05:00:00'::timestamp without time zone
,report_json_driver_orders_end_dt_:='2019-01-01 00:00:00'::timestamp without time zone
-- Тип заказов 5- успешные, 6 - не успешные, null - все
,report_json_driver_orders_order_state_id_:='5'::smallint
,report_json_driver_orders_order_source_id_:= array[1,3,2,5,6]::bigint[]
);

RAISE NOTICE 'copy %', dep_id;
select department_name into dep_name from core.department where department_id=dep_id;
dep_name:=dep_name ||'_'||dep_id;
PERFORM tmp_table_to_file('/tmp/report/UE_8765_'|| dep_name ||'.csv');
RAISE NOTICE 'done %', dep_id;
END LOOP;

END $$;