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
Re: How to use views&rules to dynamically choose which |
Список | 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 по дате отправления: