2012/5/4 Ingmar Brouns <swingi@gmail.com>:
>
> Hi all,
>
> I'm a bit confused about when exactly I can rely on the resultset order when
> using set returning functions. The documentation states:
>
> 'After a query has produced an output table (after the select list has been
> processed) it can optionally be sorted. If sorting is not chosen, the rows
> will be returned in an unspecified order.'
>
> So when taking this very literally, I would expect that you cannot rely on
> the order of
>
> SELECT * FROM generate_series(2,4);
> generate_series
> -----------------
> 2
> 3
> 4
>
>
> I'm asking this because I'm writing queries similar to
>
> SELECT string_agg(foo, '@') FROM regexp_split_to_table('1@2@3@4','@') foo;
> string_agg
> ------------
> 1@2@3@4
> (1 row)
>
> and
>
> SELECT generate_subscripts(regexp_split_to_array('bird@dog@cow@ant','@'),
> 1), regexp_split_to_table('bird@dog@cow@ant','@');
> generate_subscripts | regexp_split_to_table
> ---------------------+-----------------------
> 1 | bird
> 2 | dog
> 3 | cow
> 4 | ant
> (4 rows)
>
> and need to know whether the order in these cases is guaranteed. Is there a
> more general statement that could be made, something like:
> If you use only set returning functions, and do not join their results, then
> the returning order of the individual functions will be respected?
result of SRF functions is ordered always - only when these processing
continues, then set can be reordered.
Regards
Pavel Stehule
>
> Thanks in advance,
>
> Ingmar Brouns
>
>