Re: NOT NULL with CREATE TYPE

Jean Hoderd <jhoderd(at)yahoo(dot)com> writes:
> I have tried the following, but it's not accepted:
> create type result as (name text not null, age int4 not null);

Frankly, the notion that a "not null" condition might be associated with
a SQL data type is simply a bad idea. The SQL committee let this happen
for domains in an episode of monumentally fuzzy thinking, but you don't
want to do it. Consider

create table t1 (id1 int, f1 int);
create table t2 (id2 int, f2 mynotnulltype);
select * from t1 left join t2 on id1=id2;

What is the datatype of the f2 column of the output? mynotnulltype,
presumably. Now what do you do about t1 rows that have no match in
t2? You can either emit null-extended rows, thus producing null
values in a mynotnulltype column, or throw an error, which isn't too
appetizing either --- you just rendered outer joins useless.

With a not null domain, it is at least possible to finesse this by
deciding that the join output column should be considered to be
of the domain's base type. If "not null" is hardwired into the
type definition, there's no way out.

So I don't recommend you try to do this. What is the actual problem you
are trying to solve? Why do you want the client library to be concerned
with attnotnull at all?

> For example, to get all people in the database, the client will invoke
> function "SELECT * FROM get_people()" instead of manually doing a SELECT
> over the 'people' table:

... or even more to the point, why do you think the above is a good idea
to begin with? It looks more like the kind of bad design that is
frequently committed by people who basically don't like SQL, and try to
ensure that no one else will like it either.

regards, tom lane