Re: Preserving ORDER of TEMP Tables during transaction

On 11/13/11 17:58, David Johnston wrote:
> On Nov 13, 2011, at 11:13, Ludo Smissaert <ludo(at)ludikidee(dot)com> wrote:
> 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?
>
> 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?

The algorithm is that I am returning a SETOF cursors pointing
to two different tables and data of these two tables will be
printed by the client like this:

row 1 of table a
set of rows from table b, depending on value of preceding a
row 2 of table a
set depending on ... etc.

The first cursor encompasses all rows of a and is needed
by the client for alignment.

dummy_cursor_a -- all rows for alignment
next_of_a -- first row
details_from_b_depending_on_previous_a
next_of_a
details_from_b

The client receives instructions in what to do with the cursors,
and basically does not know anything about the sort of data
it prints. It is just instructed in how to handle the cursors.

Further the entire result set of a depends on a dynamically
generated WHERE-clause.

EXECUTE 'CREATE TEMPORARY TABLE v_temp ON COMMIT DROP AS '
'SELECT <projection> FROM <view> WHERE ' || v_filter || ' ORDER BY '
'WITH DATA;'

Well, I guess I will think of something simpler.

Thanks for answering.

Regards,

Ludo Smissaert