Postgres 9.4: fast way to convert color from hex to rgb representation

Question

I have hex representation color, for sample #112233 stored in Postgresql 9.4

Need convert this representation to rgb(17,34,51) in pl/pgsql function

Any Ideas to convert it fastest way?

Answer 1

This uses Erwin's trick to convert a hex value to an integer value:

with colors (hex_code) as (
  values ('#112233'), ('#203040')
)
select 'rgb('||
       ('x'||substr(hex_code,2,2))::bit(8)::int||','||
       ('x'||substr(hex_code,4,2))::bit(8)::int||','||
       ('x'||substr(hex_code,6,2))::bit(8)::int||')'
from colors
;

Not sure if that is the fastest way, but I can't think of a different one. The select expression can be moved into a function without problems.

SQLFiddle demo: http://sqlfiddle.com/#!15/d41d8/3720