gistfile1.sql

DROP FUNCTION IF EXISTS summary_v2.get_campaign_placement_currency(bigint, date, date);

CREATE OR REPLACE FUNCTION summary_v2.get_campaign_placement_currency(campaign_id bigint, date_from date, date_to date)
  RETURNS TABLE (
  currency VARCHAR
  ) AS
  $BODY$
  BEGIN
    RETURN QUERY
    SELECT array_to_string(array_agg( DISTINCT coalesce(pc.iso_code, 'NULL')), ',')::VARCHAR AS currency
    FROM summary_v2.campaign_placement_details_cost_daily_v2 AS daily
      INNER JOIN ref_data.tracking_campaign AS tc ON (daily.campaign_id            = tc.external_id::BIGINT)
      INNER JOIN ref_data.provider          AS pr ON (pr.id                        = tc.tracking_data_provider_id AND pr.tpasns_id    = daily.third_party_adserver_id)
      LEFT  JOIN mdata.placement_currency   AS pc ON (pc.tracking_data_provider_id = tc.tracking_data_provider_id AND pc.placement_id = daily.inventory_placement_id)

    WHERE summary_v2.empty_to_null(tc.external_id) IS NOT NULL
          AND (tc.external_id ~ E'^\\d+$')
          AND  pr.tpasns_id IS NOT NULL
          AND  tc.campaign_id = $1
          AND  daily.report_date >= date_from
          AND  daily.report_date <= date_to
    GROUP BY tc.campaign_id;
    RETURN;
  END
  $BODY$
LANGUAGE plpgsql VOLATILE;