On 11/13/11 17:58, David Johnston wrote:
On Nov 13, 2011, at 11:13, Ludo Smissaert <ludo@xxxxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
