agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: pgsql-sql@postgresql.org
Subject: Monthly budgets
Date: Thu, 1 Mar 2018 08:42:19 +0000
Message-ID: <201803010842.19235.gary.stainburn@ringways.co.uk> (raw)
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)
view thread (2+ messages) latest in thread
Message-ID: <201803010842.19235.gary.stainburn@ringways.co.uk>
Permalink: ../201803010842.19235.gary.stainburn@ringways.co.uk/
Also on: postgresql.org/message-id/201803010842.19235.gary.stainburn@ringways.co.uk
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gary.stainburn@ringways.co.uk
Subject: Re: Monthly budgets
In-Reply-To: <201803010842.19235.gary.stainburn@ringways.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox