Oracle提供了dump函式顯示欄位的十六進位制值,PostgreSQL可透過to_hex函式來進行模擬.

**Oracle**
測試指令碼:
```
TEST-orcl@DESKTOP-V430TU3>CREATE TABLE t_dumpdemo(description varchar(200));

Table created.

TEST-orcl@DESKTOP-V430TU3>
TEST-orcl@DESKTOP-V430TU3>INSERT INTO t_dumpdemo VALUES('中文');

1 row created.

TEST-orcl@DESKTOP-V430TU3>INSERT INTO t_dumpdemo VALUES('ABCDE');

1 row created.

TEST-orcl@DESKTOP-V430TU3>
TEST-orcl@DESKTOP-V430TU3>SELECT * FROM t_dumpdemo;

DESCRIPTION
--------------------------------------------------------------------------------
中文
ABCDE

```
dump十六進位制值
```
TEST-orcl@DESKTOP-V430TU3>select dump(description,1016) from t_dumpdemo;

DUMP(DESCRIPTION,1016)
--------------------------------------------------------------------------------
Typ=1 Len=4 CharacterSet=ZHS16GBK: d6,d0,ce,c4
Typ=1 Len=5 CharacterSet=ZHS16GBK: 41,42,43,44,45

```
資料庫是用GBK編碼,因此'中文'字元編碼為GBK編碼.

**PG**
建立dump函式
```
[local]:5432 pg12@testdb=# CREATE OR REPLACE FUNCTION dump(anynonarray) RETURNS TEXT
pg12@testdb-# LANGUAGE plpgsql
pg12@testdb-# AS
pg12@testdb-# $function$
pg12@testdb$# DECLARE 
pg12@testdb$#   v_hexstr TEXT;
pg12@testdb$#   v_hexbyte TEXT;
pg12@testdb$#   v_tmp TEXT;
pg12@testdb$#   i INT;
pg12@testdb$#   v_len INT;
pg12@testdb$# BEGIN
pg12@testdb$#   SELECT octet_length($1) into v_len;
pg12@testdb$#   v_hexstr := 'Len=' || v_len || ' ';
pg12@testdb$#   v_tmp := ',';
pg12@testdb$#   FOR i in 1..v_len LOOP
pg12@testdb$#     select to_hex(get_byte($1::bytea, i-1)) into v_hexbyte;
pg12@testdb$#     if i = v_len then
pg12@testdb$#       v_tmp := '';
pg12@testdb$#     end if;
pg12@testdb$#     v_hexstr := v_hexstr || v_hexbyte || v_tmp;
pg12@testdb$#   END LOOP;
pg12@testdb$#   -- SELECT encode($1::bytea,'hex') into v_hexbyte;
pg12@testdb$#   -- v_hexstr := v_hexstr||','||v_hexbyte;
pg12@testdb$#   RETURN v_hexstr;
pg12@testdb$# END;
pg12@testdb$# $function$;
CREATE FUNCTION
Time: 3.621 ms
```
執行dump函式,顯示十六進位制值
```
[local]:5432 pg12@testdb=# select dump(description) from t_dumpdemo;
          dump           
-------------------------
 Len=6 e4,b8,ad,e6,96,87
 Len=5 41,42,43,44,45
(2 rows)

Time: 1.337 ms
```

**參考資料**
[ How to display encoded byte stream of a varchar column in PostgreSQL](https://abbas-technical.blogspot.com/2019/07/how-to-display-encoded-byte-stream-of.html)

PostgreSQL DBA(73) - dump函式

相關文章