Monthly budgets

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)

Responses

Browse pgsql-sql by date

  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