CODEWITHSUNDEEP
sqlpostgresql
user6339518
user6339518

Reputation:

SQL function to convert NUMERIC to BYTEA and BYTEA to NUMERIC

In PostgreSQL, how can I convert a NUMERIC value to a BYTEA value? And BYTEA to NUMERIC? Using TEXT values I can use CONVERT_TO() and CONVERT_FROM(). Is there anything simmilar? If not, how would it be the SQL function code?

Upvotes: 1

Views: 7061

Answers (2)

gernot
gernot

Reputation: 226

Here are functions tested with PG 11. Note that numeric2bytea handles only nonnegative numbers.

CREATE OR REPLACE FUNCTION bytea2numeric(_b BYTEA) RETURNS NUMERIC AS $$
DECLARE
    _n NUMERIC := 0;
BEGIN
    FOR _i IN 0 .. LENGTH(_b)-1 LOOP
        _n := _n*256+GET_BYTE(_b,_i);
    END LOOP;
    RETURN _n;
END;
$$ LANGUAGE PLPGSQL IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION numeric2bytea(_n NUMERIC) RETURNS BYTEA AS $$
DECLARE
    _b BYTEA := '\x';
    _v INTEGER;
BEGIN
    WHILE _n > 0 LOOP
        _v := _n % 256;
        _b := SET_BYTE(('\x00' || _b),0,_v);
        _n := (_n-_v)/256;
    END LOOP;
    RETURN _b;
END;
$$ LANGUAGE PLPGSQL IMMUTABLE STRICT;

Example:

=> select bytea2numeric('\xdeadbeef00decafbad00cafebabe');
           bytea2numeric            
------------------------------------
 4516460495214885311638200605653694
(1 row)

=> select numeric2bytea(4516460495214885311638200605653694);
         numeric2bytea          
--------------------------------
 \xdeadbeef00decafbad00cafebabe
(1 row)

Upvotes: 5

B. P. Singh
B. P. Singh

Reputation: 13

I think that VARBINARY is used to store in sql for bytea. so that convert to numeric to byte use the flowing script

select CONVERT(VARBINARY,10)

and answer will be 0x0000000A and VARBINARY to numeric 

select CONVERT(int,0x0000000A)

and answer will be 10

Upvotes: -3

Related Questions