将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的大端序。 有没有一种简单的方法可以在不编写程序的情况下进行这样的操作？我正在寻找一种在PostgreSQL中类似于swap64()的函数，但未能找到。

- Junping

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;

我昨天刚写的，所以它没有经过特别充分的测试和优化，但至少在长度不超过1k的字节字符串上似乎能够工作。

- Adam Mackler

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')将其转换为bytea。

整个类型转换实际上可以在单个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');

- Daniel Vérité

Answer 3

我现在正在使用pageinspect模块进行编程，并且我还好奇如何更改现有的bytea值的字节顺序，这与您的情况非常相似。

我想出了以下函数：

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;

- vyegorov

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; 

- Mike Sherrill 'Cat Recall'