title: Can't get postgres to recognise input variables in a function
body: <p>What am I doing wrong here?</p>
  
  <p>I want to write a function that returns a table of all the products that we have had dismal </p>
  
  <p>performance for between two dates - where 'dismal' means marketing costs exceeded profits. I </p>
  
  <p>have three tables: products, spend and transactions.  </p>
  
  <pre><code>CREATE TABLE products
  (id int not null, name varchar(255), primary key (id));
  
  CREATE TABLE spend
  (id int not null, spenddate date, spendamount decimal (9, 2));
  
  CREATE TABLE transactions
  (id int not null, transactiondate date, profit decimal (9, 2));
  </code></pre>
  
  <p>What I'd do is union queries between the two tables and then sum them to get a line per </p>
  
  <p>product:</p>
  
  <pre><code>WITH a as (
  SELECT products.id, products.name, 
  sum(transactions.profit) as profit,
  sum(0) as spendamount
  FROM transactions
  WHERE transactiondate BETWEEN startdate AND enddate
  GROUP BY products.id, products.name
  UNION ALL
  SELECT id, sum(0) as profit
  sum(amount) as spendamount
  FROM spend
  WHERE spenddate BETWEEN startdate AND enddate
  GROUP BY id)
  SELECT products.id, products.name, sum(profit)-sum(spendamount) as loss
  FROM a, products
  WHERE products.id = a.id
  GROUP BY products.id, products.name
  HAVING sum(profit)-sum(spendamount) &lt; 0;
  </code></pre>
  
  <p>But I don't want to keep changing the start and enddate values every time I run my code.</p>
  
  <p>I thought I'd do this:</p>
  
  <pre><code>CREATE FUNCTION report_dismal_products (startdate date, enddate date,
  out id int,
  out name varchar(255),
  out loss decimal(9, 2)
  
  )
  RETURNS SETOF RECORD 
   as $$
  
  SELECT products.id, products.name, sum(profit)-sum(spendamount) as loss
  FROM 
  (
  SELECT id, 
  sum(transactions.profit) as profit,
  sum(0) as spendamount
  FROM transactions
  WHERE transactiondate BETWEEN startdate AND enddate
  GROUP BY id
  UNION ALL
  SELECT id, sum(0) as profit
  sum(amount) as spendamount
  FROM spend
  WHERE spenddate BETWEEN startdate AND enddate
  GROUP BY id) a, products
  WHERE products.id = a.id
  GROUP BY products.id, products.name
  HAVING sum(profit)-sum(spendamount) &lt; 0;
  
  $$ Language sql;
  </code></pre>
  
  <p>But it returns </p>
  
  <pre><code>ERROR:  column "startdate" does not exist
  LINE 17:     WHERE transactiondate BETWEEN startdate AND enddate
  </code></pre>