title: How to write a postgresql function to sum grouped metrics from a daily report table
body: <p>I am trying to write a function that sums up reports for a given date range.</p>
  <p>I have 3 tables:</p>
  <ul>
  <li><code>campaign</code> it has a primary key (<code>campaign.id</code>) and the portfolio the campaign is related to (<code>campaign.portfolio_id</code>)</li>
  <li><code>report_campaign</code> is a daily report table, each row represents some metrics for a specific date(<code>report_campaign.report_date</code>) and the campaign the report is for <code>report_campaign.campaign_id</code></li>
  <li><code>portfolio_summary</code> This table doesn't contain any data, but a tool I use needs this table to understand the information returned by the function.</li>
  </ul>
  <p>I have written a function that I think should work but I am seeing some strange behaviour that I do not understand. When I call the function it does not filter out anything by the dates I pass in instead it includes all data in the table, even when I call the function with a start date after the end date I receive all the data in the table. I have some similar functions that accept start_date and end_date in an identical fashion and they work.</p>
  <p>Here is the function:</p>
  <pre><code>CREATE OR REPLACE FUNCTION public.portfolio_summary_between_dates(start_date date, end_date date)
   RETURNS SETOF portfolio_summary
   LANGUAGE sql 
   STABLE
  AS $$ WITH cte AS (
    SELECT 
      campaign.portfolio_id AS id, 
      sum(clicks) AS clicks, 
      sum(impressions) AS impressions, 
      sum(attributed_sales_14d) AS sales, 
      sum(cost) AS total_cost 
    FROM 
      report_campaign 
      INNER JOIN campaign ON report_campaign.campaign_id = campaign.id 
    WHERE 
      report_campaign.report_date &gt;= start_date 
      AND report_campaign.report_date &lt;= end_date 
      AND campaign.portfolio_id IS NOT NULL 
    GROUP BY 
      campaign.portfolio_id
  ) 
  SELECT 
    *, 
    (clicks :: float / NULLIF(impressions, 0)) AS ctr, 
    (total_cost / NULLIF(sales, 0)) AS acos, 
    (total_cost / NULLIF(clicks, 0)) AS cpc 
  FROM 
    cte $$
  </code></pre>
  <p>I am using Postgres 10.6</p>