inconsistent composite type null handling in plpgsql out variable

Поиск
Список
Период
Сортировка
От Merlin Moncure
Тема inconsistent composite type null handling in plpgsql out variable
Дата
Msg-id b42b73150908280910y1cc6973etc010bca3f6ff3f6@mail.gmail.com
обсуждение исходный текст
Ответы Re: inconsistent composite type null handling in plpgsql out variable
Re: inconsistent composite type null handling in plpgsql out variable
Список pgsql-bugs
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

В списке pgsql-bugs по дате отправления:

Предыдущее
От: "Dan O'Hara"
Дата:
Сообщение: BUG #5021: ts_parse doesn't recognize email addresses with underscores
Следующее
От: Tom Lane
Дата:
Сообщение: Re: inconsistent composite type null handling in plpgsql out variable