Help with views/rules... - Mailing list pgsql-sql

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
nulldate_received | date                           | not nullstaff_id       | integer                        | not
nullsurvey_type   | smallint                       | not nullis_blank       | boolean                        | not
nullbirth_month   | smallint                       |birth_year     | smallint                       |check_ppt      |
boolean                       | not nullcheck_dob      | boolean                        | not nullcheck_tracking |
boolean                       | not nulldate_inserted  | timestamp(0) without time zone | not nulldate_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_idAND
tblpis_survey_receipt.check_ppt='t'ANDtblpis_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_blankFROM tblpis_survey_receiptWHERE ppt_id=new.ppt_idAND
survey_type=1ANDcheck_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_blankFROM tblpis_survey_receiptWHERE 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_idfrom
 
tblpis_tracking);

UPDATE tblpis_trackingSET post_rc_date=new.date_received,  post_rc_id=new.staff_id,
post_is_blank=new.is_blankWHERE 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@gwm.sc.edu) - 803-777-9476 
Database Administrator/Webmaster
Prevention Research Center
University of South Carolina