Re: query

On Thu, Sep 01, 2005 at 01:34:41PM -0500, Federico Balbi wrote:
> I was just wondering if postgresql supports queries between servers in
> order to pull info from different machines.

Not natively, but contrib/dblink provides a way to query other
PostgreSQL databases, which can be running on other machines.
Functions in some procedural languages (e.g., PL/Perl) can query
arbitrary data sources (other kinds of databases, spreadsheets,
text files, etc.) and return data as sets of rows. With such
external data sources you don't get transactional semantics, however.

In PostgreSQL, if you need to partition data but also need to perform
cross-partition operations, then the usual recommendation is to use
multiple schemas inside a single database.

> Let's say server A can run a query against server B... something like:
>
> server1 running a SELECT * FROM server2.table
>
> or joins
>
> SELECT A.name, B.name FROM server1.table A, server2.table B WHERE A.id=B.id

You could abstract a dblink query with a view, but unfortunately
the WHERE clause wouldn't propogate to the underlying query on the
remote database -- a dblink query would have to fetch the entire
remote result set before the local database could do any restrictions.
In some cases, however, it can be worthwhile to call dblink directly
with an appropriate WHERE clause in the query string.

--
Michael Fuhr