SQL order by query 以任意（但可重現）的方式產生結果

出於測試目的，我需要以任意（但可重現）的順序從一組數據庫表中獲取一些數據。這個想法是我以後可以使用文本差異工具比較兩次執行。有成語嗎？

例如，我顯然可以做一個：

SELECT * FROM table_with_N_columns ORDER BY column_1, ... , column_N

我只是想問是否有一種慣用的方式來實現相同的效果（出於我的目的），而不必費心列出 ORDER BY 子句中的每一列。只要查詢的後續執行可重現，任何排序都將執行。

無需費心列出 ORDER BY 子句中的每一列

為了這個目標，ORDER BY tablename.*確實有效。

如果涉及多個表，我們總是可以這樣寫：

SELECT * FROM (select ... complex query...) as T order by T.*;

如果結果集有許多寬行導致排序效率低下，則對散列表示進行排序可能是一個更好的主意：

SELECT * FROM (select ... complex query...) as T order by md5(T.*::text);

使用 PK 列

按表的所有列排序可能（非常）昂貴或有時根本不可能。範例：具有json列 or的表xml，或point不能在 中使用的許多其他數據類型之一ORDER BY，缺少必要的運算符。你得到一個例外。

如果它沒有失敗，它應該可以滿足您的目的，但它仍然不能保證真正穩定的排序順序。如果沒有唯一鍵或主鍵，則允許完全重複的行，這些行仍然是任意排序的。兩個並發會話都可以更改一組欺騙中的“第一個”而不會發生衝突，從而導致兩個不同的行被分別更改。

使用每個表的主鍵列。唯一約束也可以，但你真的應該在每個表上都有一個 pk 。除了正確並保證工作之外，它通常也更快。

功能

查詢系統目錄pg_constraint並pg_attribute獲取PRIMARY KEYorUNIQUE約束的列。

我將查詢包裝在一個方便的 SQL 函式中：

CREATE OR REPLACE FUNCTION f_order_cols(_tbl regclass)
 RETURNS text AS
$func$
SELECT string_agg(_tbl::text || '.' || quote_ident(attname), ',')
FROM (
  SELECT conrelid AS attrelid, conkey
  FROM   pg_catalog.pg_constraint
  WHERE  conrelid = $1
  AND    contype = ANY ('{p,u}'::"char"[]) -- primary or unique constraint
  ORDER  BY contype                        -- pk has priority ('p' < 'u')
          , array_length(conkey, 1)        -- else, fewest columns as quick tie breaker
  LIMIT  1                                 -- ... but any 1 of them is good
  )   c
JOIN   unnest(c.conkey) attnum ON TRUE      -- unnest array, possibly mult. cols
JOIN   pg_catalog.pg_attribute a USING (attrelid, attnum)

UNION  ALL                                  -- Default if first query returns no row
SELECT _tbl::text || '::text COLLATE "C"'   -- See chapter "Default" below
LIMIT  1
$func$ LANGUAGE sql;

• 這是非常快的。

• 該函式以表名為參數，類型regclass準確。根據目前的 search_path 解析名稱，如果無效則引發異常。同時在結果中自動提供正確引用的標識符。

• 任何PRIMARY KEY或UNIQUE約束都是好的。PK 具有優先權，否則將選擇具有最少列的唯一約束。

• 約束可以由多個列組成，其屬性編號儲存在數組中pg_constraint.conkey。使用一個隱含JOIN LATERAL的unnest()。

• 結果列是表限定的，以便為多個表的查詢準備好語法。如果您在查詢中使用表別名，則可能必須修改此可選功能。

• 還要正確引用列名以避免 SQL 注入。quote_ident()為你做。更多關於：

  • Postgres 函式中的 SQL 注入與準備好的查詢

預設

如果沒有找到約束，則函式返回NULL。將結果包裝到COALESCE並使用整行的文本表示作為預設值：tbl::text.

這不會像原始值那樣對行進行排序，但您會獲得適用於每個表的一致排序順序。因為無論如何你都要比較文本表示。

更好的是，添加COLLATE "C"忽略排序規則。更快，並且同樣適合您的目的。

ORDER BY tbl::text COLLATE "C"

仍然是一種可能非常低效的最後手段。盡可能使用 PK。我將預設值放入上面的函式中。將所有內容放在一起，以進行查詢：

SELECT * FROM **u** JOIN **p** USING (a) JOIN **n** ON n.b = p.b ORDER BY ?

這將返回完整的字元串?：

SELECT concat_ws(', ', f_order_cols('u')
                    , f_order_cols('p')
                    , f_order_cols('n')) AS order_by;

SQL Fiddle展示了所有內容。

引用自：https://dba.stackexchange.com/questions/75876