Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 9C143633753 for ; Sat, 2 May 2009 12:01:11 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 94232-05 for ; Sat, 2 May 2009 12:01:10 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from yw-out-2324.google.com (yw-out-2324.google.com [74.125.46.31]) by mail.postgresql.org (Postfix) with ESMTP id D2194633744 for ; Sat, 2 May 2009 12:01:09 -0300 (ADT) Received: by yw-out-2324.google.com with SMTP id 3so1791674ywj.73 for ; Sat, 02 May 2009 08:01:09 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:cc:content-type; bh=fAwazqkLIszlFt2MCALz0ys21dsgm2dl3wHy8lNwA24=; b=X/NJ+uFN9cK5qsB9as6IX38uNOiIjFz2PMpC28jvrtpJHrrrIqIYIcZ8/JQMTP3wx6 CE+7IffAcJShGoYXmVvQIOq6UrJO8Z4spuVVTpMS2PSALq6lLvQ2fjcBqvFcyLAi75uB n5qpMY758W8umolfXRBNImGB6AHsyGd9l4jz8= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=PE1y3ttwYErctYhnkhoIcxT7HSPD/bZVUG358n50yS8RfZa0wiXPBGnyZ1A/IY0CWO nzD+NTcj8ebuK14MXIlVZe8oV20GK2CMuRuZQbJ+2ckasOMHA2sIIpaXW/zaCJbaQAXM 65GeA1TWV/ONSeOKnRZxVfMDu0+yqxOZZpsNM= MIME-Version: 1.0 Received: by 10.90.69.7 with SMTP id r7mr3356903aga.47.1241276469245; Sat, 02 May 2009 08:01:09 -0700 (PDT) In-Reply-To: References: <328DDBBB-31C4-41D7-BF0E-5ADD732F2B9F@mac.com> <20090430033747.GB1126@eddie> Date: Sat, 2 May 2009 12:01:09 -0300 Message-ID: Subject: Re: Comparing two tables of different database From: Lucas Brito To: Nicholas I Cc: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=0016363b92220476c10468ef3375 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200905/4 X-Sequence-Number: 32496 --0016363b92220476c10468ef3375 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Nicholas, To use the dblink: 1. In your postgres server you should find a file *dblink.sql*. In my beta installation is in *share/postgresql/contrib*. It is the installation for the dblink contrib module that usually is already compiled in. It will create a lot of dblink functions. 2. on database2 create a function nammed db_datbase1() which returns "dbname=database1" (if you need a login use "dbname=database1 password=xxx", you can also specify host= port= to connect in a remote postgresql database) 3. now execute the sql: select * from dblink(db_database1(), 'select "id", "name", "time" from pr_1') as pr_1("id" integer, "name" text, "time" time) then you will see the table "pr_1" on the datbase2 -- Lucas Brito --0016363b92220476c10468ef3375 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Nicholas,

To use the dblink:
  1. In your postgres server you should find a file dblink.sql. <= br> In my beta installation is in share/postgresql/contrib. It is the installation for the dblink contrib module that usually is already compiled in. It will create a lot of dblink functions.

  2. on database2 create a function nammed db_datbase1() which return= s "dbname=3Ddatabase1" (if you need a login use "dbname=3Ddata= base1 password=3Dxxx", you can also specify host=3D port=3D to connect in a = remote postgresql database)

  3. now execute the sql:
    = select * from dblink(db_database1(), 'select "id", "name= ", "time" from pr_1') as pr_1("id" integer, &q= uot;name" text, "time" time)
    then you will see the table "pr_1" on the datbase2    =A0
--
Lucas Brito --0016363b92220476c10468ef3375--