pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFailing join with set returning function
2+ messages / 2 participants
[nested] [flat]
* Failing join with set returning function
@ 2007-09-07 09:51 Bart Degryse <Bart.Degryse@indicator.be>
0 siblings, 0 replies; 2+ messages in thread
From: Bart Degryse @ 2007-09-07 09:51 UTC (permalink / raw)
To: 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);
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Failing join with set returning function
@ 2007-09-07 13:04 frobbiani@libero.it <frobbiani@libero.it>
0 siblings, 0 replies; 2+ messages in thread
From: frobbiani\@libero\.it @ 2007-09-07 13:04 UTC (permalink / raw)
To: pgsql-sql
Please, remove my address from this Mailing List.
thanks
------------------------------------------------------
Leggi GRATIS le tue mail con il telefonino i-mode™ di Wind
http://i-mode.wind.it/
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2007-09-07 13:04 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-09-07 09:51 Failing join with set returning function Bart Degryse <Bart.Degryse@indicator.be>
2007-09-07 13:04 Re: Failing join with set returning function frobbiani@libero.it <frobbiani@libero.it>
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