This is what I have been reflecting on for awhile. I spoke to some individuals in #postgresql about the possibility of using a trigger, stored procedure, or something like that. [Which honestly I have no experience in implementing.]
The rules method seems to be the most straightfoward implementation, however * but perhaps that's a false presumption? As I understand it a rule is a type of procedure in and of itself. Is this understanding correct? If so, what would prevent me from being able to execute more than one INSERT per rule?
Peter T. Bense - Teradata Certified Professional
(ptbense(at)gwm(dot)sc(dot)edu) - 803-777-9476
Prevention Research Center
University of South Carolina
>>> "Greg Campbell" <greg(dot)campbell(at)us(dot)michelin(dot)com> 5/17/2005 5:24:14 PM >>>
I think of the activity you described as TRIGGER events, more than rules.
It sound a bit complicated, like you have to avoid a series of foreign
key violations or loops or FOREIGNS where the PRIMARY side is NOT
This situation still sound like a DATA problem with a value exceeding
the BOUNDS of what a field will hold.
Have you tried DEBUGGING with a simplistic version where each action is
done as separate steps? (I suppose if the rule are in place you have to
redo you database a bit to test this.) .... I guess you have,...that's
what your message says.
Is it possible to use the client to send each step as a statement, all
wrapped in a transaction?
Is it possible to write a function (stored procedure) to take care of
the combination of steps?
Peter Bense wrote:
> 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(),
> 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
> As soon as I add an INSERT statement to my rule following the UPDATEs,
> INSERT INTO tblpis_tracking (ppt_id, pre_rc_date, pre_rc_id,
> 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.
> Can I only perform one insert per AS ON INSERT TO?
> If so, this might be the cause of my problem.
pgsql-odbc by date
|Next:||From: Peter Bense||Date: 2005-05-18 18:56:34|
|Subject: I hate MS-Access & ODBC.|
|Previous:||From: Zoltan Boszormenyi||Date: 2005-05-17 22:26:48|
|Subject: Re: PsqlODBC problem with complex query|