Convert hex string to bigint in Postgres

Question

I'd like to convert a hex string as used by HTML into a bigint to then convert it into separate R, G and B values in Postgres via a function written in PL/pgSQL.

I can decode the string into bytea like this:

hex bytea := decode(hex, 'hex');

And in a query with fixed values this works like a beauty:

select ( array[ (cast(x'ffaa33' as bigint) >> 16) % 256,
                (cast(x'ffaa33' as bigint) >> 8) % 256,
                 cast(x'ffaa33' as bigint) % 256 ] )

But I can't put the two together, passing - for example 'ffaa33' as parameter.

Anyone got a better idea? I'm using PosgreSQL 9.1?

Solution

An easy way is:

 select ('x'||lpad(the_hex_value,16,'0'))::bit(64)::bigint;

The left padding with 0 is necessary because the leftmost bit is always going to be interpreted as the sign bit. Also keep in mind that bigint is signed, postgres doesn't have built-in unsigned types.

Comments

[Cherven]

thanks Daniel. How to do bigint to hex?

[Daniel Vérité]

@Cherven: postgres has a builtin to_hex(bigint) function