BUG #7808: unnest doesn't handle nulls in array of composite types correctly

The following bug has been logged on the website:

Bug reference: 7808
Logged by: Joe Van Dyk
Email address: joe(at)tanga(dot)com
PostgreSQL version: 9.2.1
Operating system: OSX
Description:

RhodiumToad says this is a bug in unnest, but honestly I don't quite
understand it all.

He said: "if you have an array of composite, then a null element provokes
that error, as opposed to an element all of whose columns are null.
basically, unnest(array[null::g]) breaks, while
unnest(array[row(null,null)::g]) works"

My goal is to remove nulls from an array. The array could be an array of a
composite type.

begin;



create table f (id integer);

insert into f values (1), (2);



create table g (id integer, f_id integer);

insert into g values (1, 1);

insert into g values (2, 1);



create function no_nulls(anyarray) returns anyarray as $$

select array(select x from unnest($1) x where not (x is null))

$$ language sql;



select f.id, no_nulls(array_agg(g))

from f

left join g on g.f_id = f.id

group by f;

Expected Result:

id | array_agg

----+-------------------

1 | {"(1,1)","(2,1)"}

2 | {}





Getting this error:



psql:/tmp/n.sql:18: ERROR: function returning set of rows cannot return
null value
CONTEXT: SQL function "no_nulls" statement 1