Monthly budgets - Mailing list pgsql-sql
From | Gary Stainburn |
---|---|
Subject | Monthly budgets |
Date | |
Msg-id | 201803010842.19235.gary.stainburn@ringways.co.uk Whole thread Raw |
Responses |
Re: Monthly budgets
(Gary Stainburn <gary.stainburn@ringways.co.uk>)
|
List | pgsql-sql |
I have two tables create table default_bugdet ( dept_id int4 primary key, target_units int4 not null, unit_cost numeric(9,2) ); create table adjustments ( dept_id int4 not null, month_start date not null, target_units int4, unit_cost numeric(9,2), primary key (dept_id,month_start) ); I have test data: goole=# select * from default_bugdet ; dept_id | target_units | unit_cost ---------+--------------+----------- 1 | 20 | 10.00 (1 row) goole=# select * from adjustments ; dept_id | month_start | target_units | unit_cost ---------+-------------+--------------+----------- 1 | 2018-02-01 | | 15.00 1 | 2018-01-01 | 15 | (2 rows) If I use the following select it appears to give me what I want. goole=# select d.dept_id, a.month_start, coalesce(a.target_units,d.target_units) as target_units, coalesce(a.unit_cost,d.unit_cost) as unit_cost from default_bugdet d, adjustments a where d.dept_id=a.dept_id order by month_start; dept_id | month_start | target_units | unit_cost ---------+-------------+--------------+----------- 1 | 2018-01-01 | 15 | 10.00 1 | 2018-02-01 | 20 | 15.00 (2 rows) However, how can I create a view that would return: dept_id | month_start | target_units | unit_cost ---------+-------------+--------------+----------- 1 | 2018-01-01 | 15 | 10.00 1 | 2018-02-01 | 20 | 15.00 1 | 2018-03-01 | 20 | 10.00 .. 1 | 2018-12-01 | 20 | 10.00 (12 rows) I've through about using date_trunc and a range but can't work out how to actually generate the dataset to do the date_trunc on. I've come up with the following function which creates the dataset,but have no idea how I would create a view from it. Would I have to create another function that returns a setof default_budget? create or replace FUNCTION month_start(year int4) RETURNS SETOF date AS $$ DECLARE wdate date; i int4; BEGIN FOR i in 1..12 LOOP select (year::text || '-' || i::text || '-01'::text)::date into wdate; return next wdate; end LOOP; return; END; $$ LANGUAGE plpgsql; select * from month_start(2018); month_start ------------- 2018-01-01 2018-02-01 2018-03-01 2018-04-01 2018-05-01 2018-06-01 2018-07-01 2018-08-01 2018-09-01 2018-10-01 2018-11-01 2018-12-01 (12 rows)