Re: Preserving ORDER of TEMP Tables during transaction

On Nov 13, 2011, at 11:13, Ludo Smissaert <ludo(at)ludikidee(dot)com> wrote:

> Greetings,
>
> Within a PL/PgSQL function I do a
>
> CREATE TEMPORARY TABLE v_temp ON COMMIT DROP
> AS
> SELECT ctime FROM source ORDER BY ctime
> WITH DATA;
>
> Then I use the v_temp in the same transaction block:
>
> FOR v_ctime IN
> SELECT ctime FROM v_temp
> LOOP
> ....
> END LOOP;
>
> Now I am curious, will the loop return values for ctime in the *same order*
> as the query that created the temporary table, or is this undefined?
>
> With other words: can I rely on the ORDER BY of the query that defined the temporary table? Is there a way to do that?
>
> Regards,
> Ludo Smissaert
>
>

Why risk basing your query's success on an implementation artifact? Put an explicit ORDER BY on the SELECT FROM v_temp.

Related question, though. Does the time to perform a sort vary based upon the entropy of the input data? If the original ORDER BY does result in the records being provided to sorter in order already does the sort basically finish immediately or is the algorithm strictly dependent upon the number of records to sort?

David J.