One way to handle dynamic and non-dynamic queries would be to match each query against a pattern, possibly a regexp, or some extended format if that's not flexible enough. For each matched pattern there would be an "action" that rewrites the query in a postgresql compatible way. Either you could write a rule for each query in the source, but it should also be possible to write the rules more generic so they would be reused for many different queries.
If this is done in the postgresql driver you wouldn't have to modify any of the acs sourcecode.
Maybe something inspired from perl's regexp substitution syntax could be used, like for example:
s/select (.*) from (.*) where (oracle outer join-matcher)/select from where rewrite-join-for-pg()/
Sometimes it might be better to solve the problem once and for all by pitching in and help the postgresql guys improve their database instead of ugly hacks just for openacs.
Just an idea to consider, what do you think?
