How about something like this:
create function dec2hex(integer) returns text
as $$
my $arg=shift;
return(sprintf("%x",$arg));
$$ language plperl;
scott=# select dec2hex(255);
dec2hex
---------
ff
(1 row)
It also works on the table columns:
scott=> select ename,dec2hex(sal::int) from emp;
ename | dec2hex
--------+---------
SMITH | 320
ALLEN | 640
WARD | 4e2
JONES | b9f
MARTIN | 4e2
BLAKE | b22
CLARK | 992
SCOTT | bb8
KING | 1388
TURNER | 5dc
ADAMS | 44c
JAMES | 3b6
FORD | bb8
MILLER | 514
(14 rows)
If the reverse function is needed, perl has a function called "hex".
Donald Kerr wrote:
> Michael,
>
> First class :) Problem solved!!!
>
> =======================
> SELECT col, ('x'||substring(col,1,2))::text::bit(8)::int || ' ' ||
> ('x'||substring(col,3,2))::text::bit(8)::int || ' ' ||
> ('x'||substring(col,1,2))::text::bit(8)::int AS oscolor FROM
> cartographictext WHERE COL <> '000000' LIMIT 10
>
> Returns:
> "0099FF";"0 153 0"
> "FF00FF";"255 0 255"
> Etc.
>
> Thank you very much to everyone who helped me with this problem.
>
> Regards,
>
> Donald
>
>
>
>
> -----Original Message-----
> From: Michael Glaesemann [mailto:grzm@seespotcode.net]
> Sent: 13 November 2010 08:45
> To: Donald Kerr
> Cc: 'Tom Lane'; 'Steve Crawford'; pgsql-novice@postgresql.org
> Subject: Re: [NOVICE] Postgres Wishlist
>
>
>
> On Nov 13, 2010, at 3:32 , Donald Kerr wrote:
>
>
>> Thank you, Tom.
>>
>> I have tried what you suggest but it does not seem to work:
>> 'x'||substring(col,3,2)::text::bit(8)::int returns - "9" is not a
>> valid binary digit.
>>
>
> Try with parens:
>
> postgres=# select ('x' || 99::text)::bit(8)::int;
> int4
> ------
> 153
> (1 row)
>
> postgres=# select version();
> version
>
> ----------------------------------------------------------------------------
> --------------------------------------------------------------
> PostgreSQL 9.0.1 on x86_64-apple-darwin10.4.0, compiled by GCC
> i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc. build 5664), 64-bit (1
> row)
>
>
> Michael Glaesemann
> grzm seespotcode net
>
>
>
--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
www.vmsinfo.com