Today I ran into a problem relating to $subject. plpgsql's handling
of 'null' composite types is not consistent with what you get in sql:
create table foo(a text, b text);
create table bar(id int, f foo);
insert into bar values (1, ('a', 'b'));
create or replace function f(_foo out foo) returns foo as
$$
declare
r record;
begin
select coalesce(_foo, f) as f
from bar where id = 1 into r;
raise notice '%', r.f;
select f
from bar where id = 1 into r;
raise notice '%', r.f;
return;
end;
$$ language plpgsql;
plpgsql is not assigning the value to _foo in the first case because
coalesce is not treating the _foo as null for some reason.
I'm going to take this opportunity to editorialize a bit:
In this case postgresql is not applying either the insane sql standard
definition of null (non null composite type with fields null) or the
sane definition that is mostly used (a composite type may itself be
null independently of its fields). This leads to some very weird
behaviors, for example 'coalesce(foo, something)' and 'case when foo
is null then something else foo end' can give different answers.
postgresql treats non-null composite types with null fields as null in
terms of the 'IS NULL' operator but as non null every where else
(coalesce, STRICT, etc). IMO, the IS NULL behavior was hacked in for
compliance with the SQL standard. In the wider context of how we do
things, IS NULL simply lies to you. This isn't (IMO) really a big
deal, but when correcting the above behavior, which standard should we
apply?
merlin
