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

Hi David

On Apr 01, 2006, at 12:13 am, David Fetter wrote:
> Wow. That's confusing.

You're telling me! :D

> How about using table partitioning for this?
>
> <http://www.postgresql.org/docs/current/static/ddl-partitioning.html>

Thanks for your suggestion. I've had a look at partitioning but the
problem is this:

Every night we get a new set of data relating to cars (makes, models,
prices, options etc) and the same for vans, and one day bikes, wagons
and caravans. Each has an identical schema, so I want to treat all
the data as coming from the same source. BUT - the primary keys used
in the tables are not unique across all the datasets. So for
example, as Ford Focus (car) might have the same ID as an Iveco Daily
(van). I think this alone precludes table partitioning (nice feature
though! I bet it's useful for really heavyweight databases).

Now the current application is written in C# and NHibernate (yuk) so
to avoid duplicating the already sprawling code and configuration, I
had the idea of creating a view to UNION all the data from the
different datasets and prepend a column to distinguish which class of
vehicle it relates to. So a row would be identified ('car', 203) to
distinguish it from the van/bike/spaceship with id 203.

The rewrite I'm proposing will be in Ruby. Unit tests in Ruby on
Rails have a habit of just chucking data at the table it thinks wants
it. So I figured I could reverse the behaviour of the view to let me
insert data into the individual tables, and my app would not realise
it was using multiple tables to fulfil the query (data in or out)
using a single model class.

Maybe I will need to do something really arcane - I could perhaps
dynamically generate classes in my app to use to load the test data,
but that would involved poring over the Rails source to see how
everything works. I was hoping there would be a nice simple (oh I
laugh now) way of doing things in Postgres itself. Right now, I
don't know which approach is more mind-bending!

Regards
Ashley