Curious unnest behavior

I have to say, this seems straightforward to me. An array with N elements
gets N rows in the result set. I'm curious what other behavior would be
more reasonable.

On Thu, Jan 3, 2013 at 11:22 AM, Jeff Trout <threshar@real.jefftrout.com>wrote:

Hello

2013/1/3 Jeff Trout <threshar@real.jefftrout.com>:

I just ran into an interesting thing with unnest and empty arrays.

create table x (
a int,
b int[]
);

insert into x(a,b) values (1, '{}');
insert into x(a,b) values (1, '{}');
insert into x(a,b) values (1, '{}');

select a, b from x;
select a, unnest(b) from x;

insert into x(a,b) values (2, '{5,6}');
select a, unnest(b) from x;

drop table x;

gives me:
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
a | b
---+----
1 | {}
1 | {}
1 | {}
(3 rows)

a | unnest
---+--------
(0 rows)

INSERT 0 1
a | unnest
---+--------
2 | 5
2 | 6
(2 rows)

DROP TABLE

I can understand the likely reasoning behind the behavior but perhaps a note in the documentation about it might be of use for others that may get bit by this functionality. (especially given the structure of the query, had I been doing select * from unnest(arr) that would be more intuitive, but given the query structure of select with no where the results can be surprising.)

thanks

this behave (and it is really strange) is related to using SRF
function in target list - in column list. This functionality is
strange and if you can, don't use it.

originaly this functionality looks like good idea, because anybody can
play like me (or
http://www.mentby.com/Group/pgsql-general/set-returning-functions-in-select-column-list.html
)

postgres=# select unnest(array[1,2]),unnest(array[1,2]);
unnest │ unnest
────────┼────────
1 │ 1
2 │ 2
(2 rows)

but it usually doesn't working like people expected

postgres=# select unnest(array[1,2]),unnest(array[1,2,3]);
unnest │ unnest
────────┼────────
1 │ 1
2 │ 2
1 │ 3
2 │ 1
1 │ 2
2 │ 3
(6 rows)

postgres=# select unnest(array[1,2]),unnest(array[1,2,3,4]);
unnest │ unnest
────────┼────────
1 │ 1
2 │ 2
1 │ 3
2 │ 4
(4 rows)

so result is - don't use SRF (set returning funtion) in column list
if you don't need.

9.3 will support LATERAL clause, and I hope so we can drop this
functionality (one day)

Regards

Pavel Stehule

--
Jeff Trout <jeff@jefftrout.com>

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general