Querying cross database

for querying cross database should install dblilnk which is available under postgresql-contrib-xxx package.

installation on unix/debain/boss

$ apt-get install postgresql-contrib-8.3

$ su postgres

$ psql dbName < /usr/share/postgresql/8.3/contrib/dblink.sql

now you could be able to querying from different database or server

1) querying from other database on same server

SELECT *

FROM dblink('dbname=test1','select id,name from emp')

AS emp( id int,ename varchar);

here

• host --- specify the remote server ip
  
• dbname -- specify the database name
  
• port -- specify port no other the default (5432)
  
• username -- specify the username (if you did not specify this, dblink will connect
  
• password -- specify the password ( with current username & password)

since we are using querying on same server we omit others'

also we need to specify output structure of the table like AS emp( id int,ename varchar); with out this we may get error like

" ERROR: a column definition list is required for functions returning "record" "

2) Querying other database from remote server

SELECT *

FROM dblink('dbname=test1 host=192.10.1.5','select id from students')

AS stu(id int);

here we ip address of remote host using 'host' parameter.

3) Bulk insert into local table from remote server

SELECT t1.*

INTO test2

FROM dblink('dbname=saas-spms', 'SELECT * FROM test1)

AS t1(emp_no integer, degree varchar);

here table test2 should be new table, if already exist our database it shows error like : relation "test2" already found.

4) querying into remote server
If we want local table insert/update into remote postgres server
we can try like,

for insert :

select

dblink_exec('dbname=test1 host=192.10.1.5','insert into emp(id,ename) values(8,''employeeName'')')

for delete :

select

dblink_exec('dbname=test1 host=192.10.1.5','delete from emp where id=8')

here
dblink --> connect with remote server to execute the query which is return row (i.e Select )
dblink_exec --> connect to remote server and execute query which is not return row.