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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Bradley W. Dutton | 2006-03-31 23:12:12 | pg_hba.conf errors |
Previous Message | Ted Byers | 2006-03-31 22:49:35 | Re: [Slightly OT] data model books/resources? |