Convertir bytea en double précision dans PostgreSQL

Question

J'ai une base de données dans laquelle l'une des tables stocke un blob ( bytea ) de toutes sortes de données génériques collectées à partir d'un autre système. Les bytea peut contenir n'importe quoi. Pour savoir comment interpréter les données, le tableau comporte également un champ de format. J'ai écrit une application Java pour lire le bytea de la base de données en tant que byte[] et je peux ensuite facilement le convertir en double[] o int[] ou tout autre format indiqué dans le champ de format en utilisant ByteBuffer et les différents points de vue ( DoubleBuffer , IntBuffer etc.)

Je me trouve maintenant dans une situation où je dois manipuler les données dans la base de données elle-même au sein d'une fonction de déclenchement afin de maintenir l'intégrité avec une autre table. Je peux trouver des conversions pour à peu près tous les types de données imaginables, mais je ne trouve rien pour passer de bytea (ou même bit ) à double precision et retour. A bytea peut être décomposée, convertie en bits, puis convertie en un int o bigint mais pas un double precision . Par exemple, x'deadbeefdeadbeef'::bit(64)::bigint sera converti en -2401053088876216593 sans problème, mais x'deadbeefdeadbeef'::bit(64)::double precision échoue avec "ERROR : cannot cast type bit to double precision" au lieu de donner la réponse IEEE 754 de -1.1885959257070704E148 .

J'ai trouvé cette réponse https://stackoverflow.com/a/11661849/5274457 qui met en œuvre la norme IEEE pour convertir les bits en doubles, mais n'y a-t-il pas une fonction de conversion de base dans PostgreSQL pour le faire ? De plus, j'ai besoin de revenir en arrière en partant de double precision a bytea lorsque j'ai fini de manipuler les données et que je dois mettre à jour les tables, ce que cette réponse ne permet pas de faire.

Des idées ?

Answer 1

Ok, j'ai trouvé une réponse. Dans PostgreSQL, vous pouvez écrire des fonctions en utilisant Python. Pour permettre l'utilisation de Python, vous devez installer la version spécifique de Python nécessaire à votre installation de PostgreSQL et la rendre disponible dans la variable d'environnement PATH. Vous pouvez trouver la version de Python dont votre installation de PostgreSQL a besoin en consultant les notes d'installation. J'utilise actuellement PostgreSQL 9.6.5 sous Windows et il nécessite Python 3.3. J'ai d'abord essayé la dernière version de Python 3.6, mais cela n'a pas fonctionné. J'ai opté pour la dernière version de Python 3.3 pour Windows, qui est la 3.3.5.

Après avoir installé Python, vous l'activez dans PostgreSQL en exécutant CREATE EXTENSION plpython3u; sur votre base de données comme documenté ici https://www.postgresql.org/docs/current/static/plpython.html . À partir de là, vous pouvez écrire n'importe quelle fonction avec des corps Python.

Dans mon cas particulier, la conversion de bytea a double precision[] et retour, j'ai écrit les fonctions suivantes :

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$;

Dans mon cas, tous les doubles sont stockés en little endian, j'utilise donc < . Je mets également en cache l'importation de la struct dans le dictionnaire global comme décrit dans https://stackoverflow.com/a/15025425/5274457 . J'ai utilisé GD au lieu de SD parce que je veux que l'importation soit disponible dans d'autres fonctions que je pourrais écrire. Pour plus d'informations sur GD et SD, voir https://www.postgresql.org/docs/current/static/plpython-sharing.html .

Pour le voir en action, il faut savoir que les blobs de ma base de données sont stockés en little endian,

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

Et la réponse que j'obtiens est la suivante

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

où 'efbeaddeefbeadde' es 'deadbeefdeadbeef' en little endian.