Re: hexadecimal to decimal

Claudio Lapidus wrote:
> I have an attribute in a table which stores hexadecimal numbers as a
> two-character string, i.e. the attr definition is char(2). Now I need to
> display these values in decimal, but I wasn´t able to find such a function.
> So, is there a way to perform this conversion?
>

I would have thought there was an easier way (I couldn't think of it),
but this seems to work:

create or replace function hex_to_int(char(2)) returns integer as '
declare
  v_ret record;
begin
  for v_ret in execute ''select x'''''' || $1 || ''''''::int as f'' loop
    return v_ret.f;
  end loop;
end;
' language 'plpgsql';

create table foo(f1 char(2));
insert into foo values ('ff');
insert into foo values ('fe');
insert into foo values ('fd');

regression=# select hex_to_int(f1) from foo;
  hex_to_int
------------
         255
         254
         253
(3 rows)

I'm sure you could do this with plperl or one of the other PLs as well.

HTH,

Joe