Skip site navigation (1) Skip section navigation (2)

How to use views&rules to dynamically choose which table to update

From: Ashley Moran <work(at)ashleymoran(dot)me(dot)uk>
To: pgsql-general(at)postgresql(dot)org
Subject: How to use views&rules to dynamically choose which table to update
Date: 2006-03-31 23:04:26
Message-ID: 63968D35-E759-401D-8C9D-3B89F9E3CE8D@ashleymoran.me.uk (view raw or flat)
Thread:
Lists: pgsql-general
I'm still relatively new to Postgres (at least when it comes to  
clever stuff - especially rules) so I hope I've missed something here.

Basically I'm still trying to combine multiple databases with  
identical schemas into one schema, adding a column to each table to  
indicate which schema it came from.  (I'm prototyping an app in Ruby  
on Rails so I want to have only one set of model classes, instead of  
5).  So I have views defined like this:

     SELECT 'schema1'::varchar(10), * from schema1.table1
     UNION ALL
     SELECT 'schema2'::varchar(10), * from schema2.table1

etc...

These tables are all from a data feed we pay for, and is updated  
nightly.  It is separate from my application database.

Now, I want to take advantage of Rails' unit tests on these tables,  
because I need to simulate changes in the data feed.  So I thought  
maybe I could add rules to the views, so Rails can load its test  
fixtures into the model I defined and not realise it is feeding  
multiple back-end tables.

This is my effort in a test database, so you can see what I'm trying  
to do:

     CREATE SCHEMA english;
      CREATE TABLE english."names" (
         id serial NOT NULL PRIMARY KEY,
         name character varying(50)
     );

     CREATE SCHEMA french;
     CREATE TABLE french."names" (
         id serial NOT NULL PRIMARY KEY,
         name character varying(50)
     );

     CREATE VIEW "names" AS
         SELECT ('english'::character varying)::character varying(20)  
AS "language", * FROM english."names";
     UNION ALL
         SELECT ('french'::character varying)::character varying(20)  
AS "language", * FROM french."names";


     CREATE RULE insert_english AS
     ON INSERT TO "names"
     WHERE (((new."language")::character varying(20))::text =
           (('english'::character varying)::character varying 
(20))::text)
     DO INSTEAD INSERT INTO english."names" (name) VALUES (new.name);

     CREATE RULE insert_french AS
     ON INSERT TO "names"
     WHERE (((new."language")::character varying(20))::text =
           (('french'::character varying)::character varying(20))::text)
     DO INSTEAD INSERT INTO french."names" (name) VALUES (new.name);

(Please forgive any mistakes above - I cobbled it together from a  
backup file)

Now if I some french names and some english names into the relvant  
tables, the view works fine on SELECT, but on INSERT I get this error:

     ERROR:  cannot insert into a view
     HINT:  You need an unconditional ON INSERT DO INSTEAD rule.

Which suggests that what I want to do is impossible.  Does anyone  
know of a way to do this?  If I can do it in the database I can  
probably save hours of hacking the unit tests in Rails.

Thanks
Ashley

Responses

pgsql-general by date

Next:From: Bradley W. DuttonDate: 2006-03-31 23:12:12
Subject: pg_hba.conf errors
Previous:From: Ted ByersDate: 2006-03-31 22:49:35
Subject: Re: [Slightly OT] data model books/resources?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group