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!

If you can use plperl, [pack()](http://perldoc.perl.org/functions/pack.html) and unpack() are what you want. But there's no equivalent in the PG [built-in](http://www.postgresql.org/docs/8.3/static/functions-binarystring.html) SQL functions. Or consider doing it client-side. — Daniel Vérité, Apr 03 '12 at 08:36
Any special reason why you want to pack those values into a bytea column? Why not just three numeric columns? If you really want to do it you would better build a function in an external language like Python or Perl as suggested by @DanielVérité in his answer. — Clodoaldo Neto, Apr 03 '12 at 18:52
The reason is I am dealing with existing architecture that has that message field as bytea, and that is what I have to use. This really surprise me that there is no support for that in pg. I was aware of pack function for example in python. — t0x13, Apr 03 '12 at 18:55

score 2 · Answer 1 · answered Apr 03 '12 at 17:55

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

Thank you for taking your time to write this. Impressive! I am just not sure that this will work for me.. — t0x13, Apr 04 '12 at 01:34
I am also trying to understand how the whole binary packing works as well. In your example `40420f00` is 1000000, however when I use an online calculator it shows 'f4240`.. does this have to do with some kind of byte rearrangement? — t0x13, Apr 04 '12 at 01:44
Yes, this is the [little endian](http://en.wikipedia.org/wiki/Endianness) order of bytes, it's the "native" one on x86 machines. Big endian could be obtained with "CCL>" template for pack instead of "CCL" — Daniel Vérité, Apr 04 '12 at 02:14

score 1 · Accepted Answer · answered Apr 17 '12 at 02:23

So I was able to figure out how to do it in plpg Here's the code to pack

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.

thanks for this! can you explain `v_sql := $$SELECT B$$||quote_literal(v_byte)||$$::int8$$;`? I'm running into an issue getting this to work because of this line — pyramation, Feb 05 '20 at 09:30

bytea in postgres storing and retrieving bytes

2 Answers2