For a number of cases, you're doing simple string transformations.
So simple, in fact, that we've simply regexp'd a bunch of them in the past. We're concerned about regexp memory leaks in Tcl8, though ...
A parser could help, yes, but you need semantic information - a simple symbol table, at minimum - not just syntax information to transform outer joins. An automatic tool to do this might be worth investigating since Oracle can't be bothered to support the SQL92 syntax for this particular feature.
The reason you need semantic information is that SQL92 performs outer joins in the FROM statement. Actually, all joins can be there. Wearing my language design hat, this makes sense since a JOIN operator joins tables, not keys.
select * from foo, bar where foo.key = bar.key(+)
becomes
select * from foo left [join] bar using(key);
(join is optional, you don't really write it with brackets!)
Unqualified names require symbol table information - for tables, not just SQL statements - in order to properly correlate columns with their tables. The syntax of the SQL statement alone ain't enough.
So this looks like a long-term project for someone who's not burned out on compiler writing (I would be the person who *is* burned out on compiler writing...)
The outer joins are easy to rewrite by hand, at least. We're not faced with "UNION hell" like we were with PG 7.0 now that PG 7.1 has outer joins.
If someone were to cobble together a simple tool to massage Oracle DDL in particular, that would be a help. It could ignore anything it doesn't understand. On the other hand, those things only take a few minutes to edit by hand, too.
Our biggest task will be porting over PL/SQL to PL/pgSQL. The changes required there are beyond even a real compiler's ability to deal with - no default parameters in PL/pgSQL. Because of that, we'll use our intuitive powers to decide which variants are commonly used and, taking advantage of PG's ability to overload functions, provide a variety which default to common values in order to help the port process.
