Skip site navigation (1) Skip section navigation (2)

inconsistent composite type null handling in plpgsql out variable

From: Merlin Moncure <mmoncure(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: inconsistent composite type null handling in plpgsql out variable
Date: 2009-08-28 16:10:39
Message-ID: b42b73150908280910y1cc6973etc010bca3f6ff3f6@mail.gmail.com (view raw or flat)
Thread:
Lists: 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

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2009-08-28 16:36:37
Subject: Re: inconsistent composite type null handling in plpgsql out variable
Previous:From: Dan O'HaraDate: 2009-08-28 13:59:41
Subject: BUG #5021: ts_parse doesn't recognize email addresses with underscores

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group