Обсуждение: FW: FW: Linking databases.

I don't think either COPY or PG_DUMP will help in this case...

I have a users table in database ADMIN with a primary key user_id. I have a
users_access table in database LIVE which I was hoping to have a foreign key
user_id reference the user_id column in the ADMIN database, so If I deleted
a user it would cascade and delete the users_access records.....

are you saying this is impossible? if so, are their plans for this to be
made a feature in a future release?

many thanks

Geoff


-----Original Message-----
From: omid omoomi [mailto:oomoomi@hotmail.com]
Sent: 11 July 2002 10:55
To: geoff@metalogicplc.com
Subject: Re: [ADMIN] FW: Linking databases.


hi,
AFAIK, it is not possible.
you better try to COPY or PG_DUMP the tables from databaseB to A.

Omid Omoomi

>From: "Geoff" <geoff@metalogicplc.com>
>Reply-To: <geoff@metalogicplc.com>
>To: "Pgsql-Admin (E-mail)" <pgsql-admin@postgresql.org>
>Subject: [ADMIN] FW: Linking databases.
>Date: Thu, 11 Jul 2002 10:22:28 +0100
>
>Sorry if this seems a little trivial, but I can't find any thing in the
>docs
>about using references in seperate db's.
>
>I need to reference columns in databaseA with columns in databaseB... is
>this possible?
>
>I've tried the usual way link  "column integer references
>databaseb.column",
>but just can't find the right syntax...
>
>I'm using 7.2
>
>thanks
>
>geoff
>
>
>
>
>-----------------------------------------------------------------------
>This message is intended only for the person or entity to which it is
>addressed and may contain confidential and/or privileged material. Any
>review, retransmission, dissemination or other use of, or taking of any
>action in reliance upon, this information by persons or entities other than
>the intended recipient is prohibited. If you receive this message in error
>please contact the sender by return e-mail and delete the message from your
>computer.
>
>Any opinions contained in this message are those of the author and are not
>given or endorsed by Metalogic PLC unless otherwise clearly indicated in
>this message and the authority of the author to bind Metalogic is duly
>verified.
>
>Metalogic PLC accepts no liability for any errors or omissions in the
>context of this message which arise as a result of internet transmission.
>-----------------------------------------------------------------------
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/users-lounge/docs/faq.html



_________________________________________________________________
Chat with friends online, try MSN Messenger: http://messenger.msn.com

On Thu, 11 Jul 2002, Geoff wrote:

> I don't think either COPY or PG_DUMP will help in this case...
>
> I have a users table in database ADMIN with a primary key user_id. I have a
> users_access table in database LIVE which I was hoping to have a foreign key
> user_id reference the user_id column in the ADMIN database, so If I deleted
> a user it would cascade and delete the users_access records.....
>
> are you saying this is impossible? if so, are their plans for this to be
> made a feature in a future release?

Not any time soon, there's no real support for making cross db queries
using just the query language right now, and that'd be a prerequisite and
that's not on the near term chart either.  Many of the things that people
historically used multiple databases for may be resolvable using schemas
which is coming up soon (7.3).

Geoff wrote:
> I don't think either COPY or PG_DUMP will help in this case...
>
> I have a users table in database ADMIN with a primary key user_id. I have a
> users_access table in database LIVE which I was hoping to have a foreign key
> user_id reference the user_id column in the ADMIN database, so If I deleted
> a user it would cascade and delete the users_access records.....
>
> are you saying this is impossible? if so, are their plans for this to be
> made a feature in a future release?
>

See contrib/dblink for a possible workaround. Note that if you want to
use it for INSERT/UPDATE/DELETE, you'll have to grab the latest version
out of CVS.

There doesn't seem to be much support for a native cross-database access
among the core developers, so I don't think you'll see that soon. As I
think others have mentioned, the next release (7.3) will support
multiple schemas in one database which might be a solution for you.

HTH,

Joe