Cultural Sublimation <cultural_sublimation@yahoo.com> writes:
> This bug seems to obvious to have been generally missed.
It's not a bug: there is no mechanism enforcing that the result of a
function can't be NULL.
For functions returning scalars you can get the effect by declaring the
result as being of a domain with a not null constraint, but those
things are actually quite unpleasant to use for anything other than
a shorthand for making a table constraint. Check the archives for loud
gripes from people who tried to use not-null-constrained domains in
plpgsql since plpgsql started to actually enforce that. (Some of them
seemed to have this curious idea that the constraint should only be
enforced when it was convenient for them ... and not, say, instantly
when the variable was declared. But I digress.)
In any case, it appears to me that your gripe has little to do with
whether there's actually any enforcement of the not-null condition,
and much to do with whether some unspecified client-side software
thinks the query result column is guaranteed not null. Most likely
you're going to have to fix the client-side software, because it's
extrapolating things that Postgres does not tell it.
regards, tom lane