X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 542D79FB3CD for ; Thu, 31 Aug 2006 11:57:51 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 44365-02 for ; Thu, 31 Aug 2006 14:57:42 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from py-out-1112.google.com (py-out-1112.google.com [64.233.166.181]) by postgresql.org (Postfix) with ESMTP id D5F5E9FA646 for ; Thu, 31 Aug 2006 11:56:04 -0300 (ADT) Received: by py-out-1112.google.com with SMTP id d80so764592pyd for ; Thu, 31 Aug 2006 07:55:30 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=oB4rNM135Vnw7g7N4h6TmjshNOrOm6Feyg/Yp2CHzFUXUuJbqHZ4ldKU2BWNM0RpvnRJZcftMyzdfUCJT/7gJODypp1IPVNHlhDnmS01GS1sbYPYCk4WJvRHkq13fzRJWV/QVEY456amDi81l3sYss3NtRvP+AJNJrItoIwLz9M= Received: by 10.35.103.1 with SMTP id f1mr870374pym; Thu, 31 Aug 2006 07:55:30 -0700 (PDT) Received: by 10.35.80.9 with HTTP; Thu, 31 Aug 2006 07:55:29 -0700 (PDT) Message-ID: Date: Thu, 31 Aug 2006 09:55:30 -0500 From: "Aaron Bono" To: "Henry Ortega" Subject: Re: Trigger on Insert to Update only newly inserted fields? Cc: "Michael Fuhr" , pgsql-sql@postgresql.org In-Reply-To: <2bffcc330608280853x50b1034nca246a5dcc935e7@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_54460_24582064.1157036129981" References: <2bffcc330608280702y16c25090i816589e20163e7e8@mail.gmail.com> <20060828145737.GA5004@winnie.fuhr.org> <2bffcc330608280853x50b1034nca246a5dcc935e7@mail.gmail.com> X-Google-Sender-Auth: b90806faf4c34575 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.2 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE, HTML_MESSAGE, SPF_PASS X-Spam-Level: X-Archive-Number: 200608/249 X-Sequence-Number: 26207 ------=_Part_54460_24582064.1157036129981 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 8/28/06, Henry Ortega 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.com http://codeelixir.com ================================================================== ------=_Part_54460_24582064.1157036129981 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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.com
   http://codeelixir.com
================================================================== ------=_Part_54460_24582064.1157036129981--