postgresql postgres 中的 bytea 存储和检索字节
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9987215/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
bytea in postgres storing and retrieving bytes
提问by t0x13
I am trying to understand how to work with binary data in postgresql (v 8.3). Let's say I have a following table
我试图了解如何在 postgresql (v 8.3) 中使用二进制数据。假设我有一张下表
Table "public.message"
Column | Type | Modifiers
---------+---------+-----------
id | integer |
message | bytea |
I would like to store a packet in the message field in this format:
我想以这种格式在消息字段中存储一个数据包：
version (1 byte), identifier (1 byte), epoch (4 bytes)
版本（1 个字节）、标识符（1 个字节）、纪元（4 个字节）
I would like to pack this data into the message field. Lets say I have version=1, identifier=8 and epoch=123456. How would I pack this data into the message field? How would I convert my integer values to hex.. or octal?
我想将此数据打包到消息字段中。假设我有 version=1，identifier=8 和 epoch=123456。我如何将这些数据打包到消息字段中？我如何将我的整数值转换为十六进制.. 或八进制？
I also need to get the message back and parse it. I was looking at the get_bytefunction, unless there is another way to parse the data out..
我还需要取回消息并解析它。我正在查看该get_byte函数，除非有另一种方法来解析数据。
Thanks!
谢谢！
回答by Daniel Vérité
Here is some sample code showing how to do it with server-side Perl. Annoyingly, pack/unpack are considered untrusted operations by PG so this has to be created with plperlu by a superuser and then access granted with GRANT EXECUTE to non superusers.
下面是一些示例代码，展示了如何使用服务器端 Perl 来做到这一点。令人讨厌的是，打包/解包被 PG 视为不受信任的操作，因此必须由超级用户使用 plperlu 创建它，然后使用 GRANT EXECUTE 授予非超级用户的访问权限。
On the other hand, this choice of language makes it easy to deal with more complex packed structures, which is a significant advantage over code that would be based on the SQL get_bytes()/set_bytes() functions. See Perl's pack() features.
另一方面，这种语言选择使得处理更复杂的打包结构变得容易，这与基于 SQL get_bytes()/set_bytes() 函数的代码相比具有显着优势。请参阅Perl 的 pack() 功能。
1) first step: define a SQL composite type representing an non-packed record.
1) 第一步：定义一个 SQL 复合类型，表示一个非压缩记录。
create type comp as (a smallint, b smallint, c int);
2) make a function to pack the record value into bytea:
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())!ge; # from PG doc
return $retval;
$body$ language plperlu;
3) make a function to unpack bytea into the composite type:
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}))! ? chr(oct()) : "\"!ge;
my ($v,$i,$e)= unpack("CCL", $arg);
return {"a"=>$v, "b"=>$i, "c"=>$e};
$body$ language plperlu;
4) usage:
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
回答by t0x13
So I was able to figure out how to do it in plpgHere's the code to pack
所以我能够弄清楚如何在plpg这是要打包的代码中做到这一点
CREATE FUNCTION pack_numeric_bytes(i_values NUMERIC[], i_byte_sizes NUMERIC[], i_big_endian BOOLEAN)
RETURNS BYTEA
DECLARE
v_bytes BYTEA := NULL;
v_start INTEGER := 1;
v_byte BYTEA;
v_byte_size INTEGER;
v_value NUMERIC;
v_binary_value TEXT;
v_num NUMERIC;
i INTEGER;
x INTEGER;
v_sql TEXT;
BEGIN
IF array_upper(i_values, 1) != array_upper(i_byte_sizes, 1) THEN
RETURN v_bytes;
END IF;
FOR x IN array_lower(i_values, 1) .. array_upper(i_values, 1) LOOP
/* Given value and size at x position */
v_byte_size := i_byte_sizes[x]::INTEGER;
v_value := i_values[x];
/* Convert number to binary form */
v_sql := $$SELECT $$|| v_value ||$$::bit($$|| v_byte_size*8 ||$$);$$;
EXECUTE v_sql INTO v_binary_value;
IF i_big_endian IS TRUE THEN
/* Convert each byte at a time */
FOR i IN 1 .. v_byte_size LOOP
/* Extract byte from our binary value.
Big endian starts at 1 and then increments of 8 */
v_byte := substring(v_binary_value, v_start, 8);
/* Convert binary 8 bits to an integer */
v_sql := $$SELECT B$$||quote_literal(v_byte)||$$::int8$$;
EXECUTE v_sql INTO v_num;
/* Build bytea of bytes */
v_bytes := COALESCE(v_bytes, '') || set_byte(E' '::BYTEA, 0, v_num::INTEGER);
v_start := v_start + 8;
END LOOP;
ELSE
/* Small endian is extracted starting from last byte */
v_start := (v_byte_size * 8) + 1;
/* Convert each byte at a time */
FOR i IN 1 .. v_byte_size LOOP
v_start := v_start - 8;
v_byte := substring(v_binary_value, v_start, 8);
/* Convert binary 8 bits to an integer */
v_sql := $$SELECT B$$||quote_literal(v_byte)||$$::int8$$;
EXECUTE v_sql INTO v_num;
/* Build bytea of bytes */
v_bytes := COALESCE(v_bytes, '') || set_byte(E' '::BYTEA, 0, v_num::INTEGER);
END LOOP;
END IF; /* END endian check */
v_start := 1;
END LOOP;
RETURN v_bytes;
END;
And here's the code to unpack:
这是解压的代码：
CREATE OR REPLACE FUNCTION public.unpack_numeric_bytes(i_bytes bytea, i_byte_sizes INTEGER[], i_big_endian BOOLEAN)
RETURNS NUMERIC[]
SECURITY DEFINER AS
DECLARE
v_bytes BYTEA;
v_start INTEGER := 1;
v_byte_index INTEGER := 0;
v_bit_shift INTEGER := 0;
v_length INTEGER;
v_size INTEGER;
v_sum_byte_sizes INTEGER;
v_vals NUMERIC[] := '{}';
v_val BIGINT := 0;
i INTEGER;
x INTEGER;
v_sql TEXT;
BEGIN
v_sql := $$SELECT $$|| array_to_string(i_byte_sizes, '+')||$$;$$;
EXECUTE v_sql INTO v_sum_byte_sizes;
IF length(i_bytes) != v_sum_byte_sizes::INTEGER THEN
RETURN v_vals;
END IF;
/* Loop through values of bytea (split by their sizes) */
FOR x IN array_lower(i_byte_sizes, 1) .. array_upper(i_byte_sizes, 1) LOOP
v_size := i_byte_sizes[x];
v_bytes := substring(i_bytes, v_start, v_size);
v_length := length(v_bytes);
IF i_big_endian IS TRUE THEN
v_byte_index := v_length - 1;
FOR i IN 1..v_length LOOP
v_val := v_val + (get_byte(v_bytes, v_byte_index) << v_bit_shift);
v_bit_shift := v_bit_shift + 8;
v_byte_index := v_byte_index - 1;
END LOOP;
ELSE
FOR i IN 1..v_length LOOP
v_val := v_val + (get_byte(v_bytes, v_byte_index) << v_bit_shift);
v_bit_shift := v_bit_shift + 8;
v_byte_index := v_byte_index + 1;
END LOOP;
END IF;
v_vals := array_append(v_vals, v_val::NUMERIC);
/* Calculate next value start index */
v_start := v_start + v_size;
v_byte_index := 0;
v_bit_shift := 0;
v_val := 0;
END LOOP;
RETURN v_vals;
END;
I hope this will help someone.
我希望这会帮助某人。
