Re: inserting to a multi-table view

On Mon, Jun 16, 2008 at 8:32 PM, Michael Shiulman <shulman(at)mathcamp(dot)org> wrote:

> No, I don't think table inheritance will help. My actual situation is
> somewhat more complicated: the view takes data from more than two
> tables with a many-to-one rather than one-to-one relationship. For
> instance, consider tables "person", "address", and "phone", with a
> view "person_with_contact_info" that joins a person with their primary
> address and phone number, while inserting to the view should insert a
> person along with an address and phone number. In that case there is
> no table that can inherit from the other to solve the problem.

Ya, I agree. Postgresql inheritance is really just fancy horizontal
partitioning with a built-in UNION ALL. As far as I know, it still
doesn't support referential integrity (i.e. foreign keys from the
sub-tables). Building your own vertically partitioned schema will
fix many of the referential integrity problems, but at the expense of
opening your self up for view update anomoloies (I wished that the
postgresql update rules where executed as serializable transactions,
that way if one of the joined tables in the view was updated during
your change, it would though an exception rollback your update instead
of writing over the other persons work.)

Anyway, here is a link discussing a generalized vertical partitioned
view. Perhaps it can give you some idea to get yourself rolling.
http://archives.postgresql.org/pgsql-general/2006-12/msg01119.php

--
Regards,
Richard Broersma Jr.

Visit the Los Angles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug