Message-Id: <s28b45e9.005@gwm.sc.edu>
Date: Wed, 18 May 2005 13:40:50 -0400
From: "Peter Bense" <Ptbense@gwm.sc.edu>
To: <pgsql-sql@postgresql.org>
Subject: Help with views/rules...
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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