Re: DEFAULT of domain ignored in plpgsql (8.4.1)

Tom Lane wrote:
> Josh Berkus <josh(at)agliodbs(dot)com> writes:
>> (2) this change, while very useful, does change what had been a
>> simple rule ("All variables are NULL unless specifically set
>> otherwise") into a conditional one ("All variables are NULL unless
>> set otherwise OR unless they are declared as domain types with
>> defaults"). Do people feel that the new behavior would be
>> sufficiently intuitive to avoid user confusion?
>
> I'm inclined to leave it alone. It complicates the mental model, and
> frankly attaching defaults to domains was not one of the SQL
> committee's better ideas anyway. It's *fundamentally*
> non-orthogonal.

I've always though of domains as being a kind of subtype of it's base
type. In this picture, DEFAULTs for domains correspond to overriding the
default constructor of the type (thinking C++ now), and seem like a
natural thing to have. But maybe that's more a C++ programmers than a
database designers point of view...

I've just checked how rowtypes behave, and while the "set to null unless
specifically set otherwise" rule kind of holds for them, their NULL
value seems to be special-cased enough to blur the line quite a bit

create or replace function myt() returns t as $body$
declare
r t;
begin
raise notice 'r: %, r is null: %', r, (r is null);
return r;
end;
$body$ language plpgsql immutable;
select myt(),myt() is null;

gives:

NOTICE: r: (,), r is null: t
NOTICE: r: (,), r is null: t
myt | ?column?
-----+----------
(,) | f

Strange I think... And at least half of an exception to the simple
"always null unless specifically set otherwise" rule

It also seems that while domain DEFAULTs are ignored, the resulting
(null-initialized) variable is still checked against the domain's
constraints, including a potential NOT NULL constraint

create domain myint as int not null;
create or replace function myint() returns myint as $body$
declare
i myint;
begin
return i;
end;
$body$ language plpgsql immutable;

raises

ERROR: domain myint does not allow null values
CONTEXT: PL/pgSQL function "myint" line 3 during statement block local
variable initialization

This has the potential to cause some headache I think if you use domains
to prohibit NULL values because they make no semantic sense for your
application, and depend on DEFAULT to fill in some other value (like an
empty string or an empty array).

best regards,
Florian Pflug