Sunday, May 18, 2008

Re: [GENERAL] Link tables from different databases

Reuben D. Budiardja wrote:
> Hello,
> Suppose I have two tables db1.table1 and db2.table2. Is there anyway I can
> actually have db2.table2 a link to db1.table1 ? What I meant is similar to a
> symlink in Unix filesystem (db2.table2 is a symlink to db1.table1).
>
> I want it so that every operation on db1.table1 (ie. INSERT, UPDATE, DELETE)
> also happens to db2.table2, but similarly I want it that application can do
> those operations on db2.table2 but actually it does it on db1.table1 behind
> the scene.
>
> Is there anything like this in postgresql ? I am using version 7.4 by the way.
>
> Thanks in advance for any help.
> RDB

You will get people saying that this is bad design and they will be
right. You should think about your design before going in this direction.

Does this same data belong to both db's? Can the two db's be merged into
one using different schema to separate the data instead of different db's?


There isn't a simple way (like a simple symlink) but dblink in the
contrib folder may help.

To automate the process you will need to add triggers to pass along all
the changes but I think you will find that being able to update the
table in both db's will cause you some headaches (think endless loops).

You may want to have one db with the table and use functions to access
the data from the other db.


--

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

No comments: