Hello
2013/1/3 Jeff Trout <threshar@xxxxxxxxxxxxxxxxxx>:
> 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@xxxxxxxxxxxxx>
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
