這陣子遇到希望挑出特定內容往前排序的狀況。為了處理這個，想了一下子怎樣都要拆分資料做處理在合併，但寫好多行 script 處理還是有點痛苦。。。
不過～找了一陣子，總算讓我看到一個排序的奇妙範例。
這個功能，就是 ORDER BY 語法，其實也可以吃運算式～
以下範例，就是把系統表 pg_tables 的紀錄（Note：這其實是張 view），特地把兩個 schema 往前挪，優先排序
edb=# SELECT schemaname, tablename FROM pg_tables
ORDER BY (schemaname IN ('pg_catalog', 'sys')) DESC, tablename;
schemaname | tablename
--------------------+-------------------------------------
sys | callback_queue_table
sys | dual
sys | edb$session_wait_history
sys | edb$session_waits
sys | edb$snap
sys | edb$stat_all_indexes
sys | edb$stat_all_tables
sys | edb$stat_database
sys | edb$statio_all_indexes
sys | edb$statio_all_tables
sys | edb$system_waits
sys | edb_dbms_redact_full_default_values
pg_catalog | edb_dir
pg_catalog | edb_last_ddl_time
pg_catalog | edb_last_ddl_time_shared
pg_catalog | edb_password_history
pg_catalog | edb_policy
pg_catalog | edb_profile
pg_catalog | edb_queue
pg_catalog | edb_queue_callback
pg_catalog | edb_queue_table
pg_catalog | edb_redaction_column
pg_catalog | edb_redaction_policy
pg_catalog | edb_resource_group
pg_catalog | edb_variable
pg_catalog | pg_aggregate
pg_catalog | pg_am
edb=#
但這招不能直接用在 UNION 上。。。會出錯。。
以下範例延伸上面範例，但湊了一個 UNION
edb=# SELECT schemaname, tablename FROM pg_tables
WHERE schemaname = 'pg_catalog'
UNION
SELECT schemaname, tablename FROM pg_tables
WHERE schemaname = 'sys'
ORDER BY (schemaname IN ('pg_catalog', 'sys')) DESC, tablename;
ERROR: invalid UNION/INTERSECT/EXCEPT ORDER BY clause
LINE 6: ORDER BY (schemaname IN ('pg_catalog', 'sys')) DESC, tablena...
^
DETAIL: Only result column names can be used, not expressions or functions.
HINT: Add the expression/function to every SELECT, or move the UNION into a FROM clause.
edb=#
這錯誤訊息，告知我們得把 UNION 框起來，不能讓 ORDER BY 的 IN list 看到（可以用 sub-query、CTE、或是塞到 FROM 裡）。這邊用 CTE 騙過去
edb=# WITH xx AS NOT MATERIALIZED(
SELECT schemaname, tablename FROM pg_tables
WHERE schemaname = 'pg_catalog'
UNION
SELECT schemaname, tablename FROM pg_tables
WHERE schemaname = 'sys'
)
SELECT * FROM xx
ORDER BY (schemaname IN ('pg_catalog', 'sys')) DESC, tablename;
schemaname | tablename
------------+-------------------------------------
sys | callback_queue_table
sys | dual
sys | edb$session_wait_history
sys | edb$session_waits
sys | edb$snap
sys | edb$stat_all_indexes
sys | edb$stat_all_tables
sys | edb$stat_database
sys | edb$statio_all_indexes
sys | edb$statio_all_tables
sys | edb$system_waits
sys | edb_dbms_redact_full_default_values
pg_catalog | edb_dir
pg_catalog | edb_last_ddl_time
pg_catalog | edb_last_ddl_time_shared
pg_catalog | edb_password_history
pg_catalog | edb_policy
pg_catalog | edb_profile
pg_catalog | edb_queue
pg_catalog | edb_queue_callback
pg_catalog | edb_queue_table
pg_catalog | edb_redaction_column
pg_catalog | edb_redaction_policy
pg_catalog | edb_resource_group
pg_catalog | edb_variable
pg_catalog | pg_aggregate
pg_catalog | pg_am
pg_catalog | pg_amop
edb=#
以上手法，就能省略好多行 script/程式了～
另外在紀錄一種，稍稍的不同用途：這次只要特定內容，但保持提供的 list 順序。
有時候會把 list 弄進 array 在利用 UNNEST() 轉成多筆資料（上一篇筆記有）。
不過這邊的點，是 Set-Returning function 額外支援的 WITH ORDINALITY ... USING 修飾字。其中 WITH ORDINALITY 所出來長的兩欄位分別是 JOIN 左右兩邊
edb=# SELECT t.schemaname, t.tablename FROM pg_tables t JOIN unnest('{pg_catalog, sys}'::text[]) WITH ORDINALITY x(schemaname, ord) USING (schemaname) ORDER BY x.ord, t.tablename; schemaname | tablename ------------+------------------------------------- pg_catalog | edb_dir pg_catalog | edb_last_ddl_time pg_catalog | edb_last_ddl_time_shared pg_catalog | edb_password_history pg_catalog | edb_policy pg_catalog | edb_profile pg_catalog | edb_queue pg_catalog | edb_queue_callback pg_catalog | edb_queue_table pg_catalog | edb_redaction_column pg_catalog | edb_redaction_policy pg_catalog | edb_resource_group pg_catalog | edb_variable pg_catalog | pg_aggregate pg_catalog | pg_am pg_catalog | pg_amop pg_catalog | pg_amproc pg_catalog | pg_attrdef pg_catalog | pg_attribute pg_catalog | pg_auth_members pg_catalog | pg_authid pg_catalog | pg_cast pg_catalog | pg_class pg_catalog | pg_collation pg_catalog | pg_constraint pg_catalog | pg_conversion pg_catalog | pg_database pg_catalog | pg_db_role_setting pg_catalog | pg_default_acl pg_catalog | pg_depend pg_catalog | pg_description pg_catalog | pg_enum pg_catalog | pg_event_trigger pg_catalog | pg_extension pg_catalog | pg_foreign_data_wrapper pg_catalog | pg_foreign_server pg_catalog | pg_foreign_table pg_catalog | pg_index pg_catalog | pg_inherits pg_catalog | pg_init_privs pg_catalog | pg_language pg_catalog | pg_largeobject pg_catalog | pg_largeobject_metadata pg_catalog | pg_namespace pg_catalog | pg_opclass pg_catalog | pg_operator pg_catalog | pg_opfamily pg_catalog | pg_partitioned_table pg_catalog | pg_policy pg_catalog | pg_proc pg_catalog | pg_publication pg_catalog | pg_publication_rel pg_catalog | pg_range pg_catalog | pg_replication_origin pg_catalog | pg_rewrite pg_catalog | pg_seclabel pg_catalog | pg_sequence pg_catalog | pg_shdepend pg_catalog | pg_shdescription pg_catalog | pg_shseclabel pg_catalog | pg_statistic pg_catalog | pg_statistic_ext pg_catalog | pg_statistic_ext_data pg_catalog | pg_subscription pg_catalog | pg_subscription_rel pg_catalog | pg_synonym pg_catalog | pg_tablespace pg_catalog | pg_transform pg_catalog | pg_trigger pg_catalog | pg_ts_config pg_catalog | pg_ts_config_map pg_catalog | pg_ts_dict pg_catalog | pg_ts_parser pg_catalog | pg_ts_template pg_catalog | pg_type pg_catalog | pg_user_mapping sys | callback_queue_table sys | dual sys | edb$session_wait_history sys | edb$session_waits sys | edb$snap sys | edb$stat_all_indexes sys | edb$stat_all_tables sys | edb$stat_database sys | edb$statio_all_indexes sys | edb$statio_all_tables sys | edb$system_waits sys | edb_dbms_redact_full_default_values sys | plsql_profiler_rawdata sys | plsql_profiler_runs sys | plsql_profiler_units sys | product_component_version edb=#
不過其實這招，把 INNER JOIN 改 LEFT JOIN 就有這篇筆記原本要的效果了～
edb=# SELECT t.schemaname, t.tablename FROM pg_tables t LEFT JOIN unnest('{pg_catalog, sys}'::text[]) WITH ORDINALITY x(schemaname, ord) USING (schemaname) ORDER BY x.ord, t.tablename; schemaname | tablename ------------+------------------------------------- pg_catalog | edb_dir pg_catalog | edb_last_ddl_time pg_catalog | edb_last_ddl_time_shared pg_catalog | edb_password_history pg_catalog | edb_policy pg_catalog | edb_profile pg_catalog | edb_queue pg_catalog | edb_queue_callback pg_catalog | edb_queue_table pg_catalog | edb_redaction_column pg_catalog | edb_redaction_policy pg_catalog | edb_resource_group pg_catalog | edb_variable pg_catalog | pg_aggregate pg_catalog | pg_am pg_catalog | pg_amop pg_catalog | pg_amproc pg_catalog | pg_attrdef pg_catalog | pg_attribute pg_catalog | pg_auth_members pg_catalog | pg_authid pg_catalog | pg_cast pg_catalog | pg_class pg_catalog | pg_collation pg_catalog | pg_constraint pg_catalog | pg_conversion pg_catalog | pg_database pg_catalog | pg_db_role_setting pg_catalog | pg_default_acl pg_catalog | pg_depend pg_catalog | pg_description pg_catalog | pg_enum pg_catalog | pg_event_trigger pg_catalog | pg_extension pg_catalog | pg_foreign_data_wrapper pg_catalog | pg_foreign_server pg_catalog | pg_foreign_table pg_catalog | pg_index pg_catalog | pg_inherits pg_catalog | pg_init_privs pg_catalog | pg_language pg_catalog | pg_largeobject pg_catalog | pg_largeobject_metadata pg_catalog | pg_namespace pg_catalog | pg_opclass pg_catalog | pg_operator pg_catalog | pg_opfamily pg_catalog | pg_partitioned_table pg_catalog | pg_policy pg_catalog | pg_proc pg_catalog | pg_publication pg_catalog | pg_publication_rel pg_catalog | pg_range pg_catalog | pg_replication_origin pg_catalog | pg_rewrite pg_catalog | pg_seclabel pg_catalog | pg_sequence pg_catalog | pg_shdepend pg_catalog | pg_shdescription pg_catalog | pg_shseclabel pg_catalog | pg_statistic pg_catalog | pg_statistic_ext pg_catalog | pg_statistic_ext_data pg_catalog | pg_subscription pg_catalog | pg_subscription_rel pg_catalog | pg_synonym pg_catalog | pg_tablespace pg_catalog | pg_transform pg_catalog | pg_trigger pg_catalog | pg_ts_config pg_catalog | pg_ts_config_map pg_catalog | pg_ts_dict pg_catalog | pg_ts_parser pg_catalog | pg_ts_template pg_catalog | pg_type pg_catalog | pg_user_mapping sys | callback_queue_table sys | dual sys | edb$session_wait_history sys | edb$session_waits sys | edb$snap sys | edb$stat_all_indexes sys | edb$stat_all_tables sys | edb$stat_database sys | edb$statio_all_indexes sys | edb$statio_all_tables sys | edb$system_waits sys | edb_dbms_redact_full_default_values sys | plsql_profiler_rawdata sys | plsql_profiler_runs sys | plsql_profiler_units sys | product_component_version (92 rows) edb=#
上面這兩種，直接在 psql 底下看起來比較沒什麼，不過當 list 是從 script/程式碼處理得到的清單，這時候套用就會感覺到效果了～
參考資料
How to prioritize rows with specific condition when on Postgres? - Stack Overflow
How to have a custom sort order for a union query in Postgres - Stack Overflow
sql - ORDER BY the IN value list - Stack Overflow
沒有留言:
張貼留言