在PostgreSQL中将bytea转换为双精度

问题描述：

我有一个数据库，其中一个表存储从另一个系统收集的各种通用数据的blob（bytea）。 bytea字段可以包含任何内容。为了知道如何解释数据，表格还有一个格式字段。我写了一个Java应用程序从数据库作为byte[]阅读bytea字段，然后我可以很容易地通过使用ByteBuffer和各种视图（DoubleBuffer，IntBuffer等），将其转换为double[]或int[]或任何格式字段说。在PostgreSQL中将bytea转换为双精度

现在我有这种情况，我需要在触发器函数中对数据库本身的数据进行一些操作，以保持与另一个表的完整性。我可以找到几乎任何可以想象的数据类型的转换，但是我找不到任何从bytea（或甚至bit）到double precision并返回的内容。 A bytea可以分解，转换为位，然后转换为int或bigint，但不是double precision。例如，x'deadbeefdeadbeef'::bit(64)::bigint将毫无问题地转换为-2401053088876216593，但x'deadbeefdeadbeef'::bit(64)::double precision将以“ERROR：can not cast type bit to double precision”而不是给出-1.1885959257070704E148的IEEE 754答案失败。

我发现这个答案https://*.com/a/11661849/5274457，它基本上实现了IEEE标准将bits转换为double，但是PostgreSQL中真的没有一个基本的转换函数来做到这一点吗？另外，当我完成操作数据并需要更新表格时，我需要从double precision回退到bytea，这个答案没有提供。

任何想法？

您可能需要一个简单的C扩展才能添加所需的强制转换。 Pg真的可以在原始二进制表单中使用更多的转换函数。 –

我会研究延伸想法。据我所知，这不仅仅是一个PostgreSQL问题。从我可以看出，HSQL和SQLServer有同样的问题。我使用的每种编程语言都有将原始字节转换为双精度的方法，但SQL数据库似乎没有。 – Keith

他们通常在更高的抽象层次上运行。对于PostgreSQL，一个实际的选择可能是使用简单的plperlu或plpythonu过程来完成它，这样你就不必编写C扩展。 –

答

好吧，我找到了答案。在PostgreSQL中，你可以使用Python编写函数。为了启用Python，您必须安装PostgreSQL安装所需的特定版本的Python，并在PATH环境变量中提供它。通过查看安装说明，您可以找到安装PostgreSQL需要的Python版本。我目前在Windows上使用PostgreSQL 9.6.5，它需要Python 3.3。我最初尝试了最新的Python 3.6，但它不起作用。我用最新的3.3.5 for Windows解决了这个问题，这是3.3.5。

安装Python之后，您可以在PostgreSQL中通过在数据库上执行CREATE EXTENSION plpython3u;来启用它，如https://www.postgresql.org/docs/current/static/plpython.html中所述。从那里，你可以用Python机构编写任何函数。

对于我的具体情况，从转换到bytea和double precision[]回来，我写了下面的功能：

CREATE FUNCTION bytea_to_double_array(b bytea) 
    RETURNS double precision[] 
    LANGUAGE 'plpython3u' 
AS $BODY$ 
    if 'struct' in GD: 
    struct = GD['struct'] 
    else: 
    import struct 
    GD['struct'] = struct 

    return struct.unpack('<' + str(int(len(b)/8)) + 'd', b) 
$BODY$; 

CREATE FUNCTION double_array_to_bytea(dblarray double precision[]) 
    RETURNS bytea 
    LANGUAGE 'plpython3u' 
AS $BODY$ 
    if 'struct' in GD: 
    struct = GD['struct'] 
    else: 
    import struct 
    GD['struct'] = struct 

    # dblarray here is really a list. 
    # PostgreSQL passes SQL arrays as Python lists 
    return struct.pack('<' + str(int(len(dblarray))) + 'd', *dblarray) 
$BODY$;

对我来说，所有的双打都存储在小端，所以我用<。我还缓存struct模块在全局字典中的导入，如https://*.com/a/15025425/5274457中所述。我使用GD而不是SD，因为我想在我可能编写的其他函数中使用导入。有关GD和SD的信息，请参阅https://www.postgresql.org/docs/current/static/plpython-sharing.html。

要看到它的行动知道我的数据库中的块存储为小端，

SELECT bytea_to_double_array(decode('efbeaddeefbeadde', 'hex')), encode(double_array_to_bytea(array[-1.1885959257070704E148]), 'hex');

而我得到的答复是

bytea_to_double_array | encode 
double precision[]  | text 
-------------------------+------------------ 
{-1.18859592570707e+148} | efbeaddeefbeadde

其中'efbeaddeefbeadde'是小尾数'deadbeefdeadbeef'。

干得好，谢谢:) –

在PostgreSQL中将bytea转换为双精度

相关推荐