The new plpython work is very cool, no doubt. My major objection to using it to work around the embedded Oracle Java stuff is exactly the same as my objection to the embedded Oracle Java stuff in the first place - it is horribly unportable.
The problem is our desire to support other RDBMS's in the future combined with the fact that some don't feature any decent embedded language facility with all the needed hooks to the system environment.
So my feeling is that if we were looking at OpenACS as solely being an Oracle + PostgreSQL solution, the path you suggest would be reasonable. But given our interest in other RDBMS platforms - including even MySQL if the new InnoBase backend matures and performs as advertised - I'd prefer to avoid trading one unportable solution for another.
As far as storing queries in functions to avoid queryplanning overhead, Karel Zak wrote an add-on that allows you to cache queries. I've had my eyes on that. It wasn't released with PG 7.1 but I think it might be released with PG 7.2. Then the query processor could tell PG to save the query plan and execute it.
We are looking at creating functions as you suggest for the inline stuff called from db_exec_plsql, in fact Dan's committed a simple version that creates and drops a function wrapper on each call. Later we'll want to keep track of these anonymous functions so they don't need to be recreated for each call - Dan's hack is meant only as something to keep this from being on the critical path.
