Re: Oracle and Postgresql

On Sep 4, 2008, at 7:40 PM, Robert Treat wrote:
> It is not as simple as Oracles database link syntax. Setting up a
> connection
> involves a couple of sql looking commands, and once you setup a
> connection to
> a remote database, you can reference a table with something like
> select *
> from mytable(at)myotherdb(dot) There's no way a function oriented solution
> can
> match that imho.

I have long thought that what would be really useful is a standard way
for third-party modules to extend or override the SQL language support
within PostgreSQL itself without needing to be integrated in core.

E.g. it should be possible for all of EnterpriseDB's Oracle-compatible
SQL changes to exist as a separate module, somebody could change the
behavior of a select to default ordering to imitate Oracle etc. It
should be possible for a replication engine to add syntax for options
specific to it. Contrib modules like dblink could install SQL-like
command support.

This would be both invaluable for compatibility efforts and probably
raise the amount of 3rd party stuff that actually gets used
(currently, many places I've seen avoid Slony because they fear having
to use the commandline scripts it comes with, and if you want to
manipulate Slony from the database itself, oftentimes this means you
have to use pl/perlu or another untrusted language.

Don't get me wrong, functions are great too. :) But currently the
above means that a lot of risk is introduced and you have to put a lot
of faith in the perl code - an exploit poses a lot of risk. If Slony
exposed it's own data to PG via custom SQL extensions, this would be
more secure by design.

Cheers,
--
Casey Allen Shobe
Database Architect, The Berkeley Electronic Press