Re: set returning functions and resultset order

2012/5/4 Ingmar Brouns <swingi(at)gmail(dot)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(at)2@3(at)4','@') foo;
>  string_agg
> ------------
>  1(at)2@3(at)4
> (1 row)
>
> and
>
> SELECT generate_subscripts(regexp_split_to_array('bird(at)dog@cow(at)ant','@'),
> 1), regexp_split_to_table('bird(at)dog@cow(at)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
>
>