How to display encoded byte stream of a varchar column in PostgreSQL_cannot apply 'sum' to arguments of type 'sum(<varc-优快云博客

本文介绍如何在PostgreSQL中创建函数以显示VARCHAR列的字节流编码，通过实例演示了如何使用该函数查看不同字符串的字节表示。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

参考自：

https://planet.postgresql.org/

How to display encoded byte stream of a varchar column in PostgreSQL

作者：Abbas Butt ，EDB

C:\Users\Administrator>d:

D:\>cd highgo\database\5.6.3

D:\highgo\database\5.6.3>cd bin

D:\highgo\database\5.6.3\bin>psql -Uhighgo -d highgo -p 5868
用户 highgo 的口令：
psql (5.6.3)

PSQL: Release 5.6.3
Connected to:
HighGo Database V5.6 Debug Edition Release 5.6.3 - 64-bit Production

输入 "help" 来获取帮助信息.

highgo=# CREATE TABLE varchar_demo(description varchar(200));
CREATE TABLE
highgo=# INSERT INTO varchar_demo VALUES('瀚高股份');
INSERT 0 1
highgo=# INSERT INTO varchar_demo VALUES('ABCDE');
INSERT 0 1
highgo=# SELECT * FROM varchar_demo;
 description
-------------
 瀚高股份
 ABCDE
(2 行记录)


highgo=# CREATE OR REPLACE FUNCTION dump(columnname TEXT, tablename TEXT,
highgo(#                                 rowid INT) RETURNS TEXT
highgo-# LANGUAGE plpgsql
highgo-# AS
highgo-# $function$
highgo$# DECLARE
highgo$#   hex_str TEXT;
highgo$#   hex_byte TEXT;
highgo$#   query TEXT;
highgo$#   i INT;
highgo$#   len INT;
highgo$# BEGIN
highgo$#   query := format('SELECT octet_length(%I) FROM %I offset %s
highgo$#                    LIMIT 1', columnname, tablename, rowid);
highgo$#   EXECUTE query INTO len;
highgo$#   hex_str := 'Len=' || len || ' ';
highgo$#   FOR i in 1..len LOOP
highgo$#     query := format('SELECT to_hex(get_byte(%I::bytea, %s))
highgo$#                      FROM %I offset %s LIMIT 1',
highgo$#                      columnname, i - 1, tablename, rowid);
highgo$#     EXECUTE query INTO hex_byte;
highgo$#     hex_str := hex_str || hex_byte || ',';
highgo$#   END LOOP;
highgo$#   RETURN hex_str;
highgo$# END;
highgo$# $function$;
CREATE FUNCTION
highgo=#
highgo=# select dump('description', 'varchar_demo', 0);
                    dump
---------------------------------------------
 Len=12 e7,80,9a,e9,ab,98,e8,82,a1,e4,bb,bd,
(1 行记录)


highgo=# select dump('description', 'varchar_demo', 1);
         dump
-----------------------
 Len=5 41,42,43,44,45,
(1 行记录)


highgo=#