- 查詢系統表
- 拼裝出需要的 SQL「指令」匯出到文字檔裡面
- 再把文字檔內容用來當成 SQL Script 執行。
這種用法通常在一次大量變動資料庫物件屬性時還蠻常見的，例如一次變動滿足條件的表格欄位型態。或是一次數所有「非系統表」的表格資料筆數。
在 PGSQL 9.6 裡面，提供了一個簡化的方式來滿足這個願望的 Meta-Command，\gexec。\gexec 用來把一段 SQL 的匯出結果，轉而當成 SQL 來執行，前提當然是 SQL 匯出結果要自己使用字串串接的方式，安排成可以執行的合法 SQL 指令～
以下列出一兩個範例。
1. 大量異動欄位大小的範例：將所有包含 char、varchar 欄位的表格縮小 6 倍
在 PGSQL 9.6 之前的 psql，只有 \g 可以匯出結果到檔案，再用 \i 執行檔案
\t on
select 'alter table '||c.table_schema||'.'||c.table_name||
' alter column '||c.column_name||
' type varchar('||
round(c.character_maximum_length/6)||');'
from information_schema.columns c,
information_schema.tables t
where c.table_schema not in ( 'information_schema','pg_catalog',
'sys')
and c.table_name = t.table_name
and t.table_type = 'BASE TABLE'
and c.data_type = 'character varying'
order by c.table_schema, c.table_name
;
\g ./alter_script.sql
\t off
\i ./alter_script.sql
到了 PG9.6 的 psql 底下，就可以使用 \gexec
select 'alter table '||c.table_schema||'.'||c.table_name||
' alter column '||c.column_name||
' type varchar('||
round(c.character_maximum_length/6)||');'
from information_schema.columns c,
information_schema.tables t
where c.table_schema not in ( 'information_schema','pg_catalog',
'sys')
and c.table_name = t.table_name
and t.table_type = 'BASE TABLE'
and c.data_type = 'character varying'
order by c.table_schema, c.table_name
;
\gexec
接著再一個例子。
2. 計數指定 Schema 裡面所有表格資料筆數的方式：在這裡排除系統 Schema，共有 information_schema, pg_catalog, sys（企業版的） 等。以下的內容也能放到 一個文字檔裡作為 Script 執行：
-- psql Variables \set schema_list 'information_schema','pg_catalog', 'sys' -- psql Formatting and Output \o ./out.txt \pset format unaligned \pset fieldsep '\t' \f ' , count= ' -- \x \t on -- Execute select 'SELECT ''' || schemaname||'.'||tablename || ''' AS Table_name, count(*) AS row_num FROM ' || schemaname||'.'||tablename || ';' from pg_tables where schemaname not in (:'schema_list') order by schemaname, tablename \gexec \q
注意上面內容中，兩個單引號是 Postgres 字串中的 single quote escape。
經過試驗，確定用新版的 psql 能連到舊版資料庫以及 Greenplum 4.x（基於 Postgres 8.x）使用這些 Meta-Command。
參考資料
沒有留言:
張貼留言