Convert hex string to bigint in Postgres

Question

I would like to convert the hexadecimal string used by HTML to bigint , then convert it to the individual values ​​of R, G and B in Postgres through a function written in PL / pgSQL.

I can decode a string in bytea as follows:

 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 not put these two together, passing, for example, "ffaa33" as a parameter.

Has anyone got a better idea? Am I using PosgreSQL 9.1?

Answer 1

Easy way:

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

Left padding with 0 is necessary because the leftmost bit will always be interpreted as a sign bit. Also keep in mind that bigint signed, postgres does not have built-in unsigned types.