"from dual" is already dealt with via the "dual" view, as is "select sysdate from dual" for simple cases. The view trick is also used so you can say "select nextval from sequence_name" in PG (though you have to be careful that it's only called once in your query)
However - date semantics are totally different. I don't see much hope there until Oracle tracks the SQL99 date standard (PG is finally getting close to standard in this regard and 7.3 may finally be there)
The differences in the PL/[pg]SQL calls are much different than merely the package vs. non-package name convention.
In Oracle we make heavy use of default values and named parameters in function/procedure calls. Can't have one without the other. Default values are very useful IMO and I'd hate to see us give them up in our Oracle source just because PG doesn't support them.
And on the PG side at times we've had to disambiguate with explicit casts (rarely) or inventing new overloaded function defs (more commonly) when writing several shorthand forms of functions or functions that do slightly different things (like in the CR regarding file/lob/etc storage.) The problem we have is that the bindvar hack in the PG driver surrounds values in quotes which means that PG has no idea what the type is and essentially picks the function that has the right number of params. The bindvar hack must work this way, too, to give us the safety against SQL smuggling we seek (forces a type conversion with error checking.)
We thought about doing the name mapping (__ vs. .) when we first started porting code and these were some of the reasons we decided not to do it.
Now ... we did speak earlier about getting a lot of leverage by simply defining permission_p() for permission checking. This function has no default params in Oracle therefore the named parameter style isn't necessary. And everyone knows what the three parameters are for, anyway, since its use is so pervasive.
And getting rid of silly things like get_live_revision() vs. cr_items.live_revision helps too.
The CR has auto-SQL generation code that does stuff similar to what Tom's mentioning for content types, though it's not used outside the CMS. I've thought about adopting the CR's view trick for general acs_objects. That would make doing something like Tom mentions - autogenerating DML for inserts and updates - easier.
That would get rid of a lot of the PL/[pg]SQL entirely and, much like the new Tcl API for service contracts, would simplify code.
What do people think about trying to attack the problem by reducing dependence on PL/[pg]SQL?
