From: "Bath, David" <dave.bath@unix.net>
Reply-To: dave.bath@unix.net
To: pgsql-sql@postgresql.org
Subject: RULES on SELECT with JDBC/perlDBI from other RDBMS products?
Date: Fri, 7 Oct 2005 11:07:37 +1000
User-Agent: KMail/1.8
MIME-Version: 1.0
Content-Type: text/plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200510071107.38564.dave.bath@unix.net>

Folks,

I'm looking at using pg to be the main platform for integrating info
from other RDBMS products (particularly Oracle) as pg seems to be
the most flexible RDBMS around.

Disregarding writing to foreign products, query-plan efficiencies,
or differences of SQL dialect, I'd like to have a way of setting
up a fairly-transparent SELECT within pg that pulls rows from the
other product.  I wonder if anyone has attempted something like
this, and can recommend (or even deprecate) an approach, and perhaps
point to a code template.

Possible approaches that occur to me include
1. For the low-level integration ....
   a) Use of "foreign" JDBC thin client within PL/Java
   b) Use of untrusted perl DBI/DBD
   c) Use of low-level C code (e.g. declaring Oracle OCI calls
      to pg) - very labor intensive
2. For "transparent" use by other routines....
   Create pg table/view, then write rules that use functions
   returning rows (including barf exceptions if someone tries
   writing to a table).

If I can embed a java thin client binary/jar for the foreign
database in pg and use it using pg pl/java, then I'd like to
go that path as it would decrease setup/admin effort when
porting to other platforms, as there would be few dependencies
on things like external perl modules.

If any pg developer gurus are reading this, perhaps such templates
might be worthwhile including in the contrib bundle?
   
-- 
David T. Bath
dave.bath@unix.net