Don Baccus <dhogaza@pacifier.com> writes:
> Maybe the behavior's implementation defined ... if not, I'd presume SQL3
> states that a function in the above context is called either once per
> row or once per query, not sometimes one or sometimes the other.
AFAICT, the relevant concept in SQL99 is whether a function is
"deterministic" or not:
An SQL-invoked routine is either deterministic or possibly non- deterministic. An SQL-invoked function
thatis deterministic always returns the same return value for a given list of SQL argument values. An
SQL-invokedprocedure that is deterministic always returns the same values in its output and inout SQL parameters
for a given list of SQL argument values. An SQL-invoked routine is possibly non-deterministic if, during
invocationof that SQL- invoked routine, an SQL-implementation might, at two different times when the
stateof the SQL-data is the same, produce unequal results due to General Rules that specify
implementation-dependent behavior.
It looks to me like the spec does NOT attempt to nail down the behavior
of non-deterministic functions; in the places where they talk about
non-deterministic functions at all, it's mostly to forbid their use in
contexts where nondeterminism would affect the final result. Otherwise
the results are implementation-defined.
regards, tom lane