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

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

Responses

Browse pgsql-general by date

  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?