Return-Path: pg_adm@postgres.berkeley.edu
Received: by postgres.Berkeley.EDU (5.61/1.29)
	id AA03696; Thu, 21 Jan 93 08:56:10 -0800
Date: Thu, 21 Jan 93 08:56:10 -0800
Message-Id: <9301211656.AA03696@postgres.Berkeley.EDU>
From: mcquaig!postgres@uunet.UU.NET (Postgres System User)
Subject: where clause in rewrite rules
To: postgres@postgres.berkeley.edu
Sender: pg_adm@postgres.berkeley.edu


The problem at hand is one of referential integrity.  The idea is as
follows: On append to a relation check for a value in a second
relation based on a foreign key attribute.  If the key is not found
retrieve an error message from an error message relation instead. (If
there is a better way to approach the referential integrity problem in
general, that would be nice to know in addition to the why the scheme
below does no work.  Or worse, does this work on UCB's system?)
In the commands below, using a rewrite rule does not work but does not
give and error.  If the rule is changed to a prs2 rule, I get the
error: WARN:Jan 21 10:31:35:View Rules must not have parameters!

These commands can be fed to the monitor to create the entire
scenario. 

/*******************************************************************/
/* create and initialize a master relation */
create inventory (
	RMId = text,		/* Raw Material Identifier */
	Grade = text)		/* Grade of Material */
archive = light
store = "magnetic disk"\g

define index inven_i on inventory using btree (RMId text_ops)\g
define index invengrd_i on inventory using btree (Grade text_ops)\g

append inventory ( RMId="Some good spec"::text, Grade="mygrade")\g

/*******************************************************************/
/* create and initialize an error message relation */
create integerror (
	Class = text,	/* relation name where error occurred */
	Attr = text,	/* the attribute not found */
	ErrCode = int4)	/* whatever for whatever purpose */
archive = none
store = "magnetic disk"\g

define index integerror_i on integerror using btree (Class text_ops)\g

append integerror (Class="inxfer", Attr="RMId", ErrCode="1")\g

/******************************************************************/
/* create a transfer detail relation */
create inxfer (
	RMId = text,		/* Raw Material Identifier */
	Grade = text,		/* Grade of Material */
	Weight = float8)	/* Weight being transferred */
archive = heavy
store = "magnetic disk"\g

/******************************************************************/
/* setup the integrity rule */
/* NOTE: the idea would to replace "Some bad spec" below with a */
/* function to do the look up. But this doesn't even work as yet */

define rewrite rule inxfer_integ is
on append to inxfer where new.RMId = "Some bad spec"::text
do instead
retrieve (integerror.all) where integerror.Class = "inxfer" and integerror.Attr = "RMId"
\g

/******************************************************************/

append inxfer ( RMId="Some good spec"::text, Grade="mygrade", Weight=10000)\g
append inxfer ( RMId="Some bad spec"::text, Grade="mygrade", Weight=10000)\g

retrieve (inxfer.oid, inxfer.all)\g

-------------------------------------------------------------------
THE RESULTS:

$ createdb nmmtest
$ monitor -T nmmtest
Welcome to the C POSTGRES terminal monitor

Go 
* \i Mail.commands

Query sent to backend is " create inventory ( 	RMId = text,		 	Grade = text)		 archive = light store = "magnetic disk""
CREATE
Query sent to backend is "define index inven_i on inventory using btree (RMId text_ops)"
DEFINE
Query sent to backend is "define index invengrd_i on inventory using btree (Grade text_ops)"
DEFINE
Query sent to backend is "append inventory ( RMId="Some good spec"::text, Grade="mygrade")"
APPEND 45027
Query sent to backend is " create integerror ( 	Class = text,	 	Attr = text,	 	ErrCode = int4)	 archive = none store = "magnetic disk""
CREATE
Query sent to backend is "define index integerror_i on integerror using btree (Class text_ops)"
DEFINE
Query sent to backend is "append integerror (Class="inxfer", Attr="RMId", ErrCode="1")"
APPEND 45082
Query sent to backend is "  create inxfer ( 	RMId = text,		 	Grade = text,		 	Weight = float8)	 archive = heavy store = "magnetic disk""
CREATE
Query sent to backend is "     define rewrite rule inxfer_integ is on append to inxfer where new.RMId = "Some bad spec"::text do instead retrieve (integerror.all) where integerror.Class = "inxfer" and integerror.Attr = "RMId" "
APPEND 45182DEFINE
Query sent to backend is "  append inxfer ( RMId="Some good spec"::text, Grade="mygrade", Weight=10000)"
 Class        Attr         ErrCode     
APPEND 0
Query sent to backend is "append inxfer ( RMId="Some bad spec"::text, Grade="mygrade", Weight=10000)"
 Class        Attr         ErrCode     
 inxfer       RMId         1           
APPEND 0
Query sent to backend is "retrieve (inxfer.oid, inxfer.all)"
 oid          RMId         Grade        Weight      

Go 
* \q
I live to serve you.
I doubt it :-}

Thanks for the help in advance.
nmm

Neil M. McQuaig, III
344 Millicent Way
Shreveport, LA  71106
VOICE: (318)868-5611 
UUCP : uunet!mcquaig!nmm