Message-ID: <431620D5.9080809@brandywine.com>
Date: Wed, 31 Aug 2005 17:27:49 -0400
From: Daryl Richter <daryl@brandywine.com>
Reply-To: daryl@brandywine.com
Organization: Brandywine Asset Management
User-Agent: Mozilla Thunderbird 1.0.6 (Windows/20050716)
MIME-Version: 1.0
To: Henry Ortega <juandelacruz@gmail.com>
Cc: =?ISO-8859-1?Q?Ragnar_Hafsta=F0?= <gnari@simnet.is>,
	pgsql-sql@postgresql.org
Subject: Re: insert only if conditions are met?
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>
In-Reply-To: <2bffcc33050831094957be1b4a@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable

Henry Ortega wrote:
> Ok. Here's TABLE A
>=20
> emp            date             hours       type
> JSMITH       08-15-2005   5             WORK
> JSMITH       08-15-2005   3             WORK
> JSMITH       08-25-2005   6             WORK
>=20
> I want to insert the ff:
> 1.) JSMITH    08-15-2005    8    VAC
> 2.) DOE        08-16-2005    8    VAC
>=20
> #1 should fail because there is already 8 hours entered as being
> Worked on 08-15-2005 (same date).
>=20
> Any suggestions?

CREATE TABLE "tablea" (
	"emp"  	varchar(6) NOT NULL,
	"date" 	varchar(10) NOT NULL,
	"hours"	int NOT NULL,
	"type" 	char(4) NOT NULL
	);

grant select, insert, update, delete on tablea to public;

insert into tablea( emp, date, hours, type ) values( 'JSMITH',=20
'08-15-2005', 5, 'WORK' );
insert into tablea( emp, date, hours, type ) values( 'JSMITH',=20
'08-15-2005', 3, 'WORK' );
insert into tablea( emp, date, hours, type ) values( 'JSMITH',=20
'08-25-2005', 6, 'WORK' );

create or replace function overtime( varchar, varchar, int, varchar )=20
returns void as '
    insert into tablea( emp, date, hours, type )
       select $1, $2, $3, $4
       from tablea where ( select sum( hours ) from tablea where emp =3D =

$1 and date =3D $2 group by emp, date ) + $3 <=3D 8
       union
       select $1, $2, $3, $4
       from tablea where( select sum( hours ) from tablea where emp =3D $=
1=20
and date =3D $2 group by emp, date ) is null
' LANGUAGE SQL;

select overtime( 'JSMITH', '08-15-2005', 8, 'VAC' );	# REJECTED
select overtime( 'JSMITH', '08-16-2005', 8, 'VAC' );    # OK
select overtime( 'JSMITH', '08-25-2005', 2, 'WORK' );	# OK

select * from tablea;

>=20
>=20
>=20
>=20
> On 8/31/05, Ragnar Hafsta=F0 < gnari@simnet.is <mailto:gnari@simnet.is>=
 >
> wrote:
>=20
> On Wed, 2005-08-31 at 11:49 -0400, Henry Ortega wrote:
>=20
>>What I am trying to do is
>>* Insert a record for EMPLOYEE A to TABLE A
>>IF
>>the sum of the hours worked by EMPLOYEE A on TABLE A
>>is not equal to N=20
>>
>>Is this possible?
>=20
>=20
> Sure, given a suitable schema
>=20
> It is not clear to me, if the hours worked are
> to be found in the same table you want to insert
> into, or not.
>=20
> gnari
>=20
>=20
>=20
>=20
>=20
>=20
>=20

--=20
Daryl Richter
Platform Author & Director of Technology
v: 610.361.1000 x202

((         Brandywine Asset Management          )
  ( "Expanding the Science of Global Investing"  )
  (          http://www.brandywine.com           ))