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@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666
                              Skype: davidfetter

Remember to vote!