将bytea转换为二进制字符串

Question

我需要解码一个base64字符串并提取一部分的二进制数据。

在Postgres中，是否有将bytea转换为二进制字符串表示形式的SQL函数？
（类似于"00010001010101010"。）

- GPif

Answer 1

如果您的Postgres安装使用默认设置bytea_output = 'hex'，那么有一个非常简单的hack：

SELECT right(bytea_col::text, -1)::varbit;

例子：

SELECT right((bytea '\xDEADBEEF')::text, -1)::varbit;

结果：

'11011110101011011011111011101111'

right(text, -1) 是从文本表示中移除前导反斜杠的最便宜的方法。

varbit (标准 SQL 名称 bit varying) 用于任意长度的比特串。如果需要，将结果转换为 text 或 varchar。

相关内容，包含解释：

• 将文本表示中的十六进制转换为十进制数

- Erwin Brandstetter

Answer 2

您可以将以下代码放入一个函数中：

演示：db<>fiddle

WITH byte AS (   -- 1
    SELECT E'\\xDEADBEEF'::bytea as value
)
SELECT
    string_agg(      -- 5
        get_byte(value, gs)::bit(8)::text -- 4
        , ''
    )
FROM 
    byte,
    generate_series(        -- 3
        0, 
        length(value) - 1   -- 2
    ) gs

我在fiddle中展示了查询的开发过程。

1. WITH子句封装了bytea值，以便在后续代码中重复使用
2. length()计算bytea值的二进制长度
3. generate_series()创建一个列表，从0到length - 1(例如我的例子中是0-3)
4. get_byte()第二次获取bytea值，并给出位置为gs（先前计算的值0-3）的字节。这给出字节的integer表示。然后将此函数的结果强制转换为其二进制表示形式(1 byte = 8 bit)的bit(8)类型。
5. string_agg()最后将所有二进制字符串聚合成一个字符串。(没有分隔符，只取其text表示而非bit类型)

---

一个函数的样例如下：

CREATE OR REPLACE FUNCTION to_bit(value bytea) RETURNS SETOF text AS 
$$
BEGIN
    RETURN QUERY 
        SELECT
            string_agg(get_byte(value, gs)::bit(8)::text, '')
        FROM 
            generate_series(0, length(value) - 1) gs;
END;
$$ LANGUAGE plpgsql;

之后，您可以称其为：

SELECT to_bit(E'\\xDEADBEEF'::bytea)

---

您可以尝试使用get_bit()代替get_byte()。这样可以避免::bit(8)的强制转换，但是需要确实用因子8乘以长度。

生成的位字符串具有不同的位顺序，但也许更适合您的用例:

WITH byte AS (
    SELECT E'\\xDEADBEEF'::bytea as value
)
SELECT
    string_agg(get_bit(value, gs)::text, '')
FROM 
    byte,
    generate_series(0, length(value) * 8 - 1) gs

示例：db<>fiddle

- S-Man