From: Bart Degryse <Bart.Degryse@indicator.be>
To: pgsql-sql@postgresql.org
Subject: Failing join with set returning function
Date: Fri, 07 Sep 2007 11:51:53 +0200
Message-ID: <46E13B58.A3DD.0030.0@indicator.be> (raw)
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);
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: Bart.Degryse@indicator.be
Subject: Re: Failing join with set returning function
In-Reply-To: <46E13B58.A3DD.0030.0@indicator.be>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox