Re: Writing oracle/postgress generic SQL

On Fri, Feb 23, 2007 at 08:28:06AM -0800, Richard Troy wrote:
>
> On Fri, 23 Feb 2007, David Fetter wrote:
> > On Fri, Feb 23, 2007 at 10:23:56AM +0100, Ben Edwards wrote:
> > > Anyone know of any guidelines for writing SQL which works under
> > > Oracle witch will also work under postgress. This is to ensure that
> > > SQL written for an Oracle database can be migrated to postgress
> > > later.
> >
> > You've just bumped into the problem that while standard SQL exists,
> > only Mimer and possibly DB2 implement it. The presentation below
> > outlines your main choices for supporting more than one DB back-end,
> > and they're all expensive and troublesome to maintain.
> >
> > http://www.powerpostgresql.com/Downloads/database_depends_public.swf
>
> With all due respect to Josh's presentation, there's a lot more to
> the story than those couple of slides.

With all due respect, the presentation was if anything an
understatement. Unless, as with rare beasties like Science Tools, the
major purpose of the application is to support multiple DBMS
back-ends, it's just too expensive. Even in those rare cases, it's
expensive.

[sales pitch elided ;)]

> Are there things it misses? Yes, but not much. I'll take the wild
> guess that more than 80% of applications are completely and
> adequately served.

That says something about the applications you've seen, and not about
the adequacy of such a library. What point is there in using a
powerful tool like an RDBMS and then hobbling yourself by only using
10% of the available features? It's certainly a bad thing to do by
default.

> It has pass-through capability so you can still get at engine-specific
> features, though it does completely side-step stored procedures

Oops! There went 60% of the code in some of the databases I've seen
in production. 80% in at least one case I've seen in the past year.

Cheers,
D
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!
http://www.postgresql.org/about/donate