Failing join with set returning function - Mailing list pgsql-sql

From Bart Degryse
Subject Failing join with set returning function
Date
Msg-id 46E13B58.A3DD.0030.0@indicator.be
Whole thread Raw
List pgsql-sql
I've written a function that calculates the number of days for every month in a given range and returns that as a set of records.
CREATE OR REPLACE FUNCTION general_daysinmonth(
  date1 IN date,
  date2 IN date,
  month OUT date,
  days OUT integer) RETURNS SETOF record AS
$body$
DECLARE
  startdate date;
  enddate date;
BEGIN
  IF date1 >= date2 THEN
    startdate := date2;
    enddate := date1;
  ELSE
    startdate := date1;
    enddate := date2;
  END IF;
  month := date_trunc('month', startdate);
  WHILE month <= enddate LOOP
    days := LEAST(general_lastdayofmonth(month), enddate) - GREATEST(startdate, month) + 1;
    RETURN NEXT;
    month := month + interval '1 month';
  END LOOP;
  RETURN;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE RETURNS NULL ON NULL INPUT SECURITY INVOKER;
Now I want to use that resultset as part of a join with a table called billing (see definition below).
This join should return a record per month that is between salesstartdate and salesenddate and lineamountmst should be divided pro rata the number of days in each month
 
An example
If table billing holds a record like
id invoiceid inventtransid invoicedate dataareaid lineamountmst dimension itemid salesunit issues salesstartdate salesenddate salesstopcode salespoolid
1170 22428431 735706 2006-02-28 hlm 89,89 nlaatoal-6 nlaatoal 3m 0 2006-02-24 2006-05-23 SWI-TRM-1Y aans
Then select * from general_daysinmonth(date '2006-02-24', date '2006-05-23')
would return
  month days
  2006-02-01 5
  2006-03-01 31
  2006-04-01 30
  2006-05-01 23
 
So my join should return 4 records like
  invoiceid billingmonth revenuemonth revenue
  22428431 2006-02-01 2006-02-01 5,05 
  22428431 2006-02-01 2006-03-01 31,31
  22428431 2006-02-01 2006-04-01 30,30
  22428431 2006-02-01 2006-05-01 23,23
where 89,89 / number of days between 2006-02-24 and 2006-05-23 * number of days in 2006-02 = 5,05 for the first record
 
I thought I could do that quite easily like this
    SELECT B.invoiceid, date_trunc('month', B.invoicedate) as billingmonth, C.month as revenuemonth,
                B.lineamountmst * C.days / (B.salesenddate - B.salesstartdate + 1) as revenue
    FROM billing B, general_daysinmonth(B.salesstartdate, B.salesenddate) C
    WHERE B.dataareaid = 'hlm' AND
          B.issues = 0 AND
          B.salesenddate >= date_trunc('year', current_timestamp) - interval '1 year';
or like this
    SELECT B.invoiceid, date_trunc('month', B.invoicedate) as billingmonth, C.month as revenuemonth,
                B.lineamountmst * C.days / (B.salesenddate - B.salesstartdate + 1) as revenue
    FROM billing B, (select * from general_daysinmonth(B.salesstartdate, B.salesenddate)) C
    WHERE B.dataareaid = 'hlm' AND 
          B.issues = 0 AND
          B.salesenddate >= date_trunc('year', current_timestamp) - interval '1 year';

But I get an error message in both cases: respectively
ERROR:  function expression in FROM may not refer to other relations of same query level
ERROR:  subquery in FROM may not refer to other relations of same query level
 
Can anyone please explain me why I'm getting this error message and how I do what I'm trying to do?
Thanks for your help, advice and time...
 
CREATE TABLE public.billing (
  id serial PRIMARY KEY,
  invoiceid varchar(20) DEFAULT NULL,
  inventtransid varchar(20) DEFAULT NULL,
  invoicedate date NOT NULL,
  dataareaid varchar(3) NOT NULL,
  lineamountmst numeric(32,16) NOT NULL,
  dimension varchar(16) NOT NULL,
  itemid varchar(20) NOT NULL,
  salesunit varchar(10) NOT NULL,
  issues numeric(2) DEFAULT 0,
  salesstartdate date NOT NULL,
  salesenddate date NOT NULL,
  salesstopcode varchar(16) DEFAULT NULL,
  salespoolid varchar(10) NOT NULL
) WITH (fillfactor=75);
 
 

pgsql-sql by date:

Previous
From: "A. Kretschmer"
Date:
Subject: Re: to_date function
Next
From: "frobbiani\@libero\.it"
Date:
Subject: Re: Failing join with set returning function