From: | Gary Stainburn <gary(dot)stainburn(at)ringways(dot)co(dot)uk> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Monthly budgets |
Date: | 2018-03-01 08:42:19 |
Message-ID: | 201803010842.19235.gary.stainburn@ringways.co.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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)
From | Date | Subject | |
---|---|---|---|
Next Message | Gary Stainburn | 2018-03-01 09:42:38 | Re: Monthly budgets |
Previous Message | garret | 2018-02-27 17:59:22 | update tables with instead of trigger after update of view |