From: Aaron Bono <postgresql@aranya.com>
To: Henry Ortega <juandelacruz@gmail.com>
Cc: Michael Fuhr <mike@fuhr.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: Trigger on Insert to Update only newly inserted fields?
Date: Thu, 31 Aug 2006 09:55:30 -0500
Message-ID: <bf05e51c0608310755j3f392dfdm3e6e829129ef908b@mail.gmail.com> (raw)
In-Reply-To: <2bffcc330608280853x50b1034nca246a5dcc935e7@mail.gmail.com>
References: <2bffcc330608280702y16c25090i816589e20163e7e8@mail.gmail.com>
<20060828145737.GA5004@winnie.fuhr.org>
<2bffcc330608280853x50b1034nca246a5dcc935e7@mail.gmail.com>
On 8/28/06, Henry Ortega <juandelacruz@gmail.com> wrote:
>
> Here's what I am doing:
>
> I have this table:
> employee payrate effective
> tstamp end_date (to be updated by
> trigger)
> jdoe 1000 04-01-2006 2006-03-10 13:39: 07.614945
> jdoe 1500 04-01-2006 2006-03-12 15:43:14.423325
> jdoe 1555 04-16-2006 2006-03-15 12:14:15.112444
> peter 500 04-1-2006 2006-03-25 08:13:35.152166
> peter 900 04-16-2006 2006-03-28 09:22:14.456221
>
> After the trigger runs, I want to have this:
> employee payrate effective
> tstamp end_date (to be updated by
> trigger)
> jdoe 1000 04-01-2006 2006-03-10 13:39:07.614945
> 04-15-2006
> jdoe 1500 04-01-2006 2006-03-12 15:43:14.423325
> 04-15-2006
> jdoe 1555 04-16-2006 2006-03-15 12:14:15.112444
> NULL
> peter 500 04-1-2006 2006-03-25 08:13:35.152166
> 04-15-2006
> peter 900 04-16-2006 2006-03-28 09:22:14.456221
> NULL
>
You may want to separate this into two tables:
employee_day
employee_day_id bigserial
employee_name varchar(100)
effective_day date
effective_pay
effective_pay_id bigserial
tstamp timestamp
employee_day_id (foreign key)
Then you can calculate end_day off the employee_day table (you can do this
without splitting out the table however splitting the table means fewer
records for this query and a more normalized database):
SELECT
ed1.employee_day_id,
ed1.employee_name,
ed1.effective_day,
case ed2.effective_day
when null then null
else min(ed2.effective_day) - interval '1 day'
end
FROM employee_day ed1
LEFT OUTER JOIN employee_day ed2 ON (
ed1.employee_name = ed2.employee_name
AND
ed1.effective_day < ed2.effective_day
)
Since it appears end_date is a derived value, you can use the above query as
a view. I guess there could be performance concerns but then you could
create a materialized view for it.
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.comhttp://codeelixir.com
==================================================================
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: postgresql@aranya.com, juandelacruz@gmail.com, mike@fuhr.org
Subject: Re: Trigger on Insert to Update only newly inserted fields?
In-Reply-To: <bf05e51c0608310755j3f392dfdm3e6e829129ef908b@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox