Re: Procedural language functions across servers

On Sun, Jul 09, 2006 at 12:40:56PM +0000, Mark Morgan Lloyd wrote:
> I know that the FAQ says that the only way to implement a query
> across databases is to use dblink,

The FAQ doesn't say dblink is the only way, it says "contrib/dblink
allows cross-database queries using function calls."  However, the
paragraph that says "There is no way to query a database other than
the current one" could be misinterpreted to mean dblink is the only
way if you read "current one" as "method mentioned in the current
FAQ item" rather than as "database to which you are currently
connected."

http://www.postgresql.org/docs/faqs.FAQ.html#item4.17

dbi-link is an alternative to dblink that uses Perl/DBI:

http://pgfoundry.org/projects/dbi-link/

> is this the only way available if additional procedural languages
> are installed?

With the untrusted version of a language you can do essentially
anything that language supports.  For example, with plperlu, you
could use DBI to open a connection to another database (even another
DBMS like Oracle, MySQL, etc.), issue a query, fetch the results,
and do whatever you want with those results.  Example:

CREATE FUNCTION remote_version(text, text, text) RETURNS text AS $$
  use DBI;
  my ($dsn, $user, $pass) = @_;
  my $dbh = DBI->connect($dsn, $user, $pass);
  my @row = $dbh->selectrow_array("SELECT version()");
  $dbh->disconnect;
  return $row[0];
$$ LANGUAGE plperlu VOLATILE;

SELECT remote_version('dbi:mysql:wopr;host=norad', 'falken', 'joshua');
 remote_version
----------------
 5.0.22-log
(1 row)

--
Michael Fuhr