From: Gary Stainburn <gary.stainburn@ringways.co.uk>
Organization: Ringways Garages Ltd
To: pgsql-sql@postgresql.org
Subject: Monthly budgets
Date: Thu, 1 Mar 2018 08:42:19 +0000
User-Agent: KMail/1.9.10
MIME-Version: 1.0
Content-Type: text/plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <201803010842.19235.gary.stainburn@ringways.co.uk>
Precedence: bulk

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)