如何在PostgreSQL中解析LoRa设备打包数据实现struct.unpack效果

首先明确你的打包规则对应的字节结构（网络大端序）：

• 前2字节：无符号短整型 设备ID
• 后续每4字节为1个大端单精度浮点数，依次是temp、humid、ph1、ph2、ph3、volt1、volt2、volt3

方案1：直接SQL查询（无需额外扩展） #

假设你的表名为lora_upload，存储二进制打包数据的字段名为payload，直接用如下查询即可提取所有字段：

SELECT
    -- 解析2字节大端设备ID
    ('x' || encode(substring(payload FROM 1 FOR 2), 'hex'))::bit(16)::integer AS device_id,
    -- 解析8个大端单精度浮点数
    ('x' || encode(substring(payload FROM 3 FOR 4), 'hex'))::bit(32)::real AS temp,
    ('x' || encode(substring(payload FROM 7 FOR 4), 'hex'))::bit(32)::real AS humid,
    ('x' || encode(substring(payload FROM 11 FOR 4), 'hex'))::bit(32)::real AS ph1,
    ('x' || encode(substring(payload FROM 15 FOR 4), 'hex'))::bit(32)::real AS ph2,
    ('x' || encode(substring(payload FROM 19 FOR 4), 'hex'))::bit(32)::real AS ph3,
    ('x' || encode(substring(payload FROM 23 FOR 4), 'hex'))::bit(32)::real AS volt1,
    ('x' || encode(substring(payload FROM 27 FOR 4), 'hex'))::bit(32)::real AS volt2,
    ('x' || encode(substring(payload FROM 31 FOR 4), 'hex'))::bit(32)::real AS volt3
FROM lora_upload;

方案2：封装为自定义函数（更适合Grafana重复调用） #

可以把解析逻辑封装成PostgreSQL函数，使用时直接传入payload字段即可：

CREATE OR REPLACE FUNCTION unpack_lora_payload(payload bytea)
RETURNS TABLE(
    device_id integer,
    temp real,
    humid real,
    ph1 real,
    ph2 real,
    ph3 real,
    volt1 real,
    volt2 real,
    volt3 real
) AS $$
BEGIN
    RETURN QUERY SELECT
        ('x' || encode(substring(payload FROM 1 FOR 2), 'hex'))::bit(16)::integer,
        ('x' || encode(substring(payload FROM 3 FOR 4), 'hex'))::bit(32)::real,
        ('x' || encode(substring(payload FROM 7 FOR 4), 'hex'))::bit(32)::real,
        ('x' || encode(substring(payload FROM 11 FOR 4), 'hex'))::bit(32)::real,
        ('x' || encode(substring(payload FROM 15 FOR 4), 'hex'))::bit(32)::real,
        ('x' || encode(substring(payload FROM 19 FOR 4), 'hex'))::bit(32)::real,
        ('x' || encode(substring(payload FROM 23 FOR 4), 'hex'))::bit(32)::real,
        ('x' || encode(substring(payload FROM 27 FOR 4), 'hex'))::bit(32)::real,
        ('x' || encode(substring(payload FROM 31 FOR 4), 'hex'))::bit(32)::real;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

函数使用示例：

SELECT * FROM unpack_lora_payload('\x000141a4000042a0e3884147fd0f41477c3b4147a5303e0895213e0895213e089521'::bytea);

返回结果和Python struct.unpack 输出完全一致。

注意事项 #

• PostgreSQL的substring对bytea类型的下标从1开始计数，和Python的0开始索引不同，计算偏移时需要注意对应
• 上述方案兼容PostgreSQL 10及以上版本，无需安装任何额外扩展
• 若你存储的是base64编码的字符串而非bytea类型，可先用decode(base64_str, 'base64')转成bytea后再传入解析逻辑

内容的提问来源于stack exchange，提问作者LowRez