Skip site navigation (1) Skip section navigation (2)

Re: Postgres Wishlist

From: Mladen Gogala <mladen(dot)gogala(at)vmsinfo(dot)com>
To: Donald Kerr <donald(dot)kerr(at)dkerr(dot)co(dot)uk>
Cc: 'Michael Glaesemann' <grzm(at)seespotcode(dot)net>, 'Tom Lane' <tgl(at)sss(dot)pgh(dot)pa(dot)us>, 'Steve Crawford' <scrawford(at)pinpointresearch(dot)com>, "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Postgres Wishlist
Date: 2010-11-13 17:59:39
Message-ID: 4CDED20B.4090101@vmsinfo.com (view raw or flat)
Thread:
Lists: pgsql-novice
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(at)seespotcode(dot)net] 
> Sent: 13 November 2010 08:45
> To: Donald Kerr
> Cc: 'Tom Lane'; 'Steve Crawford'; pgsql-novice(at)postgresql(dot)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 


In response to

pgsql-novice by date

Next:From: Mladen GogalaDate: 2010-11-13 18:07:58
Subject: Re: Postgres Wishlist
Previous:From: Michael GlaesemannDate: 2010-11-13 10:00:30
Subject: Re: Postgres Wishlist

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group