Re: Postgres Wishlist

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