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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs