[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-sql
Subject:    Re: [SQL] insert only if conditions are met?
From:       Daryl Richter <daryl () brandywine ! com>
Date:       2005-08-31 21:27:49
Message-ID: 431620D5.9080809 () brandywine ! com
[Download RAW message or body]

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).
> 
> 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', 
'08-15-2005', 5, 'WORK' );
insert into tablea( emp, date, hours, type ) values( 'JSMITH', 
'08-15-2005', 3, 'WORK' );
insert into tablea( emp, date, hours, type ) values( 'JSMITH', 
'08-25-2005', 6, 'WORK' );

create or replace function overtime( varchar, varchar, int, varchar ) 
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 = 
$1 and date = $2 group by emp, date ) + $3 <= 8
       union
       select $1, $2, $3, $4
       from tablea where( select sum( hours ) from tablea where emp = $1 
and date = $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;

> 
> 
> 
> 
> On 8/31/05, Ragnar Hafstaš < gnari@simnet.is <mailto:gnari@simnet.is> >
> wrote:
> 
> On Wed, 2005-08-31 at 11:49 -0400, Henry Ortega wrote:
> 
>>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 
>>
>>Is this possible?
> 
> 
> Sure, given a suitable schema
> 
> 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.
> 
> gnari
> 
> 
> 
> 
> 
> 
> 

-- 
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           ))



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

[prev in list] [next in list] [prev in thread] [next in thread]