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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
