OK, a couple quick comments:
- sysdate()'s not inefficient. The problem is that PG doesn't have
the equivalent of "create or replace function", and the compiled-in
reference to the function call in the datamodel means that dropping
and creating a functino forces you to drop and create any table
referencing it.
If we're 100% sure a function will never change, we won't be causing
update problems for our users, but if we're not ... well, I think you
see the problem.
now()'s built in and guaranteed not to change.
BTW, Oracle has a similar problem if you drop/create functions -
that's why they've got the "create or replace" non-standard query.
PG will be getting something equivalent, probably in 7.2, but for now
we're best off just avoiding user functions in the datamodel unless we
there's no alternative available.
- I would rather change "nvl" to "coalesce" and "decode" to "case"
rather than use overloaded functions.
They're easy to recode, and the resulting query works in both
Oracle and PG so you don't need to split into separate queries for
this reason alone. I've been trying to get aD to use these rather
than the Oracle-isms but obviously with no success thus far. They do
exactly the same thing.
An additional reason for rewriting the query to use proper SQL92
forms is that IB tends to support proper SQL92. If we rewrite the
queries to use "coalesce" and "case" we're part-way home for a port to
other DBs that follow the standard.
Since the rewrite doesn't break Oracle, and since it won't add
significantly to our burden, my thinking was "why not change 'em".
The reality here is that aD is making life unnecessarily hard by
not using SQL92 constructs when Oracle supports them.
