Thread: Link tables from different databases

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
--
Reuben D. Budiardja

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

> 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.

If the tables could be in the same database but in different schemas,
you should be able to arrange a view in schema2 (called "table2") with
rules that operate on table1 in schema1. See the manual for a discuss of
rules and views.

If you really want these tables in separate *databases* (same machine or
not), then you probably want dblink, a stiff drink, and a long-hard
think about the prudence of such a design.

-Reece

--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0