Monthly budgets

Поиск
Список
Период
Сортировка
От Gary Stainburn
Тема Monthly budgets
Дата
Msg-id 201803010842.19235.gary.stainburn@ringways.co.uk
обсуждение исходный текст
Ответы Re: Monthly budgets  (Gary Stainburn <gary.stainburn@ringways.co.uk>)
Список 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)



В списке pgsql-sql по дате отправления:

Предыдущее
От: garret
Дата:
Сообщение: update tables with instead of trigger after update of view
Следующее
От: Gary Stainburn
Дата:
Сообщение: Re: Monthly budgets