postgresql - postgres 中的 bytea 存储和检索字节

Question

我想了解如何在 postgresql (v 8.3) 中处理二进制数据。假设我有一个下表

Table "public.message"
Column  |  Type   | Modifiers 
---------+---------+-----------
id      | integer | 
message | bytea   | 

我想以这种格式在消息字段中存储一个数据包:

版本(1字节)，标识符(1字节)，纪元(4字节)

我想将此数据打包到消息字段中。假设我有版本=1、标识符=8 和纪元=123456。我如何将这些数据打包到消息字段中？如何将我的整数值转换为十六进制...或八进制？

我还需要取回消息并解析它。我正在查看 get_byte 函数，除非有另一种方法来解析数据..

谢谢!

Answer 1

这里是一些示例代码，展示了如何使用服务器端 Perl 来完成此操作。令人恼火的是，打包/解包被 PG 认为是不受信任的操作，因此这必须由 super 用户使用 plperlu 创建，然后通过 GRANT EXECUTE 授予非 super 用户访问权限。

另一方面，这种语言选择使得处理更复杂的打包结构变得容易，这与基于 SQL get_bytes()/set_bytes() 函数的代码相比具有显着优势。参见 Perl's pack() features .

1) 第一步:定义表示非打包记录的 SQL 复合类型。

create type comp as (a smallint, b smallint, c int);

2) 编写一个函数将记录值打包到bytea中:

create function pack_comp(comp) returns bytea
as $body$
 my $arg=shift;
 my $retval = pack("CCL", $arg->{a},$arg->{b},$arg->{c});
 # encode bytea according to PG doc. For PG>=9.0, use encode_bytea() instead
 $retval =~ s!(\\|[^ -~])!sprintf("\\%03o",ord($1))!ge; # from PG doc
 return $retval;
$body$ language plperlu;

3) 创建一个函数将 bytea 解压为复合类型:

create or replace function unpack_comp(bytea) returns comp
as $body$
 my $arg=shift;
 # decode bytea according to PG doc. For PG>=9.0, use decode_bytea() instead
 $arg =~ s!\\(?:\\|(\d{3}))!$1 ? chr(oct($1)) : "\\"!ge;
 my ($v,$i,$e)= unpack("CCL", $arg);
 return {"a"=>$v, "b"=>$i, "c"=>$e};
$body$ language plperlu;

4) 用法:

# select encode(pack_comp((254,14,1000000)::comp), 'hex');
    encode    
--------------
 fe0e40420f00

# select unpack_comp(decode('fe0e40420f00','hex'));
   unpack_comp    
------------------
 (254,14,1000000)

 # select * from unpack_comp(decode('fe0e40420f00','hex'));
  a  | b  |    c    
-----+----+---------
 254 | 14 | 1000000