Help with views/rules...

I've got this funky problem. Basically I have a table that contains:

afl=# \d tblpis_survey_receipt
Table
"public.tblpis_survey_receipt"
Column | Type |
Modifiers
----------------+--------------------------------+------------------------------------------------------------------------------
insertion | integer | not null default
nextval('public.tblpis_survey_receipt_insertion_seq'::text)
ppt_id | integer | not null
date_received | date | not null
staff_id | integer | not null
survey_type | smallint | not null
is_blank | boolean | not null
birth_month | smallint |
birth_year | smallint |
check_ppt | boolean | not null
check_dob | boolean | not null
check_tracking | boolean | not null
date_inserted | timestamp(0) without time zone | not null
date_modified | timestamp(0) without time zone | not null

The goal of this table is to provide a location for staff members to
enter information relevant to the tracking of participant surveys.
They'll have a form where they'll enter this basic data:

ppt_id, date_received, survey_type, is_blank, birth_month, birth_year.

THEN [the part where I'd need the rule thing working] what should
happen is this.

The above 6 fields are entered into a view, which then inserts 'f'
values for the "check" fields by default into the table.

Then [also as part of the rule attached to the view] we perform
checks:

1. check_ppt looks to the participant table to make sure the ppt_id is
valid. If valid, then:
2. check_dob occurs, which verifies that the month and year of birth
entered by the data entry person matches the respective fields in the
parent record. Simple enough.
3. check_tracking looks to the tracking table to ensure that there
hasn't already been a record created or field populated for that type of
survey in that participant's tracking record. If this is okay, then
[and this is the part where things get weird]:

A) We should insert records into the tracking table where there isn't
one already.
B) We should update tracking records where a record exists but there
isn't an entry for that type of survey date received.

Everything works fine until I get to A & B. If I enter these as
inserts via psql, they work fine.

Problem is, I am using MS-Access, which returns some really weird-ass
error message about the data entered being too large for the field... if
I include more than one `INSERT` in the rule?

Here's my rule [hopefully someone can help advise of a more elegant way
to do this?]

CREATE OR REPLACE RULE tblpis_survey_receipt_in AS
ON INSERT TO vi_tblpis_survey_receipt
DO INSTEAD
(
INSERT INTO tblpis_survey_receipt (ppt_id, date_received, staff_id,
survey_type, is_blank, birth_month, birth_year, check_ppt, check_dob,
check_tracking, date_inserted, date_modified)
VALUES (new.ppt_id, new.date_received, new.staff_id, new.survey_type,
new.is_blank, new.birth_month, new.birth_year, 'f', 'f', 'f', now(),
now());

UPDATE tblpis_survey_receipt SET check_ppt='t'
WHERE tblpis_survey_receipt.ppt_id IN
(SELECT ppt_id FROM tblpis_participant);

UPDATE tblpis_survey_receipt SET check_dob='t'
WHERE tblpis_survey_receipt.ppt_id=new.ppt_id
AND tblpis_survey_receipt.check_ppt='t'
AND tblpis_survey_receipt.ppt_id IN
(select ppt_id FROM
tblpis_participant
WHERE
tblpis_survey_receipt.ppt_id=tblpis_participant.ppt_id
AND
tblpis_survey_receipt.birth_month=tblpis_participant.birth_month
AND
tblpis_survey_receipt.birth_year=tblpis_participant.birth_year
);

UPDATE tblpis_survey_receipt SET check_tracking='t'
WHERE tblpis_survey_receipt.ppt_id NOT IN
(SELECT ppt_id from tblpis_tracking);

UPDATE tblpis_survey_receipt SET check_tracking='t'
WHERE tblpis_survey_receipt.survey_type='1' AND
tblpis_survey_receipt.ppt_id NOT IN
(SELECT ppt_id from tblpis_tracking where pre_rc_date IS
NOT NULL);

UPDATE tblpis_survey_receipt SET check_tracking='t'
WHERE tblpis_survey_receipt.survey_type='2' AND
tblpis_survey_receipt.ppt_id NOT IN
(SELECT ppt_id from tblpis_tracking where post_rc_date
IS NOT NULL);

INSERT INTO tblpis_tracking (ppt_id, pre_rc_date, pre_rc_id,
pre_is_blank)
SELECT ppt_id, date_received, staff_id, is_blank
FROM tblpis_survey_receipt
WHERE ppt_id=new.ppt_id
AND survey_type=1
AND check_ppt='t'
AND check_dob='t'
AND check_tracking='t';

INSERT INTO tblpis_tracking (ppt_id, post_rc_date, post_rc_id,
post_is_blank)
SELECT ppt_id, date_received, staff_id, is_blank
FROM tblpis_survey_receipt
WHERE ppt_id=new.ppt_id
AND survey_type=2
AND check_ppt='t'
AND check_dob='t'
AND check_tracking='t'
AND new.ppt_id NOT IN (select ppt_id from
tblpis_tracking);

UPDATE tblpis_tracking
SET post_rc_date=new.date_received, post_rc_id=new.staff_id,
post_is_blank=new.is_blank
WHERE new.ppt_id IN
(SELECT ppt_id
FROM tblpis_survey_receipt
WHERE ppt_id=new.ppt_id
AND survey_type=2
AND check_ppt='t'
AND check_dob='t'
AND check_tracking='t');
);

Peter T. Bense - Teradata Certified Professional
(ptbense(at)gwm(dot)sc(dot)edu) - 803-777-9476
Database Administrator/Webmaster
Prevention Research Center
University of South Carolina