My MS-Access problem keeps getting weirder and weirder... - Mailing list pgsql-odbc

I want to thank everyone who has provided suggestions regarding my
MS-Access / ODBC / Link-tables issue the past couple of days.

Here's what I've found:

1. As my gut instincts had told me, there is no problem with the
translations of booleans, at least given how I have been using them
(with foreign-key lookups).

2. Inserts into the following view work cleanly:

afl=# \d vi_tblpis_survey_receipt
View "public.vi_tblpis_survey_receipt"
    Column     |   Type   | Modifiers
---------------+----------+-----------
 ppt_id        | integer  |
 date_received | date     |
 staff_id      | integer  |
 survey_type   | smallint |
 is_blank      | boolean  |
 birth_month   | smallint |
 birth_year    | smallint |
View definition:
 SELECT tblpis_survey_receipt.ppt_id,
tblpis_survey_receipt.date_received, tblpis_survey_receipt.staff_id,
tblpis_survey_receipt.survey_type, tblpis_survey_receipt.is_blank,
tblpis_survey_receipt.birth_month, tblpis_survey_receipt.birth_year
   FROM tblpis_survey_receipt
  ORDER BY tblpis_survey_receipt.insertion;

3. THINGS BREAK WHEN I APPLY CERTAIN TYPES OF RULES TO THE VIEW.

The way this view is supposed to work is as follows:
A) - A data entry person enters participant ID, survey type, date
received, etc.
B) - A RULE performs the following insertion:
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());

C) - A series of checks are conducted to ensure that this data is
valid.  Basically 4 or 5 updates are run to toggle these boolean fields
on the PostgreSQL side.  If the participant ID is a valid participant
ID, that field is toggled.  Once it passes that field, the month and
year of birth are verified.  If that check is successful, then it checks
to see whether or not there is an associated tracking record already.
If there isn't, it passes the tracking check.

All of those steps work fine on my test inserts, and the datavalues are
toggled accordingly.  So far so good.

WHEN I TRY TO INSERT DATA INTO SOME OTHER TABLE FROM THAT RULE, THINGS
BREAK!

As soon as I add an INSERT statement to my rule following the UPDATEs,
e.g.
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';
);

... Things break upon insert.

Why?

Can I only perform one insert per AS ON INSERT TO?

If so, this might be the cause of my problem.


Help!

./Peter

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