Monthly budgets

Gary Stainburn <[email protected]>
Newsgroups gmane.comp.db.postgresql.sql
Organization Ringways Garages Ltd
Message-ID <[email protected]>
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)
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.