X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 86E89D6E05 for ; Fri, 2 Sep 2005 17:45:01 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 67637-02 for ; Fri, 2 Sep 2005 20:44:58 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.198]) by svr1.postgresql.org (Postfix) with ESMTP id 3B9D6D8040 for ; Fri, 2 Sep 2005 17:44:55 -0300 (ADT) Received: by wproxy.gmail.com with SMTP id i2so195097wra for ; Fri, 02 Sep 2005 13:44:57 -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=sa3EWgFK3orGYahhRsJzvsEy9BKBz+BV42+FIS/PCFL7aUm+wlkN724hkz/Y0yLAN1kqE/hAZOVbmnGEYjZk5AKzscJf3kiq4JKBQSRPxoQ1S4DonU79qipKGxkfKRGPqCSq/6nX9rAcehvZo7ADXB9KrqaekfqEcmU48J/x1rQ= Received: by 10.54.56.56 with SMTP id e56mr2769623wra; Fri, 02 Sep 2005 13:44:57 -0700 (PDT) Received: by 10.54.124.5 with HTTP; Fri, 2 Sep 2005 13:44:57 -0700 (PDT) Message-ID: <2bffcc3305090213441166a53b@mail.gmail.com> Date: Fri, 2 Sep 2005 16:44:57 -0400 From: Henry Ortega To: Philip Hallstrom Subject: Re: insert only if conditions are met? Cc: pgsql-sql@postgresql.org In-Reply-To: <20050831140831.Q5678@wolf.pjkh.com> Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_20313_17700935.1125693897659" References: <2bffcc33050831080942f806e1@mail.gmail.com> <20050831152534.GA18934@winnie.fuhr.org> <2bffcc3305083108487fb80cb2@mail.gmail.com> <2bffcc3305083108497ea6dcc6@mail.gmail.com> <1125506698.2605.22.camel@localhost.localdomain> <2bffcc33050831094957be1b4a@mail.gmail.com> <1125520078.2605.38.camel@localhost.localdomain> <20050831140831.Q5678@wolf.pjkh.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=-0.023 required=5 tests=[AWL=-0.143, HTML_50_60=0.095, HTML_MESSAGE=0.001, RCVD_BY_IP=0.024] X-Spam-Level: X-Archive-Number: 200509/19 X-Sequence-Number: 22566 ------=_Part_20313_17700935.1125693897659 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Thanks for all your answers. Very helpful. What if after adding all those hours in one long transaction, I want to send a query to check the MONTHLY TOTAL HOURS (including those just entered) and if they exceed N number of hours, all those records added should *ROLLBACK*? BEGIN; insert.......... insert......... if sum(hours)>N then ROLLBACK END; Is that possible? Maybe with just plain SQL? (and one transaction) On 8/31/05, Philip Hallstrom wrote: >=20 > > On Wed, 2005-08-31 at 12:49 -0400, Henry Ortega wrote: > >> Ok. Here's TABLE A > >> > >> emp date hours type > >> JSMITH 08-15-2005 5 WORK > >> JSMITH 08-15-2005 3 WORK > >> JSMITH 08-25-2005 6 WORK > >> > >> I want to insert the ff: > >> 1.) JSMITH 08-15-2005 8 VAC > >> 2.) DOE 08-16-2005 8 VAC > >> > >> #1 should fail because there is already 8 hours entered as being > >> Worked on 08-15-2005 (same date). > > > > sorry, did not notice the duplicates before my previous reply. > > > > you could do something like > > insert into A select 'JSMITH','08-15-2005',8,'VAC' > > where > > 8 !=3D (select sum(hours) FROM A > > WHERE emp =3D 'JSMITH' > > AND date =3D '8-15-2005'); >=20 > Wouldn't that fail if JSMITH had only worked 7 hours on 8-15? I'm > guessing he'd still want it to fail since adding that 8 hours ov VAC woul= d > result in a 15 hour day... so maybe something like? >=20 > insert into A select 'JSMITH','08-15-2005',8,'VAC' > WHERE > 8 >=3D 8 + (select sum(hours) FROM A > WHERE emp =3D 'JSMITH' > AND date =3D '8-15-2005'); >=20 > ? > ------=_Part_20313_17700935.1125693897659 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Thanks for all your answers. Very helpful.
What if after adding all those hours in one long transaction,
I want to send a query to check the MONTHLY TOTAL HOURS
(including those just entered)
and if they exceed N number of hours, all those records added
should *ROLLBACK*?

BEGIN;
insert..........
insert.........
  if sum(hours)>N then ROLLBACK
END;

Is that possible? Maybe with just plain SQL? (and one transaction)



On 8/31/05, Philip Hallstrom <postgresql@philip.pjkh.com> wrote:
> On Wed, 2005-08-31 at 12:49 -0400, Henry Ortega wrote:
>> Ok.= Here's TABLE A
>>
>> emp            = date            = ; hours       type
>> JSMITH       08-15-2005   5             WORK
>> JSMITH       08-15-2005   3             WORK
>> JSMITH       08-25-2005   6             WORK
>>
>> I want to insert the ff:
>> 1.) JSMIT= H    08-15-2005    8  &nb= sp; VAC
>> 2.) DOE       &= nbsp;08-16-2005    8    VAC
>= >
>> #1 should fail because there is already 8 hours entered as= being
>> Worked on 08-15-2005 (same date).
>
> sorry, did n= ot notice the duplicates before my previous reply.
>
> you coul= d do something like
> insert into A select 'JSMITH','08-15-2005',8,'V= AC'
>    where
>     =   8 !=3D (select sum(hours) FROM A
>    &nbs= p;            &= nbsp;  WHERE emp =3D 'JSMITH'
>        &nb= sp;           AND date =3D '8-15-2005');

Wouldn't that fail if JSMITH had only worked = 7 hours on 8-15?  I'm
guessing he'd still want it to fail sinc= e adding that 8 hours ov VAC would
result in a 15 hour day... so maybe s= omething like?

insert into A select 'JSMITH','08-15-2005',8,'VAC'
WHERE
8 &g= t;=3D 8 + (select sum(hours) FROM A
      =             &nb= sp;  WHERE emp =3D 'JSMITH'
        &= nbsp;            AND date =3D '8-15-2005');

?

------=_Part_20313_17700935.1125693897659--