6

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?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
simone
  • 4,667
  • 4
  • 25
  • 47

1 Answers1

24

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.

Daniel Vérité
  • 58,074
  • 15
  • 129
  • 156