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

Поиск
Список
Период
Сортировка
От Ashley Moran
Тема How to use views&rules to dynamically choose which table to update
Дата
Msg-id 63968D35-E759-401D-8C9D-3B89F9E3CE8D@ashleymoran.me.uk
обсуждение исходный текст
Ответы Re: How to use views&rules to dynamically choose which table to update  (David Fetter <david@fetter.org>)
Re: How to use views&rules to dynamically choose which  (Stephan Szabo <sszabo@megazone.bigpanda.com>)
Список 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

В списке pgsql-general по дате отправления:

Предыдущее
От: "Ted Byers"
Дата:
Сообщение: Re: [Slightly OT] data model books/resources?
Следующее
От: David Fetter
Дата:
Сообщение: Re: How to use views&rules to dynamically choose which table to update