X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 4F6A69FCFFD for ; Mon, 28 Aug 2006 12:53:51 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 69495-03 for ; Mon, 28 Aug 2006 12:53:37 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.184]) by postgresql.org (Postfix) with ESMTP id 749F99FCFFA for ; Mon, 28 Aug 2006 12:53:37 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id l36so1656001nfa for ; Mon, 28 Aug 2006 08:53:36 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=CoVsJsebcZYLdrsJRRYodMEa1qdVPE7V7abTIcuhzMp0R5XTXTZAIW6OkqeDrR8LyqWQdtkQ5zNQaRaP2xc8H2B12C6R2NF2mAiyIg4bLe3XjedpiZa6cq+1LpTX7rf0+NEcvBIeR2oQZ3WkE65fnJx4babF88bi7MFlytCUqdQ= Received: by 10.49.29.3 with SMTP id g3mr9157278nfj; Mon, 28 Aug 2006 08:53:36 -0700 (PDT) Received: by 10.48.243.12 with HTTP; Mon, 28 Aug 2006 08:53:36 -0700 (PDT) Message-ID: <2bffcc330608280853x50b1034nca246a5dcc935e7@mail.gmail.com> Date: Mon, 28 Aug 2006 11:53:36 -0400 From: "Henry Ortega" To: "Michael Fuhr" Subject: Re: Trigger on Insert to Update only newly inserted fields? Cc: pgsql-sql@postgresql.org In-Reply-To: <20060828145737.GA5004@winnie.fuhr.org> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_48049_7973972.1156780416105" References: <2bffcc330608280702y16c25090i816589e20163e7e8@mail.gmail.com> <20060828145737.GA5004@winnie.fuhr.org> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.354 tagged_above=0 required=5 tests=AWL, HTML_50_60, HTML_MESSAGE, SPF_PASS X-Spam-Level: X-Archive-Number: 200608/223 X-Sequence-Number: 26181 ------=_Part_48049_7973972.1156780416105 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 The reason some of the end_date is NULL is because it is the latest record in table for that particular employee. My Trigger: CREATE FUNCTION updated_end_date() RETURNS trigger AS ' BEGIN update table set end_date=(select effective-1 from table t2 where t2.employee=table.employee and t2.effective>table.effective order by t2.effective limit 1); RETURN NEW; END; ' LANGUAGE 'plpgsql'; That updates ALL of the records in the table which takes so long. Should I be doing things like this? Or is the update query on my trigger function so wrong? On 8/28/06, Michael Fuhr wrote: > > On Mon, Aug 28, 2006 at 10:02:32AM -0400, Henry Ortega wrote: > > I have a On Insert Trigger that updates one of the columns in that same > > table. > > > > Is there a way for the trigger to run only for the newly inserted > records? > > Instead of all records in the database? > > Row-level INSERT and UPDATE triggers run only for the rows being > inserted or updated. What are you doing that suggests otherwise? > > > E.g.: > > ID Start_Date End_Date > > 001 08-01-2006 > > 002 08-02-2006 > > > > On Insert/Update, Update End_Date=now(). > > I want that to run only on new records.or the updated > > record. How can I do this? > > Row-level BEFORE triggers can modify the row they're processing -- > is that what you're looking for? Something like this? > > CREATE FUNCTION trigfunc() RETURNS trigger AS $$ > BEGIN > NEW.end_date := current_date; > RETURN NEW; > END; > $$ LANGUAGE plpgsql; > > If that's not what you mean then please elaborate. > > -- > Michael Fuhr > ------=_Part_48049_7973972.1156780416105 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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

The reason some of the end_date is NULL is because it is the latest record in
table for that particular employee.

My Trigger:
CREATE FUNCTION updated_end_date() RETURNS trigger AS '
BEGIN
    update table set end_date=(select effective-1 from table t2 where t2.employee=table.employee and t2.effective>table.effective order by t2.effective limit 1);
    RETURN NEW;
END;
' LANGUAGE 'plpgsql';

That updates ALL of the records in the table which takes so long.
Should I be doing things like this? Or is the update query on my trigger function so wrong?



On 8/28/06, Michael Fuhr <mike@fuhr.org> wrote:
On Mon, Aug 28, 2006 at 10:02:32AM -0400, Henry Ortega wrote:
> I have a On Insert Trigger that updates one of the columns in that same
> table.
>
> Is there a way for the trigger to run only for the newly inserted records?
> Instead of all records in the database?

Row-level INSERT and UPDATE triggers run only for the rows being
inserted or updated.  What are you doing that suggests otherwise?

> E.g.:
> ID      Start_Date     End_Date
> 001   08-01-2006
> 002   08-02-2006
>
> On Insert/Update, Update End_Date=now().
> I want that to run only on new records.or the updated
> record. How can I do this?

Row-level BEFORE triggers can modify the row they're processing --
is that what you're looking for?  Something like this?

CREATE FUNCTION trigfunc() RETURNS trigger AS $$
BEGIN
    NEW.end_date := current_date;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

If that's not what you mean then please elaborate.

--
Michael Fuhr

------=_Part_48049_7973972.1156780416105--