From MGoldner@agmednet.com Thu Aug 9 02:49:29 2007 Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 2DF7C9FB80B for ; Wed, 8 Aug 2007 23:49:29 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 22523-05 for ; Wed, 8 Aug 2007 23:49:15 -0300 (ADT) X-Greylist: delayed 00:12:05.553131 by SQLgrey-1.7.5 Received: from boston.agmednet.com (boston.agmednet.com [74.92.47.82]) by postgresql.org (Postfix) with ESMTP id 392299FBFAB for ; Wed, 8 Aug 2007 23:48:54 -0300 (ADT) X-MimeOLE: Produced By Microsoft Exchange V6.5 Received: from 71.233.123.244 ([71.233.123.244]) by agmednet-dc.boston.agmednet.local ([192.168.67.2]) with Microsoft Exchange Server HTTP-DAV ; Thu, 9 Aug 2007 02:22:50 +0000 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C7DA2C.2F240C3A" User-Agent: Microsoft-Entourage/11.3.6.070618 Content-class: urn:content-classes:message Subject: Joining tables in two different databases? Date: Wed, 8 Aug 2007 22:22:50 -0400 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Joining tables in two different databases? thread-index: AcfaLeHMICTDMEYhEdyamgAX8gFv1g== From: "Michael Goldner" To: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.485 tagged_above=0 required=5 tests=AWL=0.527, BAYES_05=-1.11, HTML_MESSAGE=0.001, RCVD_NUMERIC_HELO=2.067 X-Spam-Level: * X-Archive-Number: 200708/96 X-Sequence-Number: 26427 This is a multi-part message in MIME format. ------_=_NextPart_001_01C7DA2C.2F240C3A Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Is it possible to access tables in two different databases, running on = the same server, from within psql? I have a table in a database that is "linked" to a table in another = database (using XA transactions in Jboss). I'd like to do some archiving at the database level, which requires that for every record in db1.table1, I = need to archive the corresponding record in db2.table1. I seem to recall using some other rdbms in the past that allowed one to prefix the table name with the dbname. Can this be done in postgres? If not, it seems that I'll need to do some sort of "COPY" operation to export the primary keys to a temporary table, import it into the second = db, and then perform my archive based on the table. Does this seem = appropriate? Otherwise, I could write the archive procedure in Java, using the XA datasource. I was hoping to avoid the overhead and complexity involved versus a succinct sql statement. Thanks! ------_=_NextPart_001_01C7DA2C.2F240C3A Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Joining tables in two different databases?

Is it possible to access tables in two different = databases, running on the
same server, from within psql?

I have a table in a database that is "linked" to a table in = another database
(using XA transactions in Jboss).  I'd like to do some archiving at = the
database level, which requires that for every record in db1.table1, I = need
to archive the corresponding record in db2.table1.

I seem to recall using some other rdbms in the past that allowed one = to
prefix the table name with the dbname.  Can this be done in = postgres?

If not, it seems that I'll need to do some sort of "COPY" = operation to
export the primary keys to a temporary table, import it into the second = db,
and then perform my archive based on the table.  Does this seem = appropriate?

Otherwise, I could write the archive procedure in Java, using the XA
datasource.  I was hoping to avoid the overhead and complexity = involved
versus a succinct sql statement.

Thanks!


------_=_NextPart_001_01C7DA2C.2F240C3A-- From revoohc@gmail.com Thu Aug 9 19:08:41 2007 Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id A77109FB2FC for ; Thu, 9 Aug 2007 16:08:41 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 53854-06 for ; Thu, 9 Aug 2007 16:08:38 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.175]) by postgresql.org (Postfix) with ESMTP id 229439F94CC for ; Thu, 9 Aug 2007 16:08:37 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id a2so447037ugf for ; Thu, 09 Aug 2007 12:08:36 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=elLCheFZvHbCWJ+pRIjpOWh69I2ZbcpCDKz6DPnEdT1IP15n0KU+nOi18NLUcHc6//o5F8QcW2L4jntr7oeVCX4L8wJFknO7HAXg+an8ltwfzcmweZ5goEBVaoCygrnb6AF2RQiRknIAdSkEo6rhqQVtrmEBOJpa4UOq1RrRMuQ= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=AKQsBwui8nxKIEsm/WhiCTeJ7AQkctERim1g9SuuIc9JKVG9Kt944ay3JgggSIojrjxQlIi/44cTaaX+GotBfijlyzqrCU+ucZ0bHy3cgo+OOdeb8+bxr9smXyn3ITUDRCyJVThW3iLtLbxuCHPz8eASHKpodbfLBCtvH1l3zrY= Received: by 10.78.162.4 with SMTP id k4mr1035094hue.1186686515728; Thu, 09 Aug 2007 12:08:35 -0700 (PDT) Received: by 10.78.201.16 with HTTP; Thu, 9 Aug 2007 12:08:35 -0700 (PDT) Message-ID: <1d219a6f0708091208p6a17cafak83cb1d0beff4495d@mail.gmail.com> Date: Thu, 9 Aug 2007 15:08:35 -0400 From: "Chris Hoover" To: "Michael Goldner" Subject: Re: Joining tables in two different databases? Cc: pgsql-admin@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_149895_13556714.1186686515695" References: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/110 X-Sequence-Number: 26441 ------=_Part_149895_13556714.1186686515695 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On 8/8/07, Michael Goldner wrote: > > Is it possible to access tables in two different databases, running on > the > same server, from within psql? > Take a look at dblink in the contrib area. I think it will give you what you are looking for. Chris ------=_Part_149895_13556714.1186686515695 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On 8/8/07, Michael Goldner <MGoldner@agmednet.com> wrote:

Is it possible to access tables in two different databases, running on the
same server, from within psql?

Take a look at dblink in the contrib area.  I think it will give you what you are looking for.

Chris
------=_Part_149895_13556714.1186686515695-- From knowhow@sistemasyconectividad.com.mx Sat Aug 11 02:22:35 2007 Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 3D33A9F97C9 for ; Fri, 10 Aug 2007 23:22:35 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 12455-06 for ; Fri, 10 Aug 2007 23:22:21 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from servidor58.suempresa.com (servidor58.suempresa.com [201.130.79.68]) by postgresql.org (Postfix) with ESMTP id 5F7299F97A8 for ; Fri, 10 Aug 2007 23:22:30 -0300 (ADT) Received: (qmail 2408 invoked from network); 10 Aug 2007 21:22:31 -0500 Received: from dsl-189-142-115-229.prod-infinitum.com.mx (HELO ?192.168.1.5?) (189.142.115.229) by servidor58.suempresa.com with SMTP; 10 Aug 2007 21:22:30 -0500 Subject: Re: Joining tables in two different databases? From: Julio Cesar =?ISO-8859-1?Q?S=E1nchez_Gonz=E1lez?= To: Chris Hoover Cc: Michael Goldner , pgsql-admin@postgresql.org In-Reply-To: <1d219a6f0708091208p6a17cafak83cb1d0beff4495d@mail.gmail.com> References: <1d219a6f0708091208p6a17cafak83cb1d0beff4495d@mail.gmail.com> Content-Type: text/plain; charset=utf-8 Date: Fri, 10 Aug 2007 21:22:20 -0500 Message-Id: <1186798940.6673.3.camel@andromeda> Mime-Version: 1.0 X-Mailer: Evolution 2.10.1 Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.174 tagged_above=0 required=5 tests=AWL=0.173, BAYES_50=0.001 X-Spam-Level: X-Archive-Number: 200708/127 X-Sequence-Number: 26458 El jue, 09-08-2007 a las 15:08 -0400, Chris Hoover escribió: > On 8/8/07, Michael Goldner wrote: > Is it possible to access tables in two different databases, > running on the > same server, from within psql? > yes it is, with dblink. follow next link: http://www.postgresql.org/docs/faqs.FAQ.html 4.17) How do I perform queries using multiple databases? There is no way to query a database other than the current one. Because PostgreSQL loads database-specific system catalogs, it is uncertain how a cross-database query should even behave. contrib/dblink allows cross-database queries using function calls. Of course, a client can also make simultaneous connections to different databases and merge the results on the client side. > > > Take a look at dblink in the contrib area. I think it will give you > what you are looking for. > > Chris -- Regards, Julio Cesar Sánchez González www.sistemasyconectividad.com.mx blog: http://darkavngr.blogspot.com --- Ahora me he convertido en la muerte, destructora de mundos. Soy la Muerte que se lleva todo, la fuente de las cosas que vendran.