The following bug has been logged on the website: Bug reference: 7808 Logged by: Joe Van Dyk Email address: j...@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 = 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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
