Thread: query a table from one database to another

On Wed, Jan 27, 2010 at 02:52:58PM +0600, AI Rumman wrote:
>    I am using Postgresql 8.3
>
>    I have two databases:
>    db1
>    db2
>
>    db1 holds a table tab1.
>
>    Is it possible to get the value of the above tab1 in db2 database?
>
>    Regards

You'll need something like the dblink contrib module to get one database to
talk to another.

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com

Please tell me why? I am using Postgresql 8.3

On Thu, Jan 28, 2010 at 11:36:55AM +0600, AI Rumman wrote:
>    I am getting the error:
>
>    ------------------------------------------------------------------------
>    LINE 1: select dblink_connect('dbname=postgres');
>                   ^
>    HINT:  No function matches the given name and argument types. You might
>    need to add explicit type casts.
>    ------------------------------------------------------------------------
>
>    Please tell me why? I am using Postgresql 8.3

I'm guessing that this is because you're trying to us dblink without having
installed it. I don't know how you've installed PostgreSQL; on my Ubuntu
system there's a package called postgresql-contrib I would need to install
first.

Once you have the package, you need to get dblink into your database. Like
most contrib modules, dblink comes with a SQL script you need to run to create
the necessary functions and other objects. In this case it's called
dblink.sql. Find that file -- in my case it's part of the postgresql-contrib
package -- and execute it in your database to create that function.

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com