Обсуждение: RULES on SELECT with JDBC/perlDBI from other RDBMS products?
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
Bath, David wrote: > 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. Are you familiar with the dblink() module in the contrib/ directory of the source distribution? -- Richard Huxton Archonet Ltd
On 10/6/05 9:07 PM, "Bath, David" <dave.bath@unix.net> wrote: > 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? > The DBI-link project is quite useful for doing just what you describe, if I understand you correctly. http://pgfoundry.org/projects/dbi-link/ http://www.pervasivepostgres.com/postgresql/tidbits_June05.asp Sean