On Mon, Feb 16, 2009 at 04:40:23PM +0100, Pavel Stehule wrote:
> 2009/2/16 Sam Mason <sam@samason.me.uk>:
> > On Mon, Feb 16, 2009 at 03:21:12PM +0100, Pavel Stehule wrote:
> >> so these modules (decode, oraemptystr) decrease differences between
> >> PostgreSQL and Oracle.
> >
> > wouldn't it be better/easier to extend something like pgpool to
> > transform Oracle style SQL code to PG style code? You'd certainly
> > be able to get it more complete in reasonable amounts of time, but
> > performance would suffer when you went to look up table definitions to
> > check the types of various things.
>
> then you should to rewrite complete PostgreSQL parser :) and
> performance will be worse (you have to parse query string two times).
Yes, there'd be a few thousand lines of code to write.
Note that you only need to parse things twice, planning only needs to be
done by PG, so it shouldn't be too bad. It'll add maybe a millisecond
or so to query execution times, with most of that time spent going off
to find table and function definitions from the real database.
> For this transformation you need query's semantic tree and access to
> dictionary (some caches) . Lot of things should by done via
> extensibility features of PostgreSQL. Sure - you can do this things
> difficult outside of PostgreSQL or simply via parser's hook.
But to do it properly inside PG would be difficult; how would your hooks
know to transform:
SELECT s FROM foo WHERE s IS NULL;
into:
SELECT s FROM foo WHERE (s = '' OR s IS NULL);
that all looks a bit tricky to me. Hum... actually it's not. All you
need to do is to rewrite any string reference "s" into NULLIF(s,'').
That would tank performance as indexes wouldn't be used most of the
time, but never mind.
> These samples are only for Oracle. But I am sure, so this technique
> should be used for different databases too. Example. Informix uses
> convention for named params like paramname = value. PostgreSQL 8.5
> will use syntax paramname AS value. So you need change app. code. With
> hook I am able transform transparently Informix syntax to PostgreSQL
> syntax without significant increase of load or complexity.
That would be a *much* bigger change; you're actually changing PG's
parser there and not just modifying the parse tree. If it was done
externally it would be a much easier thing to do.
-- Sam http://samason.me.uk/