gistfile1.sql

 IF debug THEN PERFORM summary_v2.debug_loger(run_id, function_name, 'CREATE INDEX on TEMPORARY TABLE'); END IF;
       CREATE INDEX tracking_campaign_details_raw_report_idx on tracking_campaign_details_raw_report_table (report_date, site_id, placement_id, creative_group_id, creative_id) ;

       IF debug THEN PERFORM summary_v2.debug_loger(run_id, function_name, 'Join AK data with _3pas data'); END IF;

       INSERT INTO tracking_campaign_details_raw_report_table_rez
          SELECT
              campaign_ids_in[j]::bigint,
              COALESCE(ak.report_date, _3pas.report_date) AS report_date,
              COALESCE(ak.site_id, _3pas.site_id::bigint) AS site_id,
              COALESCE(ak.placement_id, _3pas.placement_id::bigint) AS placement_id,
              COALESCE(ak.creative_id, _3pas.creative_id::bigint) AS creative_id,
              COALESCE(ak.creative_group_id, _3pas.creative_group_id::bigint) AS creative_group_id,

              COALESCE(ak.impressions,0)::bigint AS impressions,
              COALESCE(ak.clicks,0)::bigint AS clicks,
              COALESCE(ak.view_actions,0)::bigint AS view_actions,
              COALESCE(ak.click_actions,0)::bigint AS click_actions,
              COALESCE(ak.view_action_revenue,0)::double precision AS view_revenue,
              COALESCE(ak.click_action_revenue,0)::double precision AS click_revenue,
              COALESCE(_3pas.impressions,0)::bigint AS _3pas_impressions,
              COALESCE(_3pas.clicks,0)::bigint AS _3pas_clicks,
              COALESCE(_3pas.view_actions,0)::bigint AS _3pas_view_actions,
              COALESCE(_3pas.click_actions,0)::bigint AS _3pas_click_actions,
              COALESCE(_3pas.view_revenue,0)::double precision AS _3pas_view_revenue,
              COALESCE(_3pas.click_revenue,0)::double precision AS _3pas_click_revenue
          FROM tracking_campaign_details_raw_report_table ak
          FULL JOIN
          (
           SELECT
               report_date,
               case when (trim(site_id) ~ E'^\\d+$') then trim(site_id) else '-1' end  as site_id,
               case when (trim(placement_id) ~ E'^\\d+$') then trim(placement_id) else '-1' end  as placement_id,
               case when (trim(creative_id) ~ E'^\\d+$') then trim(creative_id) else '-1' end  as creative_id,
               case when (trim(creative_group_id) ~ E'^\\d+$') then trim(creative_group_id) else '-1' end  as creative_group_id,
               impressions,
               (SELECT COALESCE(SUM((item::summary_v2.engagement_by_flavor).raw_count),0)
                      FROM (SELECT unnest(engagements_by_flavor) AS item) AS items WHERE (item::summary_v2.engagement_by_flavor).flavor_id = ANY(codes)
               ) AS clicks,
               view_actions,
               click_actions,
               view_revenue,
               click_revenue
           FROM summary_v2.third_party_campaign_details_report
           WHERE report_date BETWEEN from_date[j] AND to_date[j] AND campaign_id = campaign_ids_in[j]
          ) AS _3pas
                ON ak.report_date = _3pas.report_date
                AND ak.site_id = _3pas.site_id::bigint
                AND ak.placement_id = _3pas.placement_id::bigint
                AND ak.creative_id = _3pas.creative_id::bigint
                AND ak.creative_group_id = _3pas.creative_group_id::bigint
       ;

       -- Clear temporary table for SUMMARY data
       DROP INDEX IF EXISTS tracking_campaign_details_raw_report_idx;
       TRUNCATE tracking_campaign_details_raw_report_table;

       -- creating index for performance
       IF debug THEN PERFORM summary_v2.debug_loger(run_id, function_name, 'CREATE INDEX on TEMPORARY TABLE'); END IF;
       CREATE INDEX tracking_campaign_details_raw_report_table_rez_idx on tracking_campaign_details_raw_report_table_rez(report_date) ;

       IF debug THEN PERFORM summary_v2.debug_loger(run_id, function_name, 'Add empty rows'); END IF;
       INSERT INTO tracking_campaign_details_raw_report_table_rez
           SELECT
               campaign_ids_in[j]::bigint,
               dt.all_days AS report_date,
               null,
               null,
               null,
               null,

               0::bigint AS impressions,
               0::bigint AS clicks,
               0::bigint AS view_actions,
               0::bigint AS click_actions,
               0::double precision AS view_revenue,
               0::double precision AS click_revenue,
               0::bigint AS _3pas_impressions,
               0::bigint AS _3pas_clicks,
               0::bigint AS _3pas_view_actions,
               0::bigint AS _3pas_click_actions,
               0::double precision AS _3pas_view_revenue,
               0::double precision AS _3pas_click_revenue
           FROM ( SELECT from_date[j]::date + x AS all_days FROM generate_series(0, to_date[j]::date - from_date[j]::date) AS s(x) ) AS dt
           LEFT JOIN (SELECT DISTINCT report_date FROM tracking_campaign_details_raw_report_table_rez) AS rd
           ON dt.all_days=rd.report_date WHERE rd.report_date is null
       ;

       IF debug THEN PERFORM summary_v2.debug_loger(run_id, function_name, 'RETURN QUERY for campaign '||campaign_ids_in[j]); END IF;
       RETURN QUERY SELECT * FROM tracking_campaign_details_raw_report_table_rez ORDER BY 1, 2, 3, 4, 5, 6;