bytea in postgres storing and retrieving bytes

Question

I am trying to understand how to work with binary data in postgresql (v 8.3). Let's say I have a following table

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)

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?

I also need to get the message back and parse it. I was looking at the get_byte function, unless there is another way to parse the data out..

Thanks!

Answer 1

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.

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.

1) first step: define a SQL composite type representing an non-packed record.

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

2) make a function to pack the record value into 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) make a function to unpack bytea into the composite type:

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) usage:

# 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