Return-Path: pg_adm@postgres.berkeley.edu
Received: by postgres.Berkeley.EDU (5.61/1.29)
	id AA23649; Thu, 30 Jul 92 10:57:48 -0700
Message-Id: <9207301757.AA23649@postgres.Berkeley.EDU>
From: Spyros Potamianos <potamian@hplsjps.hpl.hp.com>
Subject: Re: rules
To: postgres@postgres.berkeley.edu
Sender: pg_adm@postgres.berkeley.edu
To: SBYATES%PUCC.BITNET@cmsa.Berkeley.EDU (Stan Yates)
Date: Thu, 30 Jul 92 11:07:05 PDT
Cc: postgres@postgres.Berkeley.EDU
In-Reply-To: <9207301714.AA23046@postgres.Berkeley.EDU>; from "Stan Yates" at Jul 30, 92 10:14 am
Mailer: Elm [revision: 70.30]

> 
> Can you explain the following situation?  The following two relations are
> created and populated:
> 
> Query sent to backend is "create records(recno=int4,record=text)"
> 
> Query sent to backend is "create title(recno=int4,tag=char16,value=text)"
> 
> [.......... stuff deleted .............]
>
> Then the following rule is defined:
> 
> Query sent to backend is "define rule title2rec is on retrieve to title.recno do
> instead retrieve (records.record) where records.recno=current.recno"
> 

The problem with this rule is that 'title.recno' is an 'int4' and
'records.record' is 'text'. So, when the rule is activated at query
execution time, it tries to replace an integer with a text.
Unfortunatelly type checking in the rule manager leaves a lot to be
desired, so the system tries to use the text value as an integer,
and of course strange things started happening!!!

However, it is possible to use a "rewrite" rule:

	define rewrite rule title2rec is
	on retrieve to title.recno
	do instead retrieve (records.record)
	where records.recno=current.recno"

The advantage is that the rule processing happens before an execution plan
is created, and the system knows what types of data to expect.
However under the current implementation of the query rewrite rule system
you will not get any tuples of 'title' that have no corresponding entry
in 'records' (i.e. when the join 'records.recno=current.recno' fails)

> What I want to do is be able to do a search on the value in the title relation
> (eventually the format of this value will be refined and it will be indexed)
> and then retrieve the corresponding complete record.  Obviously this could
> be done using a join, but I am experimenting with rules to see if that is
> more efficient.  In any case, I would like to get the rule to work.  Have
> I defined it incorrectly?
> 
> Thanks for any assistance.

I would suggest to either use a good old-fashioned join, or define an extra
dummy attribute on 'title':

    create title(recno=int4,tag=char16,value=text, rec=text)

and define a rule:

	define rule title2rec is
	on retrieve to title.rec
	do instead retrieve (records.record)
	where records.recno=current.recno"

(thus you are replacing the value of a 'text' attribute with the value of
another 'text' attribute, and you won't have problems with type mismatches)

Hope that helps!
Spyros Potamianos
potamian@hpl.hp.com