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
>