How to distinguish bytea type for double precision

Question

I have a database column whose type is bytea. It contains floats converted to an array of bytes (4 bytes per float), and the encoding is Escape. I can get the corresponding bytea string using the substring function.

My question is how can I convert a bytea string to a float inside an SQL function. I used to convert to float on the C # side. I used the dataReader.getByte method to extract bytes and then converted to a float using the BitConverter.ToSingle method (.net in the class).

Now I can not use the intermediate component as an Npqsql driver. I want SQL to directly convert bytea to float and return the corresponding number when executing a query from a third-party application.

Thanks Amila

Answer 1

For this purpose, the best possible solution is to convert to bytes using the IEEE754-1985 standard using SQL commands.

You must first check the special cases defined by the IEEE754-1985 standard. Then simply follow the standard conversion algorithm if it is not in any special cases. Sample code below.

The inputs are bytea_value bytea, is_little_endian booleanthen divided by 4 bytes, as shown below:

  byte_array[0]:= get_byte(bytea_value, 0);
  byte_array[1]:= get_byte(bytea_value, 1);
  byte_array[2]:= get_byte(bytea_value, 2);
  byte_array[3]:= get_byte(bytea_value, 3);

Then get the binary value by looking at the small endian or big endian value

IF is_little_endian THEN
        binary_value:= byte_array[0]::bit(8) || byte_array[1]::bit(8) || byte_array[2]::bit(8) || byte_array[3]::bit(8);
    ELSE
        binary_value:= byte_array[3]::bit(8) || byte_array[2]::bit(8) || byte_array[1]::bit(8) || byte_array[0]::bit(8); 
    END IF;

Now check out the special cases:

IF binary_value = '00000000000000000000000000000000' OR binary_value = '10000000000000000000000000000000' THEN -- IEEE754-1985 Zero
        return 0.0;
    END IF;

sign := substring(binary_value from 1 for 1);
    exponent := substring(binary_value from 2 for 8);
    mantissa := substring(binary_value from 10 for 23); 

    IF exponent = '11111111' THEN
        IF mantissa = '00000000000000000000000' THEN   -- IEEE754-1985 negative and positive infinity
            IF sign = '1' THEN                    
                return '-Infinity';                    
            ELSE                    
                return 'Infinity';  
            END IF;                  
        ELSE
          return 'NaN'; -- IEEE754-1985 Not a number
        END IF; 
    END IF;

If this does not apply to any special cases, simply convert them as shown below:

exp := exponent::int;

    IF exp > 126 THEN
     exp := exp - 127;
    ELSE
     exp:= -exp;
    END IF;

    WHILE mantissa_index < 24 LOOP
        IF substring(mantissa from mantissa_index for 1) = '1' THEN
            result := result + power(2, -(mantissa_index));
        END IF;
        mantissa_index = mantissa_index + 1;
    END LOOP;

    result := result * power(2, exp);

    IF(sign = '1') THEN
        result = -result;
    END IF;

    return result;