Конвертируйте bigint в bytea, но меняйте порядок байтов

Question

У меня есть таблица PostgreSQL, из которой я хочу изменить столбец bigint в bytea байт для хранения дополнительных данных. Я думаю, используя следующую последовательность:

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

Приведенная выше последовательность работает, единственная проблема в том, что я хочу, чтобы последовательность байтов в bytea быть полностью измененным. Например, если значение в old_column является 0x1234567890abcdefвышеуказанная последовательность будет генерировать \0224Vx\220\253\315\357, но я хочу, чтобы это было\357\315\253\220xV4\022, Похоже, в результате bytea использует порядок с прямым порядком байтов от происхождения bigint,

Есть ли простой способ сделать это без написания программы? Я искал swap64() своего рода функция в PostgreSQL, но не смог ее найти.

Answer 1

Вот функция чистого SQL, которую я написал, чтобы изменить порядок байтов byteaзначение типа:

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;

Я только что написал это вчера, так что он не особенно хорошо протестирован и не оптимизирован, но, похоже, работает, по крайней мере, на байтовых строках длиной до 1 КБ.

Answer 2

Байт-своп можно выполнить без кода plpgsql, используя извлечения регулярных выражений в шестнадцатеричном представлении. Вот пример для замены константы bigint, предполагая, SET standard_conforming_strings to ON (по умолчанию с 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');

Возвращается cdab896745230100, Тогда подать заявку decode(value, 'hex') чтобы преобразовать это в байту.

Целое преобразование типов может фактически быть сделано в единственном операторе SQL:

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

Я играю с pageinspect модуль, и меня также интересовало, как изменить порядок байтов существующего значения байта, что в значительной степени соответствует вашему случаю.

Я придумал следующую функцию:

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;

Это довольно просто и работает аналогично текстовому reverse() функция:

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

Answer 4

Эта функция, хотя и не совсем то, что вы ищете, должна помочь вам в этом.

Исходный код этой функции дословно воспроизведен ниже.

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;