PostgreSQL 8.2跨数据库查询

目前的版本已经支持跨数据库查询数据，首先得创建一系列的函数，创建脚本名称为“dblink.sql”：

$ psql -f /usr/share/dblink.sql my_database

创建完毕，一个典型的查询如下：

SELECT towns.* FROM dblink ('dbname=somedb', 'SELECT town, pop1980 FROM towns') AS towns (town varchar(21), pop1980 integer)

如果使用非标准端口，则：

SELECT p.* FROM dblink ('dbname=ma_geocoder port=5433', 'SELECT gid, fullname, the_geom_4269 FROM ma.suffolk_pointlm') AS p(gid int,fullname varchar(100), the_geom geometry)

对非本地数据库查询：

SELECT blockgroups.* INTO temp_blockgroups FROM dblink('dbname=somedb port=5432 host=someserver user=someuser password=somepwd', 'SELECT gid, area, perimeter, state, county, tract, blockgroup, block, the_geom FROM massgis.cens2000blocks') AS blockgroups(gid int,area numeric(12,3), perimeter numeric(12,3), state char(2), county char(3), tract char(6), blockgroup char(1), block char(4), the_geom geometry)

连接查询：

SELECT realestate.address, realestate.parcel, s.sale_year, s.sale_amount, FROM realestate INNER JOIN dblink('dbname=somedb port=5432 host=someserver user=someuser password=somepwd', 'SELECT parcel_id, sale_year, sale_amount FROM parcel_sales') AS s(parcel_id char(10),sale_year int, sale_amount int) ON realestate.parcel_id = s.parcel_id

参考资料：Using DbLink to access other PostgreSQL Databases and Servers