Re: How to use views&rules to dynamically choose which

On Sat, 1 Apr 2006, Ashley Moran wrote:

> 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);


What should it do if you try to insert something that is neither french
nor english? I think an unconditional instead nothing rule might work
to supplement the two conditional ones if doing nothing is okay, but I
haven't tried.



>
> (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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>