Hello it is known old strange feature
http://postgresql.1045698.n5.nabble.com/Set-returning-functions-in-select-column-list-td5491544.html Regards Pavel Stehule p.s. don't use this feature, it is strange - and we cannot change behave due compatibility reasons. 2013/6/12 Denis de Bernardy <ddeberna...@yahoo.com>: > The actual query was something like: > > select id, person, unnest(groups) as grp from people > > … where groups is a crazy column containing an array that needed to be joined > with another table. In this case, you cannot do your suggested solution, > which would look like this: > > select id, person, grp from people, unnest(groups) as grp > > Admittedly, there are other ways to rewrite the above, but — if I may — > that's entirely besides the point of the bug report. The Stack Overflow > question got me curious about what occurred when two separate arrays are > unnested. > > Testing revealed the inconsistency, which I tend to view as a bug. > > This statement works as expected, unnesting the first array, then cross > joining the second accordingly: > >>> select 1 as a, unnest('{2,3}'::int[]) as b, unnest('{4,5,6}'::int[]) > > > This seems to only unnest one of the arrays, and match the element with the > same subscript in the other array: > >>> select 1 as a, unnest('{2,3}'::int[]) as b, unnest('{4,5}'::int[]) > > > Methinks the behavior should be consistent. It should always do one > (presumably like in the first statement) or the other (which leads to > undefined behavior in the first statement). > > Or it should raise some kind of warning, e.g. "you're using > undocumented/unsupported/deprecated/broken syntactic sugar". > > Denis > > > On Jun 12, 2013, at 12:05 PM, Greg Stark wrote: > >> On Wed, Jun 12, 2013 at 9:58 AM, <ddeberna...@yahoo.com> wrote: >>> denis=# select 1 as a, unnest('{2,3}'::int[]) as b, unnest('{4,5}'::int[]) >> >> set returning functions in the target list of the select don't behave >> the way you're thinking. What you probably want to do is move the >> unnest() to the FROM clause: >> >> select 1 as a, b, c from unnest('{2,3}'::int[]) as b(b), >> unnest('{4,5}'::int[]) as c(c) >> >> >> -- >> greg > > > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
