BUG #7808: unnest doesn't handle nulls in array of composite types correctly - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      7808
Logged by:          Joe Van Dyk
Email address:      joe@tanga.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 =3D 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                               =

                       =

 =