On Fri, 2009-06-05 at 10:58 -0700, Jean Hoderd wrote:
> The above example was absurdly simple, but in the real world the query
> is complex enough that instead of being just a SELECT is actually the
> return of a PL/PGSQL function. I just want a way to tell the client
> which fields from the return type are actually, really, nullable...
The way you are approaching this problem is understandable. You're
thinking of NULL as just an extra value in the domain of the type (and
therefore can be restricted by a type constraint), but that is not true
in SQL. You may think this approach is good or you may think it's bad,
but NULL permeates SQL at many levels, and can't merely be ignored.
In general, in SQL, NULLs can be produced in several ways even if every
column in your database is declared NOT NULL and you never specify a
NULL in any query. For instance, aggregates produce NULLs when there are
no input rows (COUNT is an exception), and (as Tom pointed out) OUTER
JOIN produces NULLs when there is no matching row on the other side.
I believe this fact foils the kind of checks you intend to do in the
general case, although you may be able to work around it creatively for
your specific situation. If you are interested in such a workaround,
provide a few more details and someone will probably have some ideas for
you.
The best I can say right now is that the client always needs to check
for NULL unless it somehow knows that NULL can't be produced.
Regards,
Jeff Davis