Обсуждение: set returning functions and resultset order

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?

Thanks in advance,

Ingmar Brouns

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
>
>