Convert bigint to bytea, but swap the byte order

Question

I have a PostgreSQL table that I want to alter a column from bigint to bytea byte to hold more data. I am thinking using the following sequence:

1. alter table mytable add new_column
2. update mytable set new_column = int8send(old_column)
3. alter table drop old_column
4. alter table rename new_column to old_column

The above sequence works, the only problem is that I want the byte sequence in the bytea to be reversed. For example, if a value in old_column is 0x1234567890abcdef, the above sequence would generate \0224Vx\220\253\315\357, but I want it to be \357\315\253\220xV4\022. Seems like the resulting bytea uses the big-endian order from originating bigint.

Is there an easy way to do that without writing a program? I was looking for a swap64() sort of function in PostgreSQL but failed to find one.

Answer 1

Here's a pure-SQL function I wrote to reverse the byte-order of a bytea-type value:

CREATE OR REPLACE FUNCTION reverse_bytes_iter(bytes bytea, length int, midpoint int, index int)
RETURNS bytea AS
$$
  SELECT CASE WHEN index >= midpoint THEN bytes ELSE
    reverse_bytes_iter(
      set_byte(
        set_byte(bytes, index, get_byte(bytes, length-index)),
        length-index, get_byte(bytes, index)
      ),
      length, midpoint, index + 1
    )
  END;
$$ LANGUAGE SQL IMMUTABLE;

CREATE OR REPLACE FUNCTION reverse_bytes(bytes bytea) RETURNS bytea AS
'SELECT reverse_bytes_iter(bytes, octet_length(bytes)-1, octet_length(bytes)/2, 0)'
LANGUAGE SQL IMMUTABLE;

I just wrote it yesterday, so it's not particularly well-tested nor optimized, but it seems to work, at least on byte strings up to 1k in length.

Answer 2

It is possible to byte-swap without plpgsql code using regexp extractions on the hexadecimal representation. Here's an example to swap a bigint constant, assuming SET standard_conforming_strings to ON (the default with PG 9.1)

select regexp_replace( lpad(to_hex(x'123456789abcd'::bigint),16,'0'),
 '(\w\w)(\w\w)(\w\w)(\w\w)(\w\w)(\w\w)(\w\w)(\w\w)',
 '\8\7\6\5\4\3\2\1');

It returns cdab896745230100. Then apply decode(value, 'hex') to convert that to a bytea.

The whole type conversion could actually be done in a single SQL statement:

ALTER TABLE mytable ALTER COLUMN old_column TYPE bytea
  USING decode(
    regexp_replace( lpad(to_hex(old_column), 16,'0'),
 '(\w\w)(\w\w)(\w\w)(\w\w)(\w\w)(\w\w)(\w\w)(\w\w)',
 '\8\7\6\5\4\3\2\1')
  , 'hex');

Answer 3

I am playing with pageinspect module now, and I was also curios how to change byte order of an existing bytea value, which pretty much matches your case.

I've come up with the following function:

CREATE OR REPLACE FUNCTION reverse(bytea) RETURNS bytea AS $reverse$
    SELECT string_agg(byte,''::bytea)
      FROM (
        SELECT substr($1,i,1) byte
          FROM generate_series(length($1),1,-1) i) s
$reverse$ LANGUAGE sql;

It's pretty straightforward and works similar to textual reverse() function:

WITH v(val) AS (
    VALUES ('\xaabbccdd'::bytea),('\x0123456789abcd'::bytea)
)
SELECT val, reverse(val)
  FROM v;

Answer 4

This function, while not exactly what you're looking for, should help you get on your way.

The source code from that function is reproduced verbatim below.

CREATE OR REPLACE FUNCTION utils.int_littleendian(v_number integer) 
  RETURNS bytea AS 
$BODY$ 
DECLARE 
        v_textresult bytea; 
        v_temp int; 
        v_int int; 
        v_i int = 0; 
BEGIN 
        v_int = v_number; 
        v_textresult = '1234'; 
        WHILE(v_i < 4) LOOP 
                raise notice 'loop %',v_int; 
                v_temp := v_int%256; 
                v_int := v_int - v_temp; 
                v_int := v_int / 256; 
                SELECT set_byte(v_textresult,v_i,v_temp) INTO v_textresult; 
                v_i := v_i + 1; 
        END LOOP; 
        return v_textresult; 
END; 

$BODY$ 
  LANGUAGE 'plpgsql' VOLATILE 
  COST 100;