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

On Sat, Apr 01, 2006 at 12:04:26AM +0100, 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);

Wow. That's confusing. How about using table partitioning for this?

<http://www.postgresql.org/docs/current/static/ddl-partitioning.html>

Cheers,
D
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!