On Thu, 22 Aug 2002, Andreas Tille wrote:
> I want to solve the following problem:
>
> CREATE TABLE Ref ( Id int ) ;
> CREATE TABLE Import ( Id int,
> Other varchar(42),
> Flag int,
> Ts timestamp ) ;
> CREATE TABLE Data ( Id int,
> Other varchar(42) ) ;
>
> The table Import will be filled by a COPY FROM statement and contains
> no checks for referential integrity. The columns Id and Other have to
> be moved to the table Data if the table Ref contains the Id. If not
> Flag should get a certain value that something went wrong. Moreover
> Import should only contain one representation of a dataset with equal
> Id and Other column and I would like to store the newest one (this
> is the reason for the timestamp).
> ...
While I fiddled around with my previous solution I found out that perhaps
an updatable view could solve my problem. Thus I tried to implement:
CREATE TABLE ViewImport ( Id int, Other varchar(42), Flag
int, Ts timestamp ) ; CREATE RULE "_RETviewimport" AS ON SELECT TO ViewImport DO
INSTEAD SELECT i.* FROM Import i INNER JOIN Ref r ON i.Id = r.Id;
Now I wonder if it is possible to find a clever rule for update which
contains the JOIN I placed into this working example to set the Flag
just right now for the appropriate data sets.
Any help for this approach or any other solution for this problem?
Kind regards
Andreas.
