From: Peter Bense <Ptbense@gwm.sc.edu>
To: pgsql-sql@postgresql.org
Subject: Help with views/rules...
Date: Wed, 18 May 2005 13:40:50 -0400
Message-ID: <s28b45e9.005@gwm.sc.edu> (raw)
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@gwm.sc.edu) - 803-777-9476
Database Administrator/Webmaster
Prevention Research Center
University of South Carolina
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: Ptbense@gwm.sc.edu
Subject: Re: Help with views/rules...
In-Reply-To: <s28b45e9.005@gwm.sc.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox